Application.Ontime 'Compile Error' variable not defined

[Calum]

Hello, please can anyone help?

I've defined within a module,

>>> use code - tags <<<

Option Explicit
Dim NextTick As Date

And am running a small block of code with comments as follows:

Sub UpdateClock()

' using the OnTime event to execute UPdateClock procedure by self calling it
' into a cell (range) named CurrentTime in a worksheet titled Chapters_and_code_trialed

' I'll use a Button to toggle this on assigning UpdateClock(). Use one to toggle it off, StopClock()

'Code reads 
   
    Worksheets("Chapters_and_code_trialed").Range("CurrentTime").Value = Time
  
'Set up the next event five seconds from now

    NextTick = Now + TimeValue("00:00:05")
    Application.OnTime EarliestTime:=NextTick, Procedure:="UpdateClock"

End Sub

When I do I get the Compile Error Window with "UpdateClock" highlighted as an undefined variable.

Any ideas please?

 

[vletm]

Calum
Have You copied Your code somewhere?
Do it really need to run itself after every five seconds?

 

[Calum]

I have tried the code;
In a module, workbook and worksheet.
When doing so I’m putting everything in to that object. That is declared variable in option explicit area and sub routine.

I don’t need it to run at all really. I am just working through methods properties etc and I’m having issues specifically with ontime that I can’t resolve currently.

thanks for any help you can provide

 

[vletm]

Calum
I (tried to) ask two questions.
The 1st one - no answer.
The 2nd - if You do not need that code at all - then what is Your point?

 

[Calum]

Sorry if you thought I had not replied inappropriately.

Your first question - Have You copied Your code somewhere? To which my reply was:

I have tried the code in a module, a workbook and worksheet.
When doing so I’m putting everything in to that object.
That is declared variable as in my opening question example in option explicit area.
The code lines are then in the sub routine in that object; Workbook, Worksheet or Module.

Your second question - Do it really need to run itself after every five seconds?

I am not working on site at present due to COVID. I see that I can set the code to run at a preset time or I could even trigger the time code activity on some other action working. E.g. where I write code supporting an output on a spreadsheet that is using OS PI. So a client make get to a fixed product volume or another process condition.

Therefore at this time I DO NOT NEED THE CLOCK but I do need to know how the OnTime activity works. IF I WISH to use it in the future.

So I want to know how I can get the OnTime method to work.

 

[Calum]

Again really appreciate your time and the reply, sorry if there was any confusion

 

[vletm]

Calum
1) Copy
... seems that You have copied some code somewhere and after that You have modified it.
... it's someway normal - if You guess - what are You doing - modified code won't work.

2) Need
... as You wrote - I don’t need it to run at all really. ... then why ask?

3) Future:
If You would like to use OnTime then
You could search ideas
a) from this forum
eg >> https://chandoo.org/forum/threads/application-ontime-vba-help.1801/
b) from 'google'
There are a lot of samples which should work.
eg https://stackoverflow.com/questions...all-a-macro-at-a-set-time-everyday-without-ha
eg http://www.cpearson.com/excel/OnTime.aspx

Question > Answer based question​

 

[Calum]

Vletm,

Firstly thanks. The eg http://www.cpearson.com/excel/OnTime.aspx has been the resolve.

Back to your points though just for your reference.

1) The code is taken verbatim from VBA for Dummies whilst reviewing it and VBA Power Programming.
Pg.180 VBA for Dummies has the exact example I used to start; included opening request.

2) Need. As I say I can see a use for it when I get to a site facility and in draughting some code to undertake tasks would wish to time schedule an event. This would be a very good construct to do so and now, with your help I can execute using OnTime.

3) The resolution from the site above was to add 2 other defined variables (all of type public) and another sub routine to call the updateclock.

Thanks once again. Best wishes, Calum