Issues with OnTime method

[cswain99]

i am having issues with the Application.OnTime method. It works great when I open the workbook but gives my the "method 'OnTime' of object'_Application' failed" error when i try and close the workbook. Any ideas or fixes would be great

/c
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Sheet11.AutoSaveAs", , False <-------Error occurs here
End Sub

Private Sub Workbook_Open()
dTime = Time + TimeValue("00:01:00")
Application.OnTime dTime, "Sheet11.AutoSaveAs"
End Sub
/c

 

[GTO]

i am having issues with the Application.OnTime method. It works great when I open the workbook but gives my the "method 'OnTime' of object'_Application' failed" error when i try and close the workbook. Any ideas or fixes would be great

/c
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Sheet11.AutoSaveAs", , False <-------Error occurs here
End Sub

Private Sub Workbook_Open()
dTime = Time + TimeValue("00:01:00")
Application.OnTime dTime, "Sheet11.AutoSaveAs"
End Sub
/c

Where is dTime declared?

 

[cswain99]

It is located in my sheet module.

Public dTime As Date


Sub AutoSaveAs()
currentFileName = ThisWorkbook.Name
MsgBox ("It Works")
dTime = Time + TimeValue("00:01:00")
With Application
.OnTime dTime, "AutoSaveAs"
.EnableEvents = False
.DisplayAlerts = False
ThisWorkbook.SaveAs "N:\Energy Marketing\East RT 2003\Physical Flow Tracking Sheet\" & currentFileName
.EnableEvents = True
End With
End Sub

 

[GTO]

Then dTime is not being seen from ThisWorkbook. At least not the way you are referring to it. I'm running late, so don't take this as 'short', just direct.

In a new wb, in ThisWorkbook:
<font face=Courier New><SPAN style="color:#007F00">'Option Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> one()<br>    dTime = #9/27/2011#<br>    MsgBox dTime<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> two()<br>    MsgBox dTime<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

In Sheet1's module:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Public</SPAN> dTime <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br></FONT>

Note that dTime is not retained for two().

Now, uncomment the Option Explicit at the top of ThisWorkbook. When you attempt to run either procedure, Excel will tell you the variable is not declared.

You are making things a lot tougher on yourself then necessary. Always use Option Explicit. When using a Public variable, declare it in a Standard Module. Move AutoSaveAs() to a Standard Module.

Whilst you can use a public variable in a sheet module (and call a sub therein prefacing the procedure name with the codename as you are), storing both these in Standard Module(s) is just a lot easier. Option Explicit would have saved you the mystery, as Excel would have warned of the visibility issue.