The midnight hour

[PhilT]

Hi!
I'm using Excel to constantly run a macro with an "If" command to end the macro when a "switch" cell is equal to 0. Every X seconds values from a DDE link are coppied and pasted to a new location to create a record which can be viewed on a chart. After X "records" are created the file is "Archived" to a new sheet. This is working fine for me except for one issue...if the macro is left running as the system clock goes by midnight, the macro stops running.

I don't have any code that uses time, other than to insert the current time and date into a cell as part of each "record". Any idea what could be causing this, or how to get round this?

Cheers
PhilT

[shg]

Is the macro scheduled, or does it run in a loop? What sets the switch cell to zero?

[PhilT]

Hi shg!
I've worked out that it's my use of the "Timer" variable in Excel. I'm using a Do While loop to pause the macro. The loop compares the dynamic value of "Timer" against a snapshot of "Timer" + a variable used to select the refresh rate of my recorder.

If someone can tell me the maximum value of "Timer" before it resets to 0, I can look for an invalid comparison value for "Timer" and solve my problem.

Anyone have details on the "Timer" function?

Cheers,
PhilT

[PhilT]

Hi shg!
I've worked out that it's my use of the "Timer" variable in Excel. I'm using a Do While loop to pause the macro. The loop compares the dynamic value of "Timer" against a snapshot of "Timer" + a variable used to select the refresh rate of my recorder.

If someone can tell me the maximum value of "Timer" before it resets to 0, I can look for an invalid comparison value for "Timer" and solve my problem.

Anyone have details on the "Timer" function?

Cheers,
PhilT

[PhilT]

VBA help provided the answer! Max value of "Timer" is 86400, which is number of seconds expired since the previous midnight!

[undeze]

How did you get your macro to run through midnight? I am using the following code which also stops the macro at midnight.

PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.

[arlu1201]

Undeze,

This is a 5 yr old thread and not likely to get a response.

Its better if you start your own thread. You can link back to this thread if required.