Is Instr Slow?

[jmp99]

Hello,

I'm using WinXP Pro, Office XP SP2.

I'm using the following code (below) to parse through a string variable
(sPage) that contains line breaks [chr(10)]. However, even though it
loops between 10,000 and 11,000 times, it seems to run slow. Can
someone please give me an idea on how I can make this code more
efficient, or suggest another method?

Thanks.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
rLen = Len(sPage)
S = 1
For x = 1 To 11000
If S < rLen Then
h = InStr(IIf(S = 1, S, S + 1), sPage, Chr(10), vbTextCompare)
Worksheets("Data").Range(sLoc).Offset(x - 1, 0).value = _
Mid(sPage, IIf(S = 1, S, S + 1), h - (S + 1))
S = h
Else
Exit For
End If
Next x
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Jake Marx]

Hi jmp99,

I'm not entirely sure what you're trying to do. But it looks like you're
trying to break apart sPage at the line breaks. If that's the case, you
should be able to do this quite a bit faster using the Split function. Then
you can write the resulting array to the worksheet in one line of code.
Here's an example:

'/ YOUR CODE
'/ ...

Dim vData As Variant

vData = Split(sPage, Chr(10))

Worksheets("Data").Range(sLoc).Resize(UBound(vData) + 1, _
1).Value = Application.Transpose(vData)

Hopefully, I understood your problem correctly. If not, let us know.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


jmp99 wrote:
> Hello,
>
> I'm using WinXP Pro, Office XP SP2.
>
> I'm using the following code (below) to parse through a string
> variable (sPage) that contains line breaks [chr(10)]. However, even
> though it loops between 10,000 and 11,000 times, it seems to run
> slow. Can someone please give me an idea on how I can make this code
> more efficient, or suggest another method?
>
> Thanks.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> rLen = Len(sPage)
> S = 1
> For x = 1 To 11000
> If S < rLen Then
> h = InStr(IIf(S = 1, S, S + 1), sPage, Chr(10), vbTextCompare)
> Worksheets("Data").Range(sLoc).Offset(x - 1, 0).value = _
> Mid(sPage, IIf(S = 1, S, S + 1), h - (S + 1))
> S = h
> Else
> Exit For
> End If
> Next x
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[jmp99]

Thanks, that worked perfectly.