Passing Excel string array to user defined VBA function

Question

I wish to use an Excel array function to generate an array of strings and then pass this to a user defined function to strip blanks and concatenate the strings separated by a ",".

I have a function that does this when called from a VBA macro. When I try to use it as a user defined function, e.g. =ConcStr({"A","B","C"}), I get a #Value! error.

Function is below:

Sub StrTest()
    Dim StaticArray(1 To 3) As String
    Dim Result As String
    StaticArray(1) = "A"
    StaticArray(2) = "B"
    StaticArray(3) = "C"
    Result = ConcStr(Arr:=StaticArray)
    MsgBox Result
End Sub

Function ConcStr(Arr() As String) As String
    MsgBox "started"
    Dim N As Long
    Dim Total As String
    For N = LBound(Arr) To UBound(Arr)
        MsgBox Arr(N)
        Total = Total & "," & Arr(N)
    Next N
    ConcStr = Total
End Function

IIRC, you cannot interchange fixed size arrays and dynamic arrays. — RBarryYoung
– RBarryYoung, Commented Aug 26, 2015 at 1:15

Bond · Accepted Answer · 2015-08-26 01:23:02Z

1

If you rewrite your UDF to accept a Variant instead, it should work. Also, you can just use the Join function to accomplish what you need:

Function ConcStr(arr As Variant) As String
    ConcStr = Join(arr, ",")
End Function

answered Aug 26, 2015 at 1:23

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 0 · Accepted Answer · 2015-08-26 01:27:11Z

0

Declare, dim, assign and pass the array over as a variant.

Sub StrTest()
    Dim StaticArray As Variant, Result As String

    ReDim StaticArray(1 To 3)

    StaticArray(1) = "A"
    StaticArray(2) = "B"
    StaticArray(3) = "C"

    Result = ConcStr(Arr:=StaticArray)
    MsgBox Result
    Result = ConcStr2(Arr:=StaticArray)
    MsgBox Result

End Sub

Function ConcStr(Arr As Variant) As String
    MsgBox "started"

    Dim N As Long, Total As String

    For N = LBound(Arr) To UBound(Arr)
        MsgBox Arr(N)
        Total = Total & "," & Arr(N)
    Next N

    ConcStr = Mid(Total, 2) 'Mid to get rid of the first comma

End Function

Function ConcStr2(Arr As Variant) As String
    'could just be like this,
    ConcStr2 = Join(Arr, ",")
End Function

I've added an alternative Join Function version to simplfy things and modified your function with the Mid function to remove the leading comma.

edited Aug 26, 2015 at 1:27

answered Aug 26, 2015 at 1:22

user4039065

1 Comment

Lulu Over a year ago

Thank you all, much appreciated

John Alexiou · Accepted Answer · 2015-08-26 02:32:05Z

0

I was able to get what you want with:

Public Function ConcatString(ByVal arr As Variant) As String
    ConcatString = vbNullString
    Dim i As Long, n As Long, z as Long
    z = LBound(arr) : n = UBound(arr)
    For i = z To n
        ConcatString = ConcatString + arr(i)
    Next i
End Function

edited Aug 26, 2015 at 2:32

answered Aug 26, 2015 at 1:22

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

3 Comments

John Alexiou Over a year ago

A UDF with array argument {"A","B","C"} does not pass as an static array in VBA. Variant is what should be used instead.

user4039065 Over a year ago

What if the arr being passed in was zero-based, e.g. with an LBound(arr) of 0? Your For... Next is going to skip the first array element.

John Alexiou Over a year ago

Well yes. So I fixed the post. I was assuming it was going to be used as an UDF only.

Collectives™ on Stack Overflow

Passing Excel string array to user defined VBA function

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related