1.11M Members

VBA for MS Access question - split function



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!


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

NewArrayKey0 = cstr(NewArray(0))

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 article has been dead for over six months: Start a new discussion instead
Start New Discussion
View similar articles that have also been tagged: