7

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.
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
user3428722
  • 71
  • 1
  • 1
  • 4
  • 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 Mar 17 '14 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 Mar 17 '14 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 Mar 17 '14 at 14:11
  • @simoco "You can't convert array of string to single string" ... unless you use Join()? – bugmagnet Mar 17 '14 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 Mar 17 '14 at 14:15
  • Then I guess I'm very confused. I can't run a split on lines and I won't be able to run a split on flat? – user3428722 Mar 17 '14 at 14:17
  • I think I've misunderstood from the beginning (blush) – bugmagnet Mar 17 '14 at 14:20
  • @user3428722, what is your real purpose? `Split` takes _single string_ and returns _array of strings_. As I see you already have array. What is the point of converting it to string array, then join and finally split it again? – Dmitry Pavliv Mar 17 '14 at 14:21
  • Because when I split the array and rewrite I was unable to get the array to not overwrite prior lines? For example: If my line said xxxxxx | xxxxxxx. I would want to append 2 lines to the array after the split. I was unsure of how to do this so I figured I'd just make another string and split that. – user3428722 Mar 17 '14 at 14:23

1 Answers1

7

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,"|")
bugmagnet
  • 7,631
  • 8
  • 69
  • 131