InStr() error

[cpjust]

Hi,
I'm using VBA for Excel 2003, and I'm trying to parse a string...
I can use the Mid() function without any errors, but I'm trying to find the position of the first space in the string using:

iEnd = InStr(iStart, strText, " ")

and it gives me:

Run-time error '5':
Invalid procedure call or argument

Am I trying to do something too logical for VBA to handle?
I don't see any syntax errors with that statement, and I don't see anything wrong with the arguments. Here are their values:

iStart = 0
strText = "107 Pass" & vbLf & "9 Fail" & vbLf & "6 Untested

 

[PHV]

iStart = 1

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

 

[cpjust]

Oops. Nevermind, I just figured it out.
This is VB, not C. Indexes start at 1. Doh!

 

[firefytr]

Well, most indexes start at 0. The InStr function starts at 1 because it's dealing with physical characters, not necessarily indexes. There is no 0 character. By default VB/A is a 0 index language.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[cpjust]

I've always been told that VB is a 1 index language.
Can you give me some examples of when indexes start at 0?

 

[HarleyQuinn]

A few examples of 0 starting indexes:

Listboxes, Comboboxes, Array's (inc. default control array's)...

And there's lots more.

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[cpjust]

Really? That's strange. I wonder why MS made it inconsistent with some starting at 0 and others at 1?

BTW, does anyone have any good VBA reference book or website suggestions? I've just been using the MSDN site so far, but I find it's either missing some functions, or it's just hard to find them.

 

[GlennUK]

Well, if you think about it, those collections that are made up of actual objects within Excel ( sheets, cells, etc ), are numbered starting at 1, as there is no concept of object number 0 in these cases.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[firefytr]

VBA is a 0 index language. You can, at the top level of every module, use something like this ..

Option Base 1

That will make the entire module a 1 index-based module. If you dim arrays, ie..

Dim arrMyText()

.. that automatically starts at 0. It's confusing where some things need to start at 1. Things such as text in a cell (no 0 character), row and column numbers, sheet index numbers, etc (as Glenn pointed out, actual "physical" objects).

Some good books I'd recommend:
Excel VBA for Dummies (my wife loves these books)
Professional Excel Development (an excellent all around development book)
VBA and Macros for Microsoft Excel (I have this one on my shelf, very good)
John Walkenbach (any J-Walk book is highly recommended)

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci