PDA

View Full Version : stop clock help needed!!!



y2mash
09-09-2007, 06:05 AM
Hi, im fairly new to VBA and am using it via excel! i urgently need help with a particular procedure.

How do i use a stop clock feature in vba, i dont know if there is a function which allows me to do this or not.

I want to create some code that when run, activates some sort of countdown timer which counts up in seconds, very similar to a stopwatch! When the code is stopped i want the time elapsed to be multiplied by a variable number which is already declared as a public variable in another procedure.

Therefore if the code is run for 10 seconds and the varible is 0.50 then the resulting answer would be 5.

Apologies for my lack of explanation, any help would be greatly appreciated!

Thanks

Amash

mdmackillop
09-09-2007, 06:22 AM
Hi Amash,
Welcome to VBAX

Option Explicit
Const Factor As Long = 5

Sub Swatch()
Dim tim As Long, i As Long
tim = Timer
For i = 1 To 1000
Cells(i, 1) = i
Next
MsgBox (Timer - tim) * Factor
End Sub

johnske
09-09-2007, 06:24 AM
Option Explicit

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal TimerID As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal TimerID As Long) As Long
Public TimerID As Long
Public TimerSeconds As Single
Public SecondsCount As Long

Sub StartTimer()
On Error Resume Next
TimerSeconds = 1 '< unit of time you want to work with
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
MsgBox SecondsCount * 5
SecondsCount = 0
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal TimerID As Long, ByVal dwTimer As Long)
SecondsCount = SecondsCount + 1
End Sub

y2mash
09-09-2007, 06:40 AM
Thanks ever so much for your help guys!

I'll have a wizz through those peices of code and then see what i can make of it. Will the code provided enable me to actually show the stopwatch in an excel cell visibly going up or will it be hidden?

Thanks again.

Amash.

mdmackillop
09-09-2007, 06:44 AM
Showing updates will impact speed of execution. Depending on the other code running, this could be major or trivial.
For my code try
Sub Swatch()
Dim tim As Long, i As Long
tim = Timer
For i = 1 To 1000
Cells(i, 1) = i
Cells(1, 2) = (Timer - tim) * Factor
Next
End Sub

y2mash
09-09-2007, 07:11 AM
Thanks for the help mdmackillop! However i was hoping that the code would not run until 1000 numbers would be displayed, rather that it would stop when i clicked another command button to stop it. therefore the timer figure would keep running until i stopped the program by clicking another button.

Thanks ever so much.

Amash.

johnske
09-09-2007, 07:16 AM
changeSub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal TimerID As Long, ByVal dwTimer As Long)
SecondsCount = SecondsCount + 1
End Subto

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal TimerID As Long, ByVal dwTimer As Long)
SecondsCount = SecondsCount + 1
ActiveSheet.Range("A1") = SecondsCount
End Sub
you'll need to activate EndTimer with a button

y2mash
09-09-2007, 07:27 AM
Option Explicit

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal TimerID As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal TimerID As Long) As Long
Public TimerID As Long
Public TimerSeconds As Single
Public SecondsCount As Long

Sub StartTimer()
On Error Resume Next
TimerSeconds = 1 '< unit of time you want to work with
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
MsgBox SecondsCount * 5
SecondsCount = 0
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal TimerID As Long, ByVal dwTimer As Long)
SecondsCount = SecondsCount + 1
End Sub



I tried this with two buttons, one for start timer and one for end timer. when i start the timer and then click end timer button, it returns a value, but how do i display the actual timer in a cell, increasing in real time during the program, which will stop at a certain time (seconds) when the end timer button is clicked?

I apologise for any inconvenience!

Thanks again.

Amash.