PDA

View Full Version : [SOLVED] Seconds Counter



maninjapan
09-19-2008, 05:51 AM
I need a timer as part of a function to update a cell every X seconds, I currently use a clock function to keep time. I thought this solved my problem, however it is only good for up to 60 seconds.
I would like to add a cell that just keeps counting the seconds.
For example from 09:05:00 - 09:09:00 would show as 240.
Any help on this would be much appreciated.

Bob Phillips
09-19-2008, 06:10 AM
Option Explicit

Public nTime As Double

Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "RunTimer"
End Sub

Sub StopTimer()
Application.OnTime nTime, "RunTimer", , False
End Sub

maninjapan
09-23-2008, 12:56 AM
Thanks XLD, thats just what I wanted. What would be the line of code to add under a command button to execute this ?

Bob Phillips
09-23-2008, 01:06 AM
Add two buttons from the forms menu, and assign RunTimer to one and StopTimer to another.

maninjapan
09-23-2008, 01:16 AM
Sorry XLD, yeah Im not quite sure how to actually assign it.....

Do I just add the code under the button_Click?? (somehow I dont think so)



Private Sub CommandButton1_Click()

Option Explicit

Public nTime As Double

Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "RunTimer"
End Sub

Bob Phillips
09-23-2008, 01:32 AM
You don't. If you create buttons from the forms toolbar as I suggested, when you copy it from the toolbar to the worksheet, it will open up a dialog to asign a macro. That is when you selct the RunTimer macro and so on.

maninjapan
09-23-2008, 01:51 AM
Ok got it, thanks for your help (again). What would I add to reset the timer when it stops?

Bob Phillips
09-23-2008, 02:02 AM
Sub ResetTimer()
Range("A1").Value = 0
call RunTimer
End Sub

maninjapan
09-23-2008, 02:14 AM
Thanks again. I noticed though that after just clicking the reset button It would start counting in 2's and 3's each second. As if it was running the timer 2 or 3 at a time.

Bob Phillips
09-23-2008, 02:44 AM
I had assumed you would stop the timer before reset, but I guess you didn't, which means that you had two timers running, hence the od behaviour.

We can force a stop on reset to be sure



Sub ResetTimer()
Call StopTimer
Range("A1").Value = 0
Call RunTimer
End Sub

maninjapan
09-23-2008, 02:50 AM
XLD, yes thats what I would normally do. I just found it out by mistake actually, but this makes it easier still.

I would a cell to return TRUE/FALSE every N seconds of the timer.

A1 = TIMER A2 = Seconds

=IF(A1/A2 = 'Whole Number', TRUE,FALSE)

Bob Phillips
09-23-2008, 03:25 AM
Sorry, you have lost me with that last post.

maninjapan
09-23-2008, 03:34 AM
Sorry. Now that I have the timer working in A1 Im trying to get a TRUE/FALSE statement working in A2. So if B1="10" then every 10 seconds A2 will change from "FALSE" to "TRUE". Hope that makes sense....

Bob Phillips
09-23-2008, 03:59 AM
So, are you saying that what you really want is to put a value in B1, and when you start, reset the timer, it alternates A2 between TRUEand FALSE every B1 seconds?

maninjapan
09-24-2008, 12:22 AM
So, are you saying that what you really want is to put a value in B1, and when you start, reset the timer, it alternates A2 between TRUEand FALSE every B1 seconds?

That is correct. every B1 seconds A2 will show true, the rest of the time it will show false

Bob Phillips
09-24-2008, 12:43 AM
Public nTime As Double

Sub RunTimer()
Range("A2").Value = Not CBool(Range("A2").Value)
nTime = Now + TimeSerial(0, 0, Range("B1").Value)
Application.OnTime nTime, "RunTimer"
End Sub

Sub StopTimer()
Application.OnTime nTime, "RunTimer", , False
End Sub

Sub ResetTimer()
Call StopTimer
Range("A2").Value = False
Call RunTimer
End Sub

maninjapan
09-24-2008, 12:50 AM
XLD, thats getting close. if B2 = 10 then it only shows true every 10th second. So it would show FALSE for 9 seconds and the TRUE for the 10th second. Having the timer actually still showing in A1 would also be a help to see where it is at.

I tried the following, but the timer in A1 doesn't count every second if I change B1


Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
Range("A2").Value = Not CBool(Range("A2").Value)
nTime = Now + TimeSerial(0, 0, Range("B1").Value)
Application.OnTime nTime, "RunTimer"
End Sub

Bob Phillips
09-24-2008, 01:16 AM
Public nTime1 As Double
Public nTime2 As Double

Sub StartTimers()
Range("A1").Value = 0
Call RunTimer1
Range("A2").Value = False
Call RunTimer2
End Sub

Sub RunTimer1()
Range("A1").Value = Range("A1").Value + 1
nTime1 = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime1, "RunTimer1"
End Sub

Sub RunTimer2()
Range("A2").Value = Not CBool(Range("A2").Value)
nTime2 = Now + TimeSerial(0, 0, Range("B1").Value)
Application.OnTime nTime2, "RunTimer2"
End Sub

Sub StopTimer()
Application.OnTime nTime1, "RunTimer1", , False
Application.OnTime nTime2, "RunTimer2", , False
End Sub

Sub ResetTimers()
Call StopTimer
Range("A1").Value = 0
Call RunTimer1
Range("A2").Value = False
Call RunTimer2
End Sub

maninjapan
09-24-2008, 01:38 AM
cool, i got the timer up now, I just need the cell to show TRUE every B1th second. for example if B1 = 5 :
FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE

currently if B1 = 5:
TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE

Bob Phillips
09-24-2008, 02:03 AM
Public nTime As Double

Sub StartTimer()
Range("A1").Value = 0
Range("A2").Value = False
Call RunTimer
End Sub

Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
If Range("A1").Value Mod Range("B1").Value = 0 Then
Range("A2").Value = True
Else
Range("A2").Value = False
End If
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "RunTimer"
End Sub

Sub StopTimer()
Application.OnTime nTime, "RunTimer", , False
End Sub

Sub ResetTimer()
Call StopTimer
Range("A1").Value = 0
Range("A2").Value = False
Call RunTimer
End Sub

maninjapan
09-24-2008, 02:31 AM
Thanks XLD, works just as it should now. Id like to add an error box if B1 is empty.
I tried adding the following to the code but it wouldn't compile


Sub StartTimer()
If Range("B1") = " " Then _
MsgBox "Add seconds!", vbInformation, "Error Message"
Exit Sub
Else
Range("A1").Value = 0
Range("A2").Value = False
Call RunTimer
End Sub

maninjapan
09-24-2008, 02:50 AM
Excellent, works good now. Id like to add an MSG Box in case B1 is empty

I tried the following, but it wouldnt compile correctly



Public nTime As Double

Sub StartTimer()
If Range("A1").Text = " " Then
MsgBox "Enter Data!", vbInformation, "Error Message"
Exit Sub
Else
Range("A1").Value = 0
Range("A2").Value = False
Call RunTimer
End Sub

Bob Phillips
09-24-2008, 03:26 AM
Option Explicit

Public nTime As Double

Sub StartTimer()
If Range("B1").Value = "" Then
MsgBox "Enter Interval Data!", vbInformation, "Error Message"
Else
Range("A1").Value = 0
Range("A2").Value = False
Call RunTimer
End If
End Sub

Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
If Range("A1").Value Mod Range("B1").Value = 0 Then
Range("A2").Value = True
Else
Range("A2").Value = False
End If
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "RunTimer"
End Sub

Sub StopTimer()
Application.OnTime nTime, "RunTimer", , False
End Sub

Sub ResetTimer()
Call StopTimer
If Range("B1").Value = "" Then
MsgBox "Enter Interval Data!", vbInformation, "Error Message"
Else
Range("A1").Value = 0
Range("A2").Value = False
Call RunTimer
End If
End Sub


BTW, what is this code being used for?

maninjapan
09-24-2008, 03:37 AM
Its for a trading spreadsheet. It updates the orders every N seconds.
Is there anyway to stop a Visual basic error coming up if I press the stop button or reset button twice?

I am also trying to set this to multiple cells so I think it would be easier if I could just add the True,False section as a formula to each cell.


=IF(Range("A1").Value Mod Range("B1").Value = 0,TRUE,FALSE)

Obviously this is incorrect but this is what I would like to do

Bob Phillips
09-24-2008, 03:59 AM
See if this works for you



Sub RunTimer()
On Error Resume Next
Range("A1").Value = Range("A1").Value + 1
If Range("A1").Value Mod Range("B1").Value = 0 Then
Range("A2").Value = True
Else
Range("A2").Value = False
End If
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "RunTimer"
End Sub

maninjapan
09-24-2008, 04:20 AM
xld, thanks. It didnt work where it was. But moved it to the following and it worked fine


Sub StopTimer()
On Error Resume Next
Application.OnTime nTime, "RunTimer", , False
End Sub

maninjapan
09-24-2008, 04:28 AM
Dont suppose you would know the correct way to put this if statement into a cell?


=IF(Range("A1").Value Mod Range("B1").Value = 0,TRUE,FALSE)

Bob Phillips
09-24-2008, 04:29 AM
What the OnError? You can't put that in a cell, it is pure VBA.

maninjapan
09-24-2008, 04:58 AM
Not the on error, the following statement. It is currently in the VBA, but I would like to have the TRUE/FALSE switch in multiple cells off one timer.
This is obviously incorrect, but I hope it gives you an idea. I think this would be easier than creating a seperate macro for every cell.

[A2]
=IF(Range("A1").Value Mod Range("B1").Value = 0,TRUE,FALSE)
[A3]
=IF(Range("A1").Value Mod Range("C1").Value = 0,TRUE,FALSE)
[A4]
=IF(Range("A1").Value Mod Range("D1").Value = 0,TRUE,FALSE)

etc...
etc...

Bob Phillips
09-24-2008, 05:52 AM
Or are you asking for a way to stop the timer if a cell has a certain value?

Bob Phillips
09-24-2008, 05:56 AM
Change the code to



Public nTime As Double

Sub StartTimer()
If Range("B1").Value = "" Then
MsgBox "Enter Interval Data!", vbInformation, "Error Message"
Else
Range("A1").Value = 0
Call RunTimer
End If
End Sub

Sub RunTimer()
On Error Resume Next
Range("A1").Value = Range("A1").Value + 1
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "RunTimer"
End Sub

Sub StopTimer()
Application.OnTime nTime, "RunTimer", , False
End Sub

Sub ResetTimer()
Call StopTimer
If Range("B1").Value = "" Then
MsgBox "Enter Interval Data!", vbInformation, "Error Message"
Else
Range("A1").Value = 0
Call RunTimer
End If
End Sub


and use a formula of


=NOT(MOD(A1,B1))=NOT(MOD(A1,B1))

I am fascinated to know what the actual, final requirement is going to be!

maninjapan
09-24-2008, 06:10 AM
xld, I promise I will show you a working example (as best I can ) once Im done with it. I dont want it to stop, just keep going and going showing TRUE every b1th seconds (jsut like it does now)
the timer works fine as you have it set, but currently it only works on 1 value at the moment 'B1'. I would like to have many cells running at the same time using different second values in each one.
So basically take this part out of VBA

If Range("A1").Value Mod Range("B1").Value = 0 Then
Range("A2").Value = True
Else
Range("A2").Value = False
End If
and be able to put it in a cell(multiple Cells actually). Would something like the following work?

=IF(MOD(A1,B1)=0,TRUE,FALSE)
=IF(MOD(A1,B2)=0,TRUE,FALSE)
=IF(MOD(A1,B3)=0,TRUE,FALSE)

Bob Phillips
09-24-2008, 06:18 AM
Try it and see!

I would add CF to the TRUE/FALSE cells to highlight when it goes TRUE.

BTW that was an ironic comment, whilst the main requirement is similar to what was asked for, you moved it along after every answer, I just wonder what you really want.

maninjapan
09-24-2008, 08:21 AM
Yeah sorry XLD, I didnt explain myself in the beginning, and I keep thinking that if I get the next bit I can figure the rest out myself. But its not working out like that. Sorry I dont mean to drag it out like this. The Following now works fine

=IF(MOD(A1,B1)=0,TRUE,FALSE)

BTW, What is CF?? Color Font?

Bob Phillips
09-24-2008, 10:31 AM
No, CF is conditional formatting.

And you can simplify the formula


MOD(A1,B1)=0

maninjapan
09-25-2008, 01:24 AM
how about if I only want to apply it to a specific spreadsheet ?

maninjapan
09-25-2008, 02:01 AM
sorry figured it out


Worksheets("Sheet1").Range("I4").Value = ""

Adonaioc
09-25-2008, 06:48 AM
I find this very interesting, May i ask what practical application this has? Just curious.

maninjapan
09-29-2008, 01:08 AM
Adonaioc, Its for a Trading spreadsheet that updates orders every N seconds. however its also my first attempt at building anything meaningful with VBA so its just as much a learning excercise for myself.
I have attached a basic setup of how it would be used minus all the prices etc (It needs a live price feed through the RTD function to get them anyway)

The logic is basically IF range("C4").Value = true THEN do something (in this case update price)

XLD, thanks for you help again.

Bob Phillips
09-29-2008, 02:11 AM
It looks pretty good doesn't it? :)

How do you get it to feed as and when the value turns TRUE, I presume you have automated this as well?

maninjapan
09-29-2008, 02:23 AM
It looks pretty good doesn't it? :)


How do you get it to feed as and when the value turns TRUE, I presume you have automated this as well?
I guess as my first real project I cant complain. thanks.
Using RTD I get live prices feed into the spreedsheet from my trade software.
I use a set formula to subtract a certain amount from the price and this total is linked back to the order book in the software for my buy order (this is a link function in the software so its as easy as copy and paste) and then every time the cell returns true it updates the order price and then this is reflected in the order book of my trading software.

Hope that makes sense........

Bob Phillips
09-29-2008, 02:27 AM
Yes it does.

I wrote an app back in the late 80s that was taking real-time feeds from many sources (onto a Tandem mainframe) and flashed them out to multiple dealer workstations. This was the first system that I ever saw with multiple screens, we had to build the hardware and the software to manage it ourselves, so it is a very familiar concept, we just didn't have anything that update the prices on a pre-set time basis, we updated them all as and when they changed.

maninjapan
09-29-2008, 03:13 AM
This one sounds a bit easier..... This one is just meant to be a local app for the trader not exactly breaking new ground here I know and it wont exactly replace the trader just meant to make things a bit easier.