Hello!

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!
-jay

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.