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.
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).
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With