real time countdown timer function with ticker

**mrhzoer** · 08-23-2008, 01:27 AM

Hi everyone I need some help on a macro, This should be an easy one for you guys.

The low down:

I'm playing a game that requires me to keep track of money that is increased by a variable amount (pre-calculated and in a cell) and in a fixed time interval of 51 minutes weather I play it or not so I want to be able to keep track of the progress of the money gain outside of the game.

So, I need to know the macro coding for a real time counter that will increase the total money amount in one cell based on the variable income (declared in another cell) in the 51 minute intervals.

now... I also need excel to keep track of the value increase even if excel is closed (by the difference in time from when excel was closed last).

Keep in mind I'm a total n00b when it comes to excel and programing so please be as clear and precise as possible with the instructions. I have been trying to figure it out myself and so far I have been able to get a real time clock in a cell, and also the coding to translate time to decimals but I have yet to find a counting function in a timed interval so your help would be greatly appreciated.

Thanks.
-Scott

**Leith Ross** · 08-23-2008, 02:06 AM

Hello Scott,

Welcome to the Forum!

As a new member please read the rules for posting. Your title is very vague and not very helpful to others who may be searching for the same information. The rules will tell how to do this. If you still have problems, post back and let me know. A suggestion for your title would "How to create a real time counter".

Sincerely,
Leith Ross

**mrhzoer** · 08-23-2008, 03:25 AM

Sorry Leith.... won't let it happen again. Thats certainly understandable to help others out with the same questions. I changed the title and made it more descriptive.

**dominicb** · 08-23-2008, 04:18 AM

Good morning mrhzoer

...and welcome to the forum!!

There seems to be a couple of questions in here, but none of them are that clear - It all sounds pretty do-able and I can kind of see where you want to go but not what you;re asking.

Can we take one question at a time, if necessary post the code you have (lease use the code tags above - the # button) and we'll see if someone can help.

DominicB

**mrhzoer** · 08-23-2008, 07:39 PM

sounds good dominicb...

So I have the following code that I have been working with, It initially displayed the current time every 5 seconds in 4 descending cell rows.

I am trying to change the output to a number instead of time, and keep the value increase in the same cell (A2) and have the value increase by 1 in each of the 5 second intervals.

            ' Module level declaration of icount, inumberofcalls. This line
      ' must be at the top of the module sheet
      Dim icount As Integer, inumberofcalls As Integer

      Sub StartOnTime()

          ' Initialize icount to 1.
          icount = 1

          ' Initialize inumberofcalls to 4.
          inumberofcalls = 4

          ' Select the range of cells for formatting.
          Range("A1" & inumberofcalls + 1).Select

          ' Format the selected cells as time.
          ' Selection.NumberFormat = "1"

          ' Start in cell A1.
          Range("A1").Select

          ' Put the word "Time" in cell A1.
          ActiveCell.Value = "Time"

          ' Start the OnTimeMacro.
          Call OnTimeMacro

      End Sub

      Sub OnTimeMacro()

          ' Run the RunEvery5seconds macro inumberofcalls times.
          If icount <= inumberofcalls Then

              ' Run the RunEvery5seconds macro in 5 seconds.
              Application.OnTime Now + TimeValue("00:00:05"), _
                  "RunEvery5seconds"

              ' Increment icount by 1.
              icount = icount + 1

          Else

              ' Icount is greater than inumberofcalls, so exit the macro.
              Exit Sub

          End If

      End Sub

      Sub RunEvery5seconds()

          ' Places the current time in a cell.
          ActiveCell.Offset(icount - 1, 0).Value = Format(Now(), _
              "hh:mm:ss")

          ' Runs the OnTimeMacro again.
          Call OnTimeMacro

      End Sub

**dominicb** · 08-24-2008, 05:50 PM

Hi mrhzoer

You want the code amending so that the value will always be shown in A2, and rather than the time being shown, it is a number that increments by 1 every 5 seconds? If that's the case, does thiw work OK for you? Just bear in mind that before you run it you'll have to change the format of cell A2 from Time to something else - General or Number would be fine. the code could benefit from other tidying up, but we'll stick with the basics first :

Dim icount As Integer, inumberofcalls As Integer
      
Sub StartOnTime()
icount = 0
inumberofcalls = 4
Range("A1" & inumberofcalls + 1).Select
Range("A1").Select
ActiveCell.Value = "Time"
Call OnTimeMacro
End Sub

Sub OnTimeMacro()
If icount <= inumberofcalls Then
Application.OnTime Now + TimeValue("00:00:05"), "RunEvery5seconds"
icount = icount + 1
Else
Exit Sub
End If
End Sub

Sub RunEvery5seconds()
Range("A2").Value = icount
Call OnTimeMacro
End Sub

HTH

DominicB

**mrhzoer** · 08-25-2008, 09:25 PM

dominicb Many Thanks! This is brilliant and a Huge Help!

It was exactly what I was looking for... but I just need a few modifications. Is it possible have the code running 24/7 and keep counting up even with excel closed and the computer turned off? (i.e. Link it to the computers internal clock and update and run automatically where it left off when you open excel again).

Also whats the code for unlimited (for unlimited # of calls)? and is it possible to make a "reset to zero" button that is linked to the counter?

**mrhzoer** · 08-27-2008, 10:32 PM

dominicb...

PERFECT PERFECT PERFECT!!!! your amazing! Many Many Many thanks!!!!!!

I think this will help out a lot of people as well. Let me know if you ever need something from photoshop, flash, or maya and I'll get you the hookup.

-Scott