Excel stalls after 7-8 VBA subs are called in a row

[BetterBeef]

I have a sub in Excel which calls 12 other subs from within my current VBA code. These subs are called right in a row. However, once the 7th or 8th sub is called, Excel freezes and I have to go into Task Manager and manually close out Excel. If I try this again, I get the same result.

Is there something I need to do (like add a pause) to allow for all 12 subs to run correctly?

Thanks,
Chris V.

 

[sous2817]

Sounds more like an issue w/ your macro and not the # of macros. can you run them individually without issue?

 

[BetterBeef]

My macro works fine. I can run up to 5 of the macros with no problems. 12 is just too much. Running each macro separately runs without a hitch. Note: I am also running Excel 2010.

 

[BetterBeef]

Using the following formula, I was able to get it to work.

Sub Wait(tSecs As Single)
' Timer to create a pause
Dim sngSec As Single

sngSec = Timer + tSecs
Do While Timer < sngSec
DoEvents
Loop
End Sub

I use this wait function every 4 or 5 functions that I call (wait for one second), and I do not have any problems. I still don't understand why this is a problem. In theory I should be able to call an infinite number of subs without needing a pause. If anyone has an answer to this, I would love to know!

 

[Norie]

Chris

What do the macros do?

Can you post any of the code?

Have you tested running only one of them, then running 2 of them, then 3 of them and so on?

That might give you some idea if it's one particular sub/macro that's causing the problem.

 

[BetterBeef]

As I said before, all my code runs fine. There just seems to need to be a pause of some sort for everything to run correctly.

 

[BetterBeef]

The only thing I would like to add is that each macro that I have basically pulls a report from the web (.txt file), and parses it out for user readability. I don't know if going to the web is the reason I need the pauses or not.

 

[Norie]

If you need to pause there must be something in the code you need to pause for.

There might be some other way to deal with it, there might not - perhaps you could use Application.Wait

Anyway, looks like you found a solution.

 

[shg]

If there are points at which you are changing stuff that requires recalculation, and then require the results of that calculation to continue, then you could instead use

    Calculate 
    Do Until Application.CalculationState = xlDone
        DoEvents
    Loop

To Norie's point, knowing when the process dies would help in understanding the issue. You could send progress messages to the Immediate window and see how far things get before it goes Tango Uniform.

 

[Norie]

Actually, if you are getting data from the web there might be other ways to deal with the pause.

That would depend on what method you were using for that part of the process.