Convert Variant Array to String

Question

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.

1) flat is array of strings, flat2 is string. How are you going to convert array of strings to string? 2) why not just use ReDim flat(0 To UBound(lines))? — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 17, 2014 at 14:00
I am not sure if i understand your second question (novice myself), the end game after this is to take this new string and run a split. If I try to use your suggested ReDim I get all "" values in flat. — user3428722
– user3428722, Commented Mar 17, 2014 at 14:06
relatively to my second quesiont you should also use flat(i) = lines(i) in your loop and remove line ReDim Preserve flat(LBound(flat) To UBound(flat) + 1). So, the end game after this is to take this new string and run a split. - what new string? flat is array of strings, but not single string. You can't convert array of string to single string. — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 17, 2014 at 14:11
@simoco "You can't convert array of string to single string" ... unless you use Join()? — bugmagnet
– bugmagnet, Commented Mar 17, 2014 at 14:14
@boost, join array of strings to single string and convert array of string to string is completly different things. Last one is impossible. Join array make no sence to me in this Q - because why OP should join array of string and then split it back to array? What is the point of it? — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 17, 2014 at 14:15

bugmagnet · Accepted Answer · 2014-03-17 14:18:50Z

8

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,"|")

edited Mar 17, 2014 at 14:18

answered Mar 17, 2014 at 14:03

bugmagnet

7,8198 gold badges74 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3428722 Over a year ago

Thanks for the suggestion. The delimter is going to be a "|", unfortunately i have no non blank rows.

gregV · Accepted Answer · 2025-08-08 14:10:29Z

0

Assuming lines is 1-dimensional Variant Array and you need a String Array out :

lines_str = Split(Join(Lines, "|"), "|")
Debug.Print "lines is", TypeName(Lines)
Debug.Print "lines_str is", TypeName(lines_str_)

if 2-dimentional add Application.Transpose as follows:
lines_str = (Split(Join(Application.Transpose(lines), "|"), "|"))

Debug.Print "lines is", TypeName(Lines)

Debug.Print "lines_str is", TypeName(lines_str_)

answered Aug 8 at 14:10

gregV

1,11712 silver badges30 bronze badges

Collectives™ on Stack Overflow

Convert Variant Array to String

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related