VERY tricky timer question

[Ryan Ragno]

Hi everyone,

I've spent a VERY long time sleuthing the web trying to find a count down
timer system (preferably that prints in the status bar of the workbook, which
i already have a working counting timer i made that does this using the
OnTime method), that allows the user to continue
changing/copying/pasting/etc. whilst it counts down to zero from a predefined
value. Although my own code uses it, you can not use the OnTime method for
this since the timer stops counting down while a user types something into a
cell for example and takes more than a second to do so:

I have found a link of someone that has cracked this problem, using an add-in:
http://www.tushar-mehta.com/excel/software/clocks/countdown.html

can this be accomplished without an addin? is it possible to view the source
code of an add-in?

Thankyou for all of your help (in advance)

 

[Peter T]

The "tricky" part was deciphering your question, but have a go with this

Option Explicit
Dim mdtStop As Date
Dim mdtInterval As Date
Dim mdtNext As Date

Sub StartTimer()

mdtStop = Now + TimeSerial(0, 0, 30)
mdtNext = Now
mdtInterval = TimeSerial(0, 0, 3)

Application.OnTime mdtStop, "CleanUp"
NextOnTime

End Sub

Sub NextOnTime()
Dim bStop As Boolean

bStop = mdtNext > mdtStop
Application.OnTime mdtNext, "Update", bStop

If bStop Then Debug.Print Now
End Sub

Sub Update()
Dim mdtRemaining As Date
Application.StatusBar = CStr(CDate(Now - mdtStop))

mdtNext = Now + mdtInterval

NextOnTime
End Sub

Sub CleanUp()
mdtNext = 0
mdtStop = 0
Application.StatusBar = False
End Sub

Re your question about viewing the source code of an addin: if the project
is locked for viewing with a password the author does not wish you to see
the code and you should respect that.

Regards,
Peter T

 

[Bob Phillips]

I have some code at http://xldynamic.com/source/xld.XtraTime.html that you
could adapt.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

Ryan,

Unfortunately, there is no good way to create a countdown timer that
displays in a worksheet cell. Using only native Excel/VBA, OnTime is the
only way to go. As you have seen, OnTime events are either postponed or
never executed (depending on the LatestTime parameter) if the user is doing
anything within a cell. If you expand your horizons to include the Windows
API, you can use native Windows timers, which have a millisecond resolution
in contrast with OnTime's one second resolution. Be warned, though, that if
the TimerProc callback function called by a Windows timer attempts to modify
any cell while the user is in Edit Mode, you will likely crash Excel and
loose any unsaved work. Both the OnTime method and Windows Timer API
functions are described at www.cpearson.com/Excel/OnTime.aspx.

is it possible to view the source code of an add-in?

If the add-in is an XLA add-in, it is protected only by VBA's *very* weak
protection. Google on "vba password" and you'll likely find ways and
freeware/shareware that can break a VBA password. If the add-in is a COM
add-in, no source code exists within the add-in.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)