Timer: I'm confused

[Johnegee]

Hello, all,

I have spent perhaps a couple hours searching the Web and trying various suggestions, and I can't seem to figure out how to implement a timer in Excel 365. I know it should be simple but I'm still confused.

The exact implementation I'm hoping for is pretty straightforward. I have written a Tip Generator that uses the random number generator native to Excel to determine whether my tip should be $5 or $10, and to insure true randomness I do use the Randomize function or formula or whatever it is, which I think relies on the exact moment when the clickable grey box is touched on my touch-screen laptop, which fires up a macro that simply generates the random number.

Because so few of the servers understand what to do -- despite my several attempts to simplify it -- I have taken the suggestion to implement a timer. The idea is for a server to touch the grey box that starts a timer that counts down five seconds and then, based on whether the random number is greater than 0.50, paints to the screen the new random number and whether the tip that time is $5 or $10.

What I can't seem to figure out is how, once the timer has counted down from 5 to 0, to get the random number and the resulting tip to paint to the screen and then stop. I suppose the macro could also paint to the screen something along the lines of "The random number you generated is [greater than] [less than] 0.5000, so your tip is [$5] [$10]," but that sort of thing is barely within my capabilities. What's not is how to get something -- anything -- to happen once the timer has gone from 5 to 0.

I assume these processes must be performed with one or more macros, but I'm still confused. Thanks for any ideas you can offer.

 

[JLGWhiz]

Not really sure I grasp the issue, but the timer runs constantly from 00:00 to 23:59:59.99, or something of that nature. It does not start at zero when you address it. If you want to use only a segment of time like 5 seconds then

s = Timer + 5
Do while Timer < s
    DoEvents
Loop

When the timer value is equal to or greater than the value of s, the loop terninates.

I don't know if this clears the fog or makes it worse, but that is how I understand the timer.

 

[Johnegee]

JLGWhiz, I'm afraid your advice, however good it might be, makes the fog denser. For one thing, I do not find anything offered by Excel called Timer. Perhaps you meant I should insert some sort of timer ability where you say "Timer," but that doesn't get me close enough to make the whole thing work. Indeed, that's the very problem.

I apologize for not being clear enough for you to "grasp the issue." Perhaps you or some other knowledgeable expert could ask me a pointed question or two so I can help you understand.

I want code that starts a countdown timer that runs, ideally visibly, from five seconds to zero seconds and then generates a random number, which then spits out the tip amount of $5 or $10. I've got the random number part figured out, and I can turn that number into the appropriate tip. What I can't figure out is how to implement the timer part that starts the process.

The way I foresee its working is that the server touches a box on my touch-screen laptop, which starts a visible timer that drops from five (or four or three) seconds to zero seconds, which then generates a random number, which then gets painted to the screen along with the amount of the tip that server gets.

I'd be happy to show you the spreadsheet and the macro that makes it go, but I don't know how to do that and I'm not sure it's necessary.

 

[Logit]

.

Option Explicit

Const idleTime = 5 'seconds
Dim Start

Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
    
    Sheets("Sheet1").Activate
    Sheets("Sheet1").Range("A1").Select
   
End Sub

 

[Johnegee]

Logit, I'm still confused. I copied your code and corrected as many error messages as I could, although I don't know that I corrected them correctly, and I never could get it to work. Is the Sub titled Start or StartTimer or Timer? Where do I insert the code that generates the random number? So many questions, so little understanding. Please treat me like the seven-year-old that I might as well be.

Incidentally, good avatar.

 

[sykes]

Hi
Try taking Logit's code, and putting the constant & timer inside the sub.

If you put this into your workbook's VBA module, and press F5 to run, it should work (make sure your cursor's somewhere in the code before you press F5):

Sub StartTimer()
Const idleTime = 5 'seconds

    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
    
    Sheets("Sheet1").Activate
    Sheets("Sheet1").Range("A1").Select
   
End Sub

Before running, make sure you have a sheet called "Sheet1", also select another sheet and a cell within that sheet, before running the code. That way, if after 5 secs Sheet1's activated, and A1 selected, you know it's worked.

 

[Logit]

.
The macro is to be pasted into a Regular Module: How to insert and run VBA code in Excel - tutorial for beginners


Add a button to your sheet : How To Add A Form Control Button To Run Your VBA Code | How To Excel

Assign the button to the macro STARTTIMER.


The macro is already designed for counting down from 5 seconds. No need to edit the macro in that regard.

Option Explicit

Const idleTime = 5 'seconds
Dim Start

Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop

    'Your code for creating a random number goes here.
    
    'Here is a suggestion :
    Dim randomNumber As Integer
    randomNumber = Int(2 + Rnd * (30 - 2 + 1))
    MsgBox randomNumber
End Sub

The last portion of the macro that creates a random number came from here : VBA Random Number - Automate Excel


The random number portion is not the final answer for your needs. You will need
to hone it down with the help of others.

 

[JLGWhiz]

JLGWhiz, I'm afraid your advice, however good it might be, makes the fog denser. For one thing, I do not find anything offered by Excel called Timer. Perhaps you meant I should insert some sort of timer ability where you say "Timer," but that doesn't get me close enough to make the whole thing work. Indeed, that's the very problem.

This link will explaing the Timer function.

Timer function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

It would require code to display a count down from 5 To 1

Sub t()
For i = 5 To 0 Step -1
     Range("A1") = i
     s = Timer + 1
     Do While Timer < s
        DoEvents
    Loop
Next
End Sub

 

[Johnegee]

Logit, I chose your version of the macro code, although I'm sure the others worked, because you treated me like the seven-year-old I seem to be. With only a little fiddling I made it work, so thank you, and thanks to everyone else who helped.

The only improvement I can think of is to make the countdown timer visible to the servers (and me). Once, a few weeks ago, I figured out how to get the timer to be visible as it counted down from 5 to 0, but I've slept since then and have forgotten how I did it. It must be simple or I couldn't have done it at all. Any ideas?

 

[JLGWhiz]

@Johnegee - check out this link.

VBA Timer

Excel VBA TIMER Function VBA Timer is an inbuilt function that gives us the fractional value of seconds. It is a very useful function used sometimes to

www.wallstreetmojo.com