Suggesties User Defined Functions

Inleiding
Voorbeeld

Samenvoegen rij
alle cellen
gevulde cellen
alle cellen, scheidingsteken
gevulde cellen, scheidingsteken

Samenvoegen kolom
alle cellen
gevulde cellen
alle cellen, met scheidingsteken
gevulde cellen, scheidingsteken

Samenvoegen gebied
alle cellen
gevulde cellen
alle cellen, scheidingsteken
gevulde cellen, scheidingsteken

afzonderlijke items in kolom
afzonderlijke items in rij

frekwentie kolom-items >1
frekwenties kolom-items

langste tekenreeks in gebied

Inleiding

Met VBA kun je eigen Excel'formules' maken.
Die heten in Excel: User Defined Functions (UDF).In het lijstje met beschikbare funkteis vind je ze bij 'door gebruiker gedefinieerd'
Die moet je altijd in een macromocule opslaan om ze beschikbaar te hebben in een werkblad.
Het resultaat van een funktie krijg je terug als de naam van de funktie en kan tekst, getallen of een matrix zijn.
Meestal moet je argumenten doorgeven waarmee de funktie kan gaan rekenen.
In een cel in het werkblad kun je de funktie met de argumenten invoeren; daarna toont de cel het resultaat van de berekening.

Voorbeeld

Plaats in een cel waarin je de samenvoeging van de cellen A1:E1 wil zien de funktie F_concatenaterow_snb'
= F_concatenaterow_snb(A1:E1)
Op deze pagina een aantal UDF's

voeg de celwaarden in een bepaalde rij samen

Function F_concatenaterow_snb(c01)
F_concatenaterow_snb = Join(Application.Index(c01.Value,1,0), "|")
End Function

voeg de celwaarden in een bepaalde rij samen zonder lege cellen

Function F_concatenaterow_noblanks_snb(c01)
F_concatenaterow_noblanks_snb = Replace(Join(Filter(Split("~" & Join(Application.Index(c01.value,1,0), "~|~") & "~", "|"), "~~", False), "|"), "~", "")
End Function

voeg de celwaarden in een bepaalde kolom samen

Function F_concatenatecolumn_snb(c01)
F_concatenatecolumn_snb = Join(Application.Transpose(c01), "|")
End Function

voeg de celwaarden in een bepaalde kolom samen zonder lege cellen

Function F_concatenatecolumn_noblanks_snb(c01)
F_concatenatecolumn_noblanks_snb = Replace(Join(Filter(Split("~" & Join(Application.Transpose(c01), "~|~") & "~", "|"), "~~", False), "|"), "~", "")
End Function

voeg de celwaarden in een bepaald gebied samen

Function F_concatenaterange_snb(c01)
For j = 1 To UBound(c01.Value)
c02 = c02 & "|" & Join(Application.Index(c01.Value, j), "|")
Next
F_concatenaterange_snb = Mid(c02, 2)
End Function

voeg de celwaarden in een bepaald gebied samen zonder lege cellen

Function F_concatenaterange_noblanks_snb(c01)
For j = 1 To UBound(c01.Value)
c02 = c02 & "~|~" & Join(Application.Index(c01.Value, j), "~|~")
Next
F_concatenaterange_noblanks_snb = Replace(Join(Filter(Split(Mid(c02, 3) & "~", "|"), "~~", False), "|"), "~", "")
End Function

voeg de celwaarden in een bepaalde rij samen met een bepaald scheidingsteken

Function F_conc_row_sep_snb(c01,c03)
F_conc_row_sep_snb = Join(Application.Index(c01.value,1,0),c03)
End Function

voeg de celwaarden in een bepaalde rij samen zonder lege cellen met een bepaald scheidingsteken

Function F_conc_row_noblanks_sep_snb(c01,c03)
F_conc_row_noblanks_sep_snb = Replace(Join(Filter(Split("~" & Join(Application.Index(c01.value,1,0), "~|~") & "~", "|"), "~~", False),c03), "~", "")
End Function

voeg de celwaarden in een bepaalde kolom samen met een bepaald scheidingsteken

Function F_conc_col_sep_snb(c01,c03)
F_conc_col_sep_snb = Join(Application.Transpose(c01),c03)
End Function

voeg de celwaarden in een bepaalde kolom samen zonder lege cellen met een bepaald scheidingsteken

Function F_conc_col_noblanks_sep_snb(c01,c03)
F_conc_col_noblanks_sep_snb = Replace(Join(Filter(Split("~" & Join(Application.Transpose(c01), "~|~") & "~", "|"), "~~", False),c03), "~", "")
End Function

voeg de celwaarden in een bepaald gebied samen met een bepaald scheidingsteken

Function F_conc_range_sep_snb(c01,c03)
For j = 1 To UBound(c01.Value)
c02 = c02 &c03& Join(Application.Index(c01.Value, j),c03)
Next
F_conc_range_sep_snb = Mid(c02, 2)
End Function

voeg de celwaarden in een bepaald gebied samen zonder lege cellen met een bepaald scheidingsteken

Function F_conc_range_noblanks_sep_snb(c01,c03)
For j = 1 To UBound(c01.Value)
c02 = c02 & "~|~" & Join(Application.Index(c01.Value, j), "~|~")
Next
F_conc_range_noblanks_sep_snb = Replace(Join(Filter(Split(Mid(c02, 3) & "~", "|"), "~~", False),c03), "~", "")
End Function

aantal afzonderlijke items in een kolom

Function F_count_distinct_snb(c01)
F_count_distinct_snb = Evaluate("Sum(N(countif(offset(" &c01.Cells(1).Address & ",,,row(" &c01.Address & "))," & c01.Address & ")=1))")
End Function

aantal afzonderlijke items in een rij

Function F_count_frequency1_items_snb(c01)
F_count_frequency1_snb = Evaluate("SUM(N(countif(" &c01.Address & "," &c01.Address & ")=1))")
End Function

aantal afzonderlijke items in een kolom met frekwentie >1

Function F_count_distinct_multiples_snb(c01)
F_count_distinct_multiples_snb = Evaluate("SUM(N(countif(" &c01.Address & "," &c01.Address & ")>1)*N(countif(offset(" &c01.Cells(1).Address & ",,,row(" &c01.Address & "))," &c01.Address & ")=1))")
End Function

aantal afzonderlijke items in een kolom met een bepaalde frekwentie

Function F_count_distinct_multiples_freq_snb(c01,x)
count_distinct_multiples_freq_snb = Evaluate("SUM(N(countif(" &c01.Address & "," &c01.Address & ")=" &x& ")*N(countif(offset(" &c01.Cells(1).Address & ",,,row(" &c01.Address & "))," &c01.Address & ")=1))")
End Function

de lengte van de langste tekenreeks in een gebied (range)

Function F_longest_string_snb(c01)
F_longest_string_snb = Evaluate("Max(len(" &c01.Address & "))")
End Function