Out of stack space using Instr

[MadForIt]

Hi, Can anyone suggest why the following code behaves as it does. When run for the first time i get an 'Out of Stack Space' error. If i End the code and run it again any number of times thereafter it runs okay! Any ideas plus possible solutions to rectify would be most appreciated. I am running the code using Excel 97.


Sub test()
Dim abc as String, pos as Long
abc = Space(260000) & " vba vba vba"
pos = InStr(1, abc, "vba", 1)
End Sub

PS. abc would usually be a string read from a text file of similar length. Spaces used here just to replicate the error.

 

[Chance1234]

mmmm stack is memory, try adding

Doevent to your code,

also , application.screenupdating = false at the start
and application.screenupdating = true at the start

Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM

 

[MadForIt]

Thanks Chance1234. Did you get it to work okay on your system using your suggestions, as the error still occurs on mine.

Thanks.

PS I know it works okay using Excel 2000, so it must be a bug in Excel 97.

 

[Chance1234]

yeap worked fine.

Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM

 

[MadForIt]

Chance1234
Would it be possible for you to post your code, as i must be doing something wrong!

Thanks in advance.

 

[Chance1234]

Sub test()
Dim abc as String, pos as Long
application.screenupdating = false
doevents
abc = Space(260000) & " vba vba vba"
pos = InStr(1, abc, "vba", 1)
application.screenupdating = true
End Sub

Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM

 

[MadForIt]

Chance1234

Still comes up with an error on mine. Must be just the combination of Excel97 and Win95/98 as i have just tried it with Excel97 on an NT machine, without the DoEvents and Screen updating False/True, and it works fine!

Think i might give up!!