CODEWITHSUNDEEP
vbaexcelmacros
mohit khullar
mohit khullar

Reputation: 11

unable to close my excel whenever there is another excel open

Whenever I try to close my macros consisting Excel file, it closes for a couple of seconds, re-opens on its own, and bugs out, giving me an object not defined error in the following code - 

Option Explicit

Public TimerCell As Range
Public TimerValue As Range
Public StopTimer As Boolean
Public EndTime As Double

Public Sub TimeCounter()
  If Not StopTimer Then
    If EndTime - Now < 0 Then
        EndTime = Now + TimerValue 'Specifically in this line
    End If
    Application.EnableEvents = False
    TimerCell = EndTime - TimeValue(Now)
    Application.EnableEvents = True
    Application.OnTime Now + TimeSerial(0, 0, 1), "TimeCounter" 'the clock runs every second
  End If
End Sub

I am using this code to run a second by second timer on the screen and refresh all my macros in 10 minutes at the completion of the timer.

It gives me an Object variable not defined error in the line notated above. Any help is appreciated.

Upvotes: 1

Views: 38

Answers (1)

RGA
RGA

Reputation: 2607

The error is caused because TimerValue has not yet been set (the instances of public variables are destroyed when excel is closed). You should either store the values in a xlVeryHidden sheet or just add a condition that checks if TimerValue is Nothing and handle it accordingly (depending on what you need TimerValue for)

Upvotes: 1

Related Questions