Sub or Function not defined Error when using Split function in Excel

ndp · Apr 26, 2004

Hi,
I am trying to customize macro in Excel sheet. The problem I have is when I try to use the function Split, it gives me error "Sub or Function not defined". Same code works at my home computer.
The code is something like this...

Dim aChars() As String
if iComma > 0 then
aChars() = Split(sChars, ",")
For counter3 = 0 To UBound(aChars)
.....
Next counter3
End if

I don't understand why it works at other computer. Do I need to add a reference from the tools menu? If so, which one should it be? I have a Excel 97 version here.

Any suggestion is most welcome!
Thanks,
ndp

johnwm · Apr 26, 2004

As I recall Split isn't supported in '97. It's fairly simple to write your own in VBA using Mid$ and Instr$ functions.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

ndp · Apr 26, 2004

Thanks for recommanding mid$ and Instr$ functions. I will use that.

ndp

PHV · Apr 26, 2004

I have a Excel 97 version here
No luck: Split, Join, Replace, InStrRev ... aren't for you.
Here a workaround:
Dim aChars() As String
if iComma > 0 then
i = 1
n = 0
Do
j = InStr(i, sChars, ",")
If j = 0 Then Exit Do
ReDim Preserve aChars

aChars

= Mid(sChars, i, j - 1)
n = n + 1
i = j + 1
Loop
ReDim Preserve aChars

aChars

= Mid(sChars, i)
For counter3 = 0 To UBound(aChars)
.....
Next counter3
End if

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

ndp · Apr 26, 2004

That is great! Perfect! I was going to do something like that only, but didn't know how to loop through. Now, I can use this.

Thanks a lot!
ndp

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Sub or Function not defined Error when using Split function in Excel

ndp

Programmer

johnwm

Programmer

ndp

Programmer

PHV

MIS

ndp

Programmer

Similar threads

Part and Inventory Search

Sponsor