VBA Join Function for unknown number of arguments

[robertgama]

Does anyone have a function that takes an unknown number of ranges (worksheet cells) as arguments that can then be joined with the provided delimiter.

It should ignore null values.

I saw that someone posted the following line of code:
Replace(WorksheetFunction.Trim(Join(myArray)), " ", ",")

So I guess my real question is how to pass an unknown number of arguments into a function and put the values in an array.

I used to know how to handle something similar but I forget now.

Thanks,

Rob.

 

[mvptomlinson]

A function to join values in a range using a delimiter could be:

Public Function mjoin(r As Range, delim As String)
Dim ce As Range
For Each ce In r
    If ce.Value <> "" Then mjoin = mjoin & delim & ce.Value
Next ce
mjoin = Replace(mjoin, delim, "", 1, 1)
End Function

You would use the following to concatenate A1:A10 with a semi-colon between each. The function skips cells that are blank.

=MJOIN(A1:A10,";")

If you wanted to add those values to an array instead, you could count (countA) the number of entries in your range(s) and set the array dimensions as needed, then just fill the array. (Use a sub, not a function.)

 

[pgc01]

So I guess my real question is how to pass an unknown number of arguments into a function and put the values in an array.

Hi Rob

Take a look at the help on ParamArray. It allows you to pass a variable number of arguments into a function, like you need.

This is a simple example of a function with a variable number of arguments:

Sub Test()
Dim s As String
 
s = MultParam("A", "B", "C", "D")
MsgBox s
End Sub
 
Function MultParam(ParamArray vStrings()) As String
MultParam = Join(vStrings, ",")
End Function

 

[robertgama]

Thanks a lot guys.

ParamArray - That was what I was trying to remember. I then searched and found this post on the forum for others that are looking to do the same thing and find this thread:
http://www.mrexcel.com/forum/showpost.php?p=3022836&postcount=6
It was a post by Domenic (MVP) with a function by Harlan Grove.

I'll quote the function here as well for completeness of the thread:

Function AConcat(Sep As String, ParamArray ArgList() As Variant) As String
 
 Dim Arg As Variant
 Dim Cell As Range
 Dim Element As Variant
 
 For Each Arg In ArgList
 
     If TypeOf Arg Is Range Then
 
         For Each Cell In Arg.Cells
             AConcat = AConcat & Cell.Value & Sep
         Next Cell
 
     ElseIf IsArray(Arg) Then
 
         For Each Element In Arg
             AConcat = AConcat & Element & Sep
         Next Element
 
     Else
 
         AConcat = AConcat & Arg & Sep
 
     End If
 
 Next Arg
 
 AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
 
End Function

Thanks again guys,

Rob.