How to create a timer (VBA)

Question

Does someone know a trick to create a timer that is called say once a second or so? 

With the Windows versions of Office this was easily accomplished using the Windows API, but I couldn't find a way with Word for Mac (2011).

Answer 1

Word has an OnTime facility to program macro execution. The shortest interval is 1 second. Here's an example:

'----------------------------------------------

Sub BackgroundStart()

Application.OnTime When:=Now + TimeValue("00:00:05"), Name:="BackgroundRun"

Message$ = "Background process has been scheduled"

End Sub

Sub BackgroundRun()

Message$ = "Background process is running"

Application.OnTime When:=Now + TimeValue("00:00:05"), Name:="BackgroundRun"

End Sub

Sub BackgroundStop()

Application.OnTime When:=Now, Name:="BackgroundDummy"

Message$ = "Background process has been stopped"

End Sub

Sub BackgroundDummy()

'Nothing here

End Sub

'----------------------------------------------

"Start" will just schedule macro execution (the "Run" sub) and would be, for instance, in a toolbar button.

"Run" contains the bulk of your code and schedules itself. You can go as short as 1 second.

"Stop" interrupts the scheduling by programming another macro that does nothing, and it does it right away (Now). This could also be in a toolbar button.

"Dummy" is blank, but required, as the only way to stop a scheduled macro is by scheduling another. Only one macro at a time can be scheduled (a macro pending execution).

Answer 2

OK, tried it again this morning and it now works. Strange - I did have it in a module in Normal before, as I think some visibility or security issue prevents it from working in a .docm. Anyway, I hope that means thomas can toss away all the stuff I wrote.

Answer 3

> Anyway, I have the feeling this will be going to produce some overhead. Do you have any idea about this?

Performance overhead: probably not a lot.

Complexity overhead : you have one more thing to write, test, install, maintain, and coordinate with your other components. Never good. In this case the stay-open applet is visible in the Dock - I expect there is a way to make it disappear but some way to achieve the same thing without that would be preferable.

Another way to do this would probably be to use either the command line "at" or launchd/launchctl to schedule a task every second. The problem I see with that is that is how it would do its work. If it could do it using some small, low-overhead Unix command, then it's probably viable. But right now, the only way I know to do it would be to call Applescript via the osascript command, and that could be quite a high overhead operation to do once a second, even if all it has to do is...

> when there is no document open

...check how many non-template documents are open (or perhaps how many visible documents there are). That might be as simple as

tell application "Microsoft Word" to return the count of documents

Answer 4

Peter, thank you very much for this very detailed and fast answer!

I'm not (yet) familiar with AppleScript, but will try this definitely. Anyway, I have the feeling this will be going to produce some overhead. Do you have any idea about this?

What I need it for is the following:

Here I described a problem I have because MS discontinued support of the DocumentChanged event. In this special case I need to hide my toolbars when there is no document open and when Word is hidden. Otherwise the bars stay on the screen without a corresponding window or document which in fact is very confusing for the users and they for sure will be going to complain ;-( .

Answer 5

Can you summarise what you need your timer to do?

The timer() function exists on Mac _ I believe on older versions of Office it "ticked" once per second, but in Office 2011 that seems to be relaxed. Or maybe it's a change in OSX.

Here, I can't make OnTime work, so if you need to schedule a routine to be run, either someone needs to tell you how to make that work, or another approach is needed.

Here, I was able to create and save a stay-open applescript applet that will call a named module at a specified interval, but I wouldn't attempt to use it for real without a lot more understanding and testing. But it is at least one way.

The script:

-- the module we need to run

property module : ""

-- when really idle, only wake up every hour

property really_idle_interval : 3600

-- set this to the smallest interval that can be used

-- (don't know what that actually is)

property smallest_interval : 0.01

-- the current interval. Don't set to 0

property interval : 0

-- set to enable quit

property allow_quit : true

on run

set interval to smallest_interval

end run

on starttimer(theModule, theInterval)

set allow_quit to false

set module to theModule

if interval < smallest_interval then

set interval to smallest_interval

else

set interval to theInterval

end if

end starttimer

to stoptimer()

set module to ""

set interval to really_idle_interval

set allow_quit to true

end stoptimer

on idle

if module is not "" then

tell application "Microsoft Word"

run VB macro macro name module

end tell

end if

return interval

end idle

to reallyquit()

set allow_quit to true

quit

end reallyquit

-- don't let the user quit from the menu

on quit

if allow_quit then

continue quit

end if

end quit

---

--end of the Applescript.

You need to save the Applescript as an "Application" with the stay-open after Run handler checked. I called mine "vbatimer" and put it in my Documents folder.

I then put the following test VBA macros in a module called Module1 in Normal and ran "dotimerstuff"

Const appFullName As String = "Macintosh HD:Users:username:Documents:vbatimer.app"

Dim i As Integer

Sub appendtime()

i = i - 1

Debug.Print i

ActiveDocument.Content.InsertParagraphAfter

ActiveDocument.Content.InsertAfter Time()

End Sub

Sub appendtime()

i = i - 1

Debug.Print i

ActiveDocument.Content.InsertParagraphAfter

ActiveDocument.Content.InsertAfter Time()

End Sub

Sub dotimerstuff()

 i = 10

Debug.Print MacScript("tell application """ & appFullName & """ to run")

Debug.Print MacScript("tell application """ & appFullName & """ to starttimer(""Normal.Module1.appendtime"",1)")

While i > 0

DoEvents

Wend

Debug.Print MacScript("tell application """ & appFullName & """ to stoptimer()")

i = 10

Debug.Print MacScript("tell application """ & appFullName & """ to starttimer(""Normal.Module1.appendtime"",0.2)")

While i > 0

DoEvents

Wend

Debug.Print MacScript("tell application """ & appFullName & """ to stoptimer()")

Debug.Print MacScript("tell application """ & appFullName & """ to reallyquit()")

End Sub