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

arguments, arrays, function, vba

Solution

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

Problem

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!)

Original source