macro scheduling

[Ed]

Hi,

I am new to VB, and a (probably simple) problem has been driving me
crazy for several hours now.

I need to run a macro 100 times, in 1 second or less intervals (I am
sampling and storing data), and I want to do the sampling once an
hour. Any suggestions on how to do this?

Thanks!

Ed

 

[Frank Kabel]

Hi
have a look at the OnTime method. See
http://www.cpearson.com/excel/ontime.htm

 

[Guest]

try this. you will need to run one macro that will do the
following:

sub example()

while timer NOT = 10
Application.OnTime Now + TimeValue
("00:01:00"), "runExample"
timer = timer + 1
Wend
end sub()

Something like that or similar. where runExample() will
run the '1 second or less intervals'. use the onTime
again to run to 1 second or less in runExample()

 

[Ed]

Thanks for your quick reply.

I am still not quite sure how to implement the "run example" sub.
Currently I have the following code:


Sub record ()

'COPY…
'PASTE…(record procedure)

'Call record procedure after one hour
Application.OnTime Now + TimeValue("01:00:00"), "record"

End sub


This records a single snapshot of my data every hour and it works
great. However, now we want to record 50 to 100 snapshots in 1 second
intervals (so we can plot it), and it should work something like that:


Sub call_record ()

'Call record procedure after one hour
Application.OnTime Now + TimeValue("01:00:00"), "record"

End sub

Sub record()

'COPY…
'PASTE…(record procedure)

'Call itself every second
Application.OnTime Now + TimeValue("00:00:01"), "record"

End sub


As you can see, this code will not work. It will repeat "record"
forever in one second intervals. What I need is some way to stop
"record" when it executed 100 times and then return to "call_record",
so the sampling can again resume in one hour.

Any suggestions?

Thanks!

Ed