Convert Variant Array to String

vba variant
user3428722 picture user3428722 · Mar 17, 2014 · Viewed 27k times · Source

I am trying to take a variant variable and convert it into a string so that I can run a split function on the data. However, whenever I try to redefine the variant I get a type mismatch error. I have used the CStr(), Str(), and ToString functions. None work.

Anything I am missing?

Function FlatLine(ByVal lines As Variant)

Dim flat() As String
ReDim Preserve flat(i)

For i = 0 To UBound(lines)
    flat(UBound(flat)) = lines(i)
    ReDim Preserve flat(LBound(flat) To UBound(flat) + 1)
Next i

Dim flat2 as String
flat2 = Cstr(flat)

^ errors there.

Answer

bugmagnet picture bugmagnet · Mar 17, 2014

The for is useless, as far as I can see. Better ReDim flat and generate flat2 as below

ReDim flat(UBound(lines))
flat2 = Join(flat,"|")

in fact, given that lines is coming in as ByVal you could probably

flat2 = Join(lines,"|")

Related questions

Excel VBA: Variants in Array Variables

A question on variants. Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps). However, I regularly use them for storing data in arrays that have …

Read More
How to return the number of dimensions of a (Variant) variable passed to it in VBA

Does anyone know how to return the number of dimensions of a (Variant) variable passed to it in VBA?

Read More
How to check whether a variant array is unallocated?

Dim Result() As Variant In my watch window, this appears as Expression | Value | Type Result | | Variant/Variant() How do I check the following: if Result is nothing then or if Result is Not Set then This is basically what I …

Read More