Using the Split function in VBA

[AD_Taylor]

Is there a particular reason when using the Split function that you can't assign the result to a variable already created as an array?

For instance with the active cell having this value:

cat/dog/moose/badger/wild boar

I tried to do something like this:

Sub prSplit()
    Dim arr() As Variant
    
    arr = Split(ActiveCell.Value, "/")
End Sub

But something like this actually worked and created an array?

Sub prSplit()
    Dim str As Variant
    
    str = Split(ActiveCell.Value, "/")
End Sub

Seems to spoil my convention a bit, which is to list all variables at the top of the code so by quickly scrolling up I can see what variables are what type and which are arrays and which are not.

 

[repairman615]

Hello,

I do know that arrays are trick, however they seem to be getting easier to understand.


I beleive that using the dim statement as a variant does not explicitly accept arrays. You can declare arrays as specific types.



<font face=Franklin Gothic Medium><SPAN style="color:#00007F">Sub</SPAN> prSplit()<br>    <SPAN style="color:#00007F">Dim</SPAN> arr() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    arr = Split(ActiveCell.Value, "/")<br>    <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(arr) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(arr)<br>        MsgBox arr(i)<br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Rick Rothstein]

The problem is that the Split function returns a String array. When your declaration was this...

Dim arr() As Variant

it meant that arr was going to be assigned an array of Variants (the parentheses force arr to be an array)... a String array is not an array of Variants, rather it is an array of Strings, so you get a Type Mismatch error. However, when your declaration was this...

Dim str As Variant

it meant that str was a simple Variant variable and, as such, could be assigned anything... a Long, a String, a Double, an Object, an array of any kind, etc. Since the Split function returns a String array, it could be assigned to a simple Variant variable. All though you did not ask, there is one other declaration you could have used...

Dim str() As String

This declaration means str can be assigned an array of Strings and, since this is exactly what the Split function returns, you could make that assignment to it...

str = Split(ActiveCell.Value, "/")

 

[AD_Taylor]

Thanks both for the explanation! and thanks very much to Rick for the great detail!

I'd mistakenly assumed an array of variants operated like a group of single variants in that any element of the array could be any variable type.

Will bear this in mind in future, thanks again
Adam

 

[Rick Rothstein]

I'd mistakenly assumed an array of variants operated like a group of single variants in that any element of the array could be any variable type.

You are not mistaken... that is exactly what an array of variants does, but that is not what you were doing. When you made this declaration...

Dim arr() As Variant

and then followed it up with this...

arr = Split(ActiveCell.Value, "/")

you were attempting to force the entire declared array of variants into an array of Strings... you were not assigning the String array to any one element of the Variant array. To do that, your code would have had to look something like this...

Dim arr() As Variant
ReDim arr(1 To 5)
arr(1) = Split(ActiveCell.Value, "/")

Note the distinction... each element of the Variant array can contain anything including an entire String array (here, only the first element is being assigned that... there are still another 4 elements that have nothing assigned to them yet), but arr itself cannot because it can only be directly assigned another array of Variants.