using the split function to define an array

[Joanne]

I stumbled on the split function while I was trying to figure out how to add
a lot of names to an array. It's working fine, as tested by the message box.
Whatever number I enter in the message box brings up the appropriately
indexed text for the array. But if a define the array with a specific number
of elements, like straray Names(5) as string, I get an error that this is not
an array.

Could someone explain?

Thanks as always for your help.


Sub gtAray()
Dim strListNames As String
Dim strNamesAray() As String
strListNames = "Joanne|Rob|Frank|Beth|Jess|Zach"
strNamesAray = Split(strListNames, "|")
MsgBox "The fourth member of the array is " & strNamesAray(3)
End Sub

 

[Jay Freedman]

I stumbled on the split function while I was trying to figure out how
to add a lot of names to an array. It's working fine, as tested by
the message box. Whatever number I enter in the message box brings
up the appropriately indexed text for the array. But if a define the
array with a specific number of elements, like straray Names(5) as
string, I get an error that this is not an array.

Could someone explain?

Thanks as always for your help.


Sub gtAray()
Dim strListNames As String
Dim strNamesAray() As String
strListNames = "Joanne|Rob|Frank|Beth|Jess|Zach"
strNamesAray = Split(strListNames, "|")
MsgBox "The fourth member of the array is " & strNamesAray(3)
End Sub

The error message (which is actually "Can't assign to array") is misleading.
It should say something like "Can't assign to an array with a fixed size".

The Split function requires the ability to resize the array to exactly the
number of elements that it finds in the string, using an internal version of
ReDim. Just like ReDim, it requires you to declare the array with an
unspecified number of elements.

The help topic doesn't mention it, but you can also declare the "array" as a
Variant data type without the parens,

Dim strNamesAray As Variant

Everything else in the macro will continue to work the same way.