CODEWITHSUNDEEP
arraysfunctionvbaarguments
BrettFromLA
BrettFromLA

Reputation: 916

In VBA, how to split a string into an array, then pass it as an argument to a Sub or Function

MY QUESTION:

Is there a way to split a string into an array, then pass that as an argument to a Sub or Function that is expecting an array?

THE DETAILS:

I have two simple VBA Subs below. Sub foo() splits a string into an array, then tries to pass it to Sub bar().

Sub foo()
    aTest = Split("1,2,3", ",")
    bar (aTest)
End Sub

Sub bar(ByRef aArray())
    ' Do something
End Sub

However, they won't compile; in the line bar (aTest) the parameter isn't recognized as an array. Here's the error:

Compile error:
Type mismatch: array or user-defined type expected

I think the error is happening because the compiler doesn't interpret the Split command as making the variable into an array. (If that's a wrong assumption, let me know!)

Upvotes: 2

Views: 5802

Answers (1)

Tim Williams
Tim Williams

Reputation: 166895

Since you don't seem to like using Variants, you are free to be more specific if you want:

Sub foo()
    Dim aTest() As String
    aTest = Split("1,2,3", ",")
    Debug.Print TypeName(aTest) 'String()
    bar aTest
End Sub

Sub bar(ByRef aArray() As String)
    ' Do something
End Sub

Upvotes: 6

Related Questions