Run time error 28 Out of Stack space

[venkateshr]

Hi Experts,

need help on runtime error 28 out of stack space

This is a time taken perform activity capturing macro and it is working fine. Only when the time is crossing 9 minutes error occurs and the macro stops. I understand this is due to recursive loop can anyone suggest alternative solution to fix this please.

Private Sub StartMe()
Dim start
start = Timer
If [RunClock? ] Then
Do While (Timer < start + 1)
Do Events
Loop
If [RunClock?] Then
[Elapsedtime] = [ElapsedTime] + TimeValue(“00:00:01”)
frmCTcapture.txtCycleTime.value = Format([ElapsedTime], “hh:mm:ss”)
StartMe
End if
End if
End sub

 

[venkateshr]

Any help would be much appreciated please.

 

[Yongle]

What is the purpose of the timer ?
When is it triggered ?
Is the procedure being used with a userform ?
Are you expecting the timer to run whilst you are using your computer to do other things ?

 

[venkateshr]

Hi Yongle,

Thanks for your response,
Purpose: To capture the Time taken to perform an activity using Excel form based VBA
Yes the procedure is used in a Userform
Yes while the macro is running i would be working on other things in my laptop

Please help

 

[venkateshr]

Hi Yongle,

Thanks for your response,
Purpose: To capture the Time taken to perform an activity using Excel form based VBA
Yes the procedure is used in a Userform
Yes while the macro is running i would be working on other things in my laptop

Please help

 

[Yongle]

In that case do not use your timer
- use 2 public variables as illustrated below

Timer is a VBA function that counts the seconds since midnight

Copy code below and paste inside a new module
- and run it without amending anything

Option Explicit

Public StartTime As Double, EndTime As Double

Sub TestTimer()

    Dim TimeTaken As Double
    StartTime = Timer

    'lets waste some time by giving VBA something to do
        Dim x As Long
            For x = 1 To 10000
                Debug.Print x
            Next
   
    EndTime = Timer
    TimeTaken = EndTime - StartTime
    'reset the values to zero
    StartTime = 0
    EndTime = 0

MsgBox Round(TimeTaken / 60, 2) & " minutes"

End Sub

To apply it to your situation ...

- declare variables at top of a MODULE (not in userform code)
(which makes these variables available from anywhere in VBA)
Public StartTime As Double, EndTime As Double

- use 2 lines below in the appropriate area in your userform code
StartTime = Timer
EndTime = Timer

- calculate the difference between them

- reset both variables to 0

Why is this better
It is much simpler
The time is captured at 2 discrete points and the difference between those 2 values used
Keeping VBA running whilst using the computer for other things leaves VBA vulnerable to unpredictable events and can cause unnecessary problems, timeouts etc