Stack out of Space - asking too much of VBA?

Hello again all,

Long story short, I've got a bit of code which simulates an odometer (mileage meter) by sampling a simulated speed value regularly and adding it to a running total. However, in order to achieve anything like the resolution / accuracy I need, I have to sample ten times a second. Which I appreciate is pretty demanding. I can run it for approximately a minute and a half before I get the Stack out of Space error.

So, having done a little research it seems like using Do Events in the way I have is a bit of a no-no, however, I've not been able to find an alternative that will give the same small time delays. I have also been trying to find a way to manually clear the Stack periodically in the code, but have again drawn a blank.

Could one of you possibly have a look over my code and see if there's any housekeeping that can be done that might improve things?

Would be much appreciated!

Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
Sub WasteTime(Finish As Long)
 
    Dim NowTick As Long
    Dim EndTick As Long
 
    EndTick = GetTickCount + (Finish)
     
    Do
 
        NowTick = GetTickCount
        DoEvents
 
    Loop Until NowTick >= EndTick
 
End Sub
Sub InterOdo()      'calls InterOdoSample sub after 100mS to avoid extra sample at 00:00:00 and waits 100mS between samples (executions of InterOdoSample)

WasteTime (100)
Call InterOdoSample

End Sub
Sub InterOdoSample()                    'samples the actual speed and adds it to Total & Intermediate every 100mS
Dim ForeCheck As Range
Set ForeCheck = Range("G10")
Dim FreezeCheck As Range
Set FreezeCheck = Range("G12")

If FreezeCheck.Value = False Then       'checks for Freeze tick box - should freeze both odometers
If ForeCheck.Value = True Then          'checks for Reverse tick box - shouldmake both odometers count backwards

Range("E9").Value = Range("E9").Value - (Range("P5").Value / 32850) 'subtacts (compensated) speed sample in meters/0.1sec from total value
Range("E13").Value = Range("E13").Value - (Range("P5").Value / 32850) 'subtracts (compensated) speed sample in meters/0.1sec from intermediate value

Else

Range("E9").Value = Range("E9").Value + (Range("P5").Value / 32850) 'adds (compensated) speed sample in meters/0.1sec to total value
Range("E13").Value = Range("E13").Value + (Range("P5").Value / 32850) 'adds (compensated) speed sample in meters/0.1sec to intermediate value

End If
End If

Call Exercise1                          'starts exercise 1 measurements

If [N5] > [P5] Then                                                                         'if demand is greater than actual (accelerating)
[P5] = WorksheetFunction.Min([N5], [P5] + WorksheetFunction.Min([N5] - [P5], 6 / [S5]))     'creates a rate-of-change between N5 demand and P5 actual basedon 0-60mph time in S5
Else
[P5] = WorksheetFunction.Max([N5], [P5] - WorksheetFunction.Min([P5] - [N5], 6 / [S6]))     'creates a rate-of-change between N5 demand and P5 actual basedon 60-0mph time in S6
End If

Call InterOdo                           'repeats sampling process at rate set by WasteTime()

End Sub

The sub Exercise 1 in an another module is:

Sub Exercise1()                         'code for exercise 1 - achieve 30mph average by 1 mile mark from standing start

If Range("B9") = 1 Then                 'detects total distance = 1.00 miles and copies end time to K14:
Range("K14").Value = Range("C1").Value
Call EndExercise1                       'ends exercise
End If
End Sub

Yes, OnTime rounds EarliestTime to the nearest second.

Originally Posted by creationracing

I need the worksheet to remain responsive, so using the Sleep Command is out, too. Apologies, should have mentioned this in the original post.

Your wait time is only 0.1 seconds. You need it to remain responsive for that interval? If you DoEvents between Sleeps, then there is still ample opportunity for interaction.

Originally Posted by 6StringJazzer

Yes, OnTime rounds EarliestTime to the nearest second.

Your wait time is only 0.1 seconds. You need it to remain responsive for that interval? If you DoEvents between Sleeps, then there is still ample opportunity for interaction.

The code is looping until a certain value on the odometer is reached, so that 0.1 second WasteTime interval is repeated nose-to-tail, if you like. So, apart from the time taken to run a few lines of code between loops, the code is pretty much always "Wasting Time". So it's important that the method used keeps the sheet active.

So, I've removed the InterOdo sub and written the WasteTime into the InterOdoSample sub. What's the best way to get it to loop back on itself until B19 = 1.00? A Do Until loop command?

Thanks again in advance - appreciate the guidance.

Thanks for the explanation on Loops - I'll have a look and try to tidy it up as per your advice.

Cheers for the advice so far!

Hi folks - I've used a Do Until loop to repeat the required parts of code. All working well and runs for at least 20 mins with no stack overflow errors.

I'm calling this solved - thanks a million for the advice, you're all superstars.