Populate a variant array from a comma-delimited string

Question

Usually if I want to create an array from a comma-delimited string then it's a String array I want and the Split() function I'll use. However right now I need to convert a comma-delimited string into a Variant array so it can be used as a function argument.

Unfortunately attempting to populate a variant array using Split() is throwing a Type Mismatch error, test example below.

Public Sub GetTheStuff()
    Dim varArray() As Variant

    varArray = Split("Bob,Alice,Joe,Jane", ",") '<~~ Error 13, Type Mismatch

    DoTheThing varArray
End Sub

Private Sub DoTheThing(Args() As Variant)
    Dim i As Long

    For i = LBound(Args) To UBound(Args)
        Debug.Print Args(i)
    Next i
End Sub

Is there a simple way to populate a Variant array from a delimited string, or am I going to have to write my own function to do so?

Note: I can't change the fact that the function I'm calling requires a Variant array as an argument, nor can I get my input as anything other than a delimited string.

score 7 · Accepted Answer · answered Apr 10 '15 at 12:35

7

In a word, no - there isn't a Split function that will return an array of Variant. You basically have 2 options - if the array is small(ish), you can convert it:

Dim rawArray() As String
Dim varArray() As Variant

rawArray = Split("Bob,Alice,Joe,Jane", ",")
ReDim varArray(LBound(rawArray) To UBound(rawArray))

Dim index As Long
For index = LBound(rawArray) To UBound(rawArray)
    varArray(index) = rawArray(index)
Next index

The second option you already mentioned in the question - write your own CSV parser (annoying but also not horribly difficult).

answered Apr 10 '15 at 12:35

Comintern

21,855
5
33
80

You mention the condition of the array being small for converting it by this method. Is there a particular reason why parsing is preferable for larger inputs? – Aiken Apr 13 '15 at 11:25
@Aiken - Speed and memory use. When you convert the array, you have 2 copies of it in memory and basically process it twice so a huge input file may cause performance issues. – Comintern Apr 13 '15 at 12:29

NOYB · Answer 2 · 2017-06-09T23:11:26.197

Import CSV data into a two dimensional variant array with numeric as value, date as date, string as string.

' Import CSV data into two dimensional variant array with numeric as value, date as date, string as string.
Private Function CSV_to_Array(CSV As String)

 Dim CSV_Rows() As String

 CSV_Rows() = Split(CSV, Chr(10)) ' split the CSV into rows

 ' Import CSV data into two dimensional variant array with numeric as value, date as date, string as string.
 aryWidth = 0
 ReDim Ary(0 To UBound(CSV_Rows), 0 To aryWidth) As Variant

 For Y = LBound(CSV_Rows) To UBound(CSV_Rows)

    CSV_Fields = Split(CSV_Rows(Y), ",")

    If UBound(CSV_Fields) > aryWidth Then
        aryWidth = UBound(CSV_Fields)
        ReDim Preserve Ary(0 To UBound(CSV_Rows), 0 To aryWidth) As Variant
    End If

    For X = LBound(CSV_Fields) To UBound(CSV_Fields)
        If IsNumeric(CSV_Fields(X)) Then
            Ary(Y, X) = Val(CSV_Fields(X))
        ElseIf IsDate(CSV_Fields(X)) Then
            Ary(Y, X) = CDate(CSV_Fields(X))
        Else
            Ary(Y, X) = CStr(CSV_Fields(X))
        End If
    Next

 Next

 CSV_to_Array = Ary()

End Function


Dim Ary() As Variant
Ary() = CSV_to_Array(strCSV)

Populate a variant array from a comma-delimited string

2 Answers2