Stack overflow in VBA

[dgs2001]

Hi

Help please

How does a 'Stack Overflow' in excel occcur.
This is the latest error I'm getting

Frustration is setting in deep now I think I'm about ready to dump my sheets and try knitting denouncing gambling as the work of Satan in the process

George UK made a point about compiling my code then f8'ing through it in a seperate post, should a compile be done whenever you save or change code ??
If that is the case then this could be part of the problem.

Thanks in advance Duncan

[PeteB]

Stack overflow - maybe you have coded an infinite loop? Or a bit of code recursively calls itself?

If you change code and then run it, it always compiles it first.

You may find it useful to use a version control system - I use Perforce (Subversion would be fine also) - every time you have something that works, submit it with a description - then if you break something you can always go back.

[GeorgeUK]

Agree with Pete. (Not encountered a stack error for years - sorry)

the stack will empty as the functions end, but if you have functions that don't end, the stack will continue to fill, getting bigger and finally run out of memory.

eg
sub one()
call two
end sub

sub two()
call one
end sub

macro 1 runs, calling macro 2, which calls 1, which calls 2, which calls...

this will create a stack error.

If you are trying to loop something, it may be that you need to reset something in your code so that when the loop starts again a value is reset to 0

[dgs2001]

Anyone need a jumper ??

Maybe a nice knitted scarf ??

How about some knitted mitten NO NO NO STOP

I think the problem is that I have two worksheets with the same active race on and they both have Worksheet Calculate() subs which refer to each other and contain static variables so that would explain the loop thing and the stack filling with knit one pearl one.
I'm going to start again and ask for help when I get confused.

Thanks Duncan

[GeorgeUK]

Can't help it... got to ask...

Why do you have the same race being fed to 2 worksheets?
Just curious.

For those just starting coding.
What i used to do when i was going to start a programme:
- write what you are trying to achieve in pseudo code, possibly in steps.
1. login to racingpost
2. get racedata to excel
3. repeat step 2 every 5 seconds

- expand on the pseudocode and include it in the macro
(put ' before any text makes it a comment that won't affect the running of the macro. Use this to make notes or add comments)
1. login to racingpost
- ensure library references are set for internet explorer and html
- open ie
- goto racingpost login page
- enter login details
2. get racedata to excel
- go to results page
- put data into excel
3. repeat step 2 every 5 seconds
- goto 2
- Add some kind of way to stop update

If you do this, and put these details in as notes, then it's usually quite easy to see where you are going wrong if things don't work. Just break the problem down into smaller and smaller parts.