Why VBA function "split" returns a 2 dimensional array, instead of a 1 dimensional array?

Question

when I use the function split to break a string into an array I get 2 different results.

if I type in the string in the code the return is an one dimensional array —array(0), array(1), array(2)

values_array = Array(Split("value1, value2", ","))

however, if read the value from a cell or something, the return is a two dimensional array — array(0,0), array(0,1), array(0,2)

values_array = Array(Split(row.Columns(2).Value, ","))

PS. in the line of code above "row" is dim as range

it probably looks stupid, but I tried it this way and it didn't work

values_array = Array(Split(Format(row.Columns(2).Value, "@"), ","))

Margaret Bloom · Accepted Answer · 2016-04-18 09:49:14Z

5

Both Array(Split("value1, value2", ",")) and Array(Split(row.Columns(2).Value, ",")) create a two-dimensional array.

The Split function return an array.
The Array function take a variable number of parameters and make an array out of them.

So in both cases you are making an array with one element which is itself an array.
If you are more comfortable with a picture, here it is

where a has been set to Array(Split("value1, value2", ",")).

It's up to you to know how your program needs to store data, but you may consider removing the call to the Array function.

answered Apr 18, 2016 at 9:49

Margaret Bloom

44.7k5 gold badges91 silver badges133 bronze badges

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

1 Answer 1