Need help in Excel VBA programming

[Wongo]

I know you guys are smart and I need your help. Is it possible to run a procedure say every 5 minutes (with the file open obviously). Some sort of timing control like the "timer" in Visual Basic.

Thanks

[Daddles]

Find brick wall. Bang head repeatedly against wall. When you awaken, it is time to run procedure again.

Richard
modesty forbids me to take financial reward for this advice:)

[Wongo]

Thanks Richard. Just send me an invoice mate.

Just found this and it is probably something I can work with

Wait Method
See Also Applies To Example Specifics
Pauses a running macro until a specified time. Returns True if the specified time has arrived.

Important The Wait method suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer while Wait is in effect. However, background processes such as printing and recalculation continue.

expression.Wait(Time)

expression Required. An expression that returns an Application object.

Time Required Variant. The time at which you want the macro to resume, in Microsoft Excel date format.

Example
This example pauses a running macro until 6:23 P.M. today.

Application.Wait "18:23:00"
This example pauses a running macro for approximately 10 seconds.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
This example displays a message indicating whether 10 seconds have passed.

If Application.Wait(Now + TimeValue("0:00:10")) Then
MsgBox "Time expired"
End If

[Grunt]

It's a sad day when you have to program Excel in VBA.

[Cliff Rogers]

Why not just set the timer on the microwave oven? :cool: :D

[chrisb691]

What you're looking at is something like:-

Sub Macro1()
Timer1
End Sub

Sub Timer1()
Application.Wait Now + TimeValue("00:10:00")
Macro1
End Sub

Here the macro's first run triggers the timer, which in turn calls the macro on timeout. The problem is that you can't do anything whilst the the timer is running, and the only way out of the loop is with ctrl +alt +break. So it's not very useful. :(

[Wongo]

Cheers Chris (not you Grunt :D )

Harsh words Grunt. I don’t use it very often but some of my finest work is done in Excel VBA. Some users are just so comfortable with columns and rows you know.

[Wongo]

Got it.

Private Sub doThis()
    Dim PauseTime, Start

    PauseTime = 1 ' Set duration.
    Start = Timer ' Set start time.
&nbsp;&nbsp;&nbsp;&nbsp;Do While Timer < Start + PauseTime
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoEvents ' Yield to other processes.
&nbsp;&nbsp;&nbsp;&nbsp;Loop
&nbsp;&nbsp;&nbsp;&nbsp;Sheet1.Range("A1") = Now
&nbsp;&nbsp;&nbsp;&nbsp;Call TryAgain
End Sub


Private Sub TryAgain()
&nbsp;&nbsp;&nbsp;&nbsp;Call doThis
End Sub

[AlexS]

Call yerself a code cutter? If yer can't do it with edlin & Fortran, it ain't worth doin'.:D

[Wongo]

We are going to run a conference in a hotel. During the 2 days conference, there will be 2 computers outside the conference room so our clients can answer a simple online questionnaire. Inside the conference room there is another computer and they want to use Excel to show some graphical statistics of the questionnaire as the conference progresses.

I am simply trying to do what they want. Anyway I’ve got it sorted.:cool:

[Daddles]

Anyway I’ve got it sorted.:cool:

Found a soft wall did you Wongo? :D

Richard

[chrisb691]

Call yerself a code cutter? If yer can't do it with edlin & Fortran, it ain't worth doin'.:D

Ye Gods....Edlin. That ages you nicely. :D