Excel VBA Help: Timer problem

[mellen]

I'm trying to develop a simple timer that counts down from 30 sec to 0 then resets back to 30 sec and I must have this macro run automatically when the workbook is open. The excel file is called Book1.xls and the page I want to place my macro is called "Sheet1" (not the real names but my test file uses default names).

I'm not a VB Programmer by any means and need some help completing this task. Below is my code and i've indicated where I get the error:

Function timer()
Application.Wait Now + TimeValue("00:00:30&quot
End Function

Sub Book1_Open() '<=== ERROR:Expected Sub,Function or Property. I thought I specfied by subroutine?
' While (1) 'Will uncomment later
timer
' endwhile
End Sub

 

[jcrater]

The code runs fine from the IDE, but you should move the Book1_Open procedure to the built-in Workbook_Open event. Look under Microsoft Excel Objects in the project explorer, then double-click the object labeled 'ThisWorkbook'. Then select the 'workbook' object from the left-side dropdown list. Then select the 'open' event from the right-side dropdown list and insert your code to call the timer.

 

[RobBroekhuis]

Even so, I don't think it's wise to set up an infinite loop in the workbook_open event. Using event-driven timer functions (with application.ontime) is probably the way to go, although we'd need to learn more about the intended application to be sure.
Rob