I'm working on VBA within MS Access XP, and can't seem to get the proper usage of the split function down. I've been trying to make this work, but I keep getting the "Run-time error '9': Subscript out of range" error.

Since the code of my project is kind of long, I'll try to stick to the most relevant part- can someone please explain to me where I'm going wrong?

'Declare your Dims:
Dim InputString As String
Dim NewArray As Variant
Dim NewArrayKey0 As String

'Give the InputString variable a value:
InputString = "input string"

'Split the InputString variable into two parts,
'separated by the default delimiter, the space:
NewArray = Split(InputString)

'Assign the first key of the array to NewArrayKey0 variable:
NewArrayKey0 = NewArray(0)

'Display the value of NewArrayKey0 (It should be the word "input") in a message box:
MsgBox NewArrayKey0

Thank you!

5 Years
Discussion Span
Last Post by BitBlt

On line 14 you have to use the cstr function to force the Variant to be a string:

NewArrayKey0 = cstr(NewArray(0))

Edited by BitBlt: n/a


Hi, BitBit, thanks for assisting,

I tried your suggestion, but I still get me the same error, highlighting line 14.

Could it be the data type of the "NewArrayKey0", when it's declared? It is a string, but should I set it up as another data type?


Wow, I'm sorry my suggestion didn't help. I actually went so far as to build a little dummy VB6 app AND a dummy MSAccess app, and it worked fine for me both ways. I have no other suggestions.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.