Excel - VBA mid() function

Asked By vello on 02-Nov-09 12:14 PM
Using XP & Excel2002 to find a string in another string, gets me weird results:
Dim S As String
Dim L As Long

S = "0123456789"
For L = 1 To 10
Debug.Print Mid$(S, L, L)

The above code returns the correct value only on the 1st iteration.
Thereafter the Mid$ keeps growing.

Is there an API procedure to accomplish this? Can't seem to find it.

Peter T replied on 02-Nov-09 12:22 PM
Debug.Print Mid$(S, L, 1)

at least that is what I assume you want!

Peter T
Jacob Skaria replied on 02-Nov-09 12:27 PM
If you are looking to find a string in another string use INSTR()..Check out
the help on VBA InStr()

If this post helps click Yes
Jacob Skaria
Mike H replied on 02-Nov-09 12:44 PM
I am not sure what your trying to do but the code will return


The reason it does that is for every loop L increases by 1 and your using L
as both start position and lengthe of text to return so in (say) the 4th
iteration of the loop your formula is

Debug.Print Mid$("0123456789", 4, 4)

From this you should be able to work out why the returned string gets
shorter when L=6

Perhaps what you really want is

Debug.Print Mid$(S, L, 1)

Rick Rothstein replied on 02-Nov-09 01:06 PM
Your posting is not very clear. You say you want to "find a string in
another string", but the code you posted does not even come close to doing
that. Exactly what are you trying to do? Try to give an example that
demonstrates whatever it is.

Rick (MVP - Excel)
Charlie replied on 02-Nov-09 05:06 PM
Debug.Print Mid$(S, L, 1)

Use a 1 not "L"
Rick Rothstein replied on 02-Nov-09 05:14 PM
I am not "picking" on your Charlie, it is just I needed to attach this note to
either you message or Peter T's message... I chose yours.

Both you and Peter made the same suggestion (to change the second L to a
one)... my question to the both of you is...

How does that explain the OP's statement "The above
code returns the correct value only on the 1st iteration"?

It would seem that changing the second L to a one would make it so the first
iteration would fail, not succeed.

Rick (MVP - Excel)
Peter T replied on 02-Nov-09 06:04 PM
Hi Rick,

If you run the loop first with Mid$(S, L, L) then with Mid$(S, L, 1) I think
your question is answered. That is, "assuming" the OP wants each character
of the sample string returned individually.

Peter T
Rick Rothstein replied on 02-Nov-09 06:57 PM
Uh... never mind... it just dawned on me how I was misreading the original
code. You are (probably) right.. the third argument should be a 1.

Rick (MVP - Excel)
vello replied on 02-Nov-09 09:16 PM
Thanks to all of you for your notes. Now I see where the problem was.

What I want to do is to find a certain 'word' in a textstream and then place
it in an Excel cell. Seems to me there was an API that did this well, but
cannot remember it.
Charlie replied on 03-Nov-09 07:56 AM
Did I miss something?  On the first iteration L would equal 1.  The loop is
from 1 to 10 -- the STRING started at zero ("0123...")
Rick Rothstein replied on 03-Nov-09 12:56 PM
No, you did not miss anything... I did. Sorry for the confusion, but
apparently I misread the original post and then made my comment based on my

Rick (MVP - Excel)