Suggestions

 User Defined Functions

concatenate
a row
a row no blanks
a column
a column no blanks
a range
a range no blanks

a row, separator
a row no blanks, separator
a column, separator
a column no blanks, separator
a range, separator
a range no blanks, separator

distinct items in column
uniques in a range
multiples in a column
frequency occurrence in a column
longest string in a range
 You can design any 'formula' in VBA that you can use in a worksheet as if it were an Excel Formula/Function.
In Excel they are called 'User Defined Functions (UDF).
For the function to be accessible in the worksheet you have to store it an a macromodule.
A function always returns a value (string, integer, array etc.) in the form of the name of the function.
More often than not you will have to pass one or more 'arguments' to a function, that it will use to perform it's calculations.
In the worksheet you can enter the function, specifying it's argument(s); after entering the formula it will be calculated and the result will be displayed in the cell.
Example
The formula 'F_concatenaterow_snb' in cell H1, where you want the concatenated values of range A1:F1
= F_concatenaterow_snb(A1:F1)

In this page I will list a few

concatenate values in a row

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

concatenate values in a row without blanks

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

concatenate values in a column

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

concatenate values in a column without blanks

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

concatenate values in a range

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

concatenate values in a range without blanks

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

concatenate values in a row specifying the separator

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

concatenate values in a row without blanks specifying the separator

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

concatenate values in a column specifying the separator

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

concatenate values in a column without blanks specifying the separator

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

concatenate values in a range specifying the separator

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

concatenate values in a range without blanks specifying the separator

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

count all distinct items in a column

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

count items that occur only once in a range

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

count distinct items that occur more than once in a column

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

count distinct items that occur in a certain frequency in a column

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

the length of the longest string in a range

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