Excel VBA: Convert Variant Array to String Array

To simplify the conversion of a variant array to a string array we have made a set of utility functions.

' Array Variant to String

Public Function VariantArrayToStringArray(ByVal arrVariants As Variant) As String()

    Dim arrStrings() As String

   

    ' Get the string array

    Call ParamArrayToStringArray(arrVariants, arrStrings)

   

    ' Get the string array

    VariantArrayToStringArray = arrStrings

End Function

' Array Variant to String

Public Sub ParamArrayToStringArray(ByVal arrVariants As Variant, ByRef arrStrings() As String)

    Dim intLength As Integer

   

    ' Handle the array

    Call ParamArrayToStringArrayInternal(arrVariants, arrStrings, intLength)

End Sub

' Array Variant to String

Private Sub ParamArrayToStringArrayInternal(ByVal arrVariants As Variant, ByRef arrStrings() As String, ByRef intLength As Integer)

    ' Parameter is array?

    If (IsArray(arrVariants)) Then

        Dim i As Integer

        Dim objValue As Variant

   

        ' Walk through the specified partner objects

        For i = LBound(arrVariants) To UBound(arrVariants) Step 1

            ' Get the value

            objValue = arrVariants(i)

       

            ' Array to string

            Call ParamArrayToStringArrayInternal(objValue, arrStrings, intLength)

        Next

    Else

        ' Next item

        intLength = intLength + 1

   

        ' Expand array

        ReDim Preserve arrStrings(1 To intLength)

        ' Set the value

        arrStrings(intLength) = CStr(arrVariants)

    End If

End Sub