how can i make it so that my macro called calc_effective runs each 2 mins.

[david gabra]

Hi

How can i make it so that my macro called calc_effective runs each 2 mins.

I try and I get a error mesage - it will open and run a macro once but it it will not keep running.

Please, anyone with better code.

thanks

 

[BobBridges]

Probably not what you want, but if you're willing to use Windows' task scheduler then you can convert your macro to VBScript and have the scheduler run that program at any interval you like. At least, I assume you can; I haven't yet used it.

If by "every two minutes" you mean you're planning to sit watching it, leaving Excel open, then, let's see.... Oops, I can't open your attachment to see what you're doing; it wants me to log on to my MS account, which I never do. Interesting, I've never seen that requirement before. But it shouldn't be hard to use the Timer function to accomplish this, with a little experimentation.

 

[Marc L]

Hi, did you try the Application.OnTime method as explained in VBA help ?​

​

 

[david gabra]

yes - errors galore.
this is unencrypted file

 

[david gabra]

it worked only when I opeded and did not do on a continual basis.

 

[david gabra]

Probably not what you want, but if you're willing to use Windows' task scheduler then you can convert your macro to VBScript and have the scheduler run that program at any interval you like. At least, I assume you can; I haven't yet used it.

If by "every two minutes" you mean you're planning to sit watching it, leaving Excel open, then, let's see.... Oops, I can't open your attachment to see what you're doing; it wants me to log on to my MS account, which I never do. Interesting, I've never seen that requirement before. But it shouldn't be hard to use the Timer function to accomplish this, with a little experimentation.

Please if you can try on my excel thanks

 

[p45cal]

In the attached:
Button at cell Y3 of the Testing_Master_Sheet sheet whose caption changes
Code in Module1:
Module-wide variables declared at the top of the module
Suggested alternative codes for your calc_effective sub. I've used calc_effective3
Additional subs to control updating.
For testing, the updating is every 10 seconds, change the "00:00:10" in
NextSchedule = Now() + TimeValue("00:00:10")
to
"00:02:00"
for two minutes.

 

[david gabra]

In the attached:
Button at cell Y3 of the Testing_Master_Sheet sheet whose caption changes
Code in Module1:
Module-wide variables declared at the top of the module
Suggested alternative codes for your calc_effective sub. I've used calc_effective3
Additional subs to control updating.
For testing, the updating is every 10 seconds, change the "00:00:10" in
NextSchedule = Now() + TimeValue("00:00:10")
to
"00:02:00"
for two minutes.

thanks - are you able to make it so that we can automatically have the macro run when the file is open and then each 20 secs after that - without the user having to press start stop.
I understand that you need to add a ' call the routine' in the worksheet VBA - but did not work for me.

thanks

 

[Marc L]

The demonstration within the below attachment is launched every five seconds until the workbook is closed …​

 

[david gabra]

The demonstration within the below attachment is launched every five seconds until the workbook is closed …​

there is an error

 

[Marc L]

As it well works on my side since more than a decade …​

​

 

[Marc L]

Another five seconds demonstration well working on my side :​

 

[david gabra]

thats cool - workinog now.

 

[Marc L]

As post #12 demonstration uses the same specific code than post #9 to launch a procedure every five seconds …​

​