placing macro in sheet code vs module code - "Cannot run the macro" error

Special K

Board Regular
Joined
Jun 20, 2011
Messages
83
Office Version
  1. 2010
Platform
  1. Windows
I am studying the stop watch example shown on this page and trying to incorporate it into a spreadsheet:

https://trumpexcel.com/stopwatch-in-excel/

The demo has 3 key functions: one to start the stopwatch, one to stop it, and one to reset it (NOTE: color formatting code and other unnecessary details removed from original example):

Code:
Dim NextTick As Date
Dim t As Date
Dim PreviousTimerValue As Date


Sub StartTime()

    PreviousTimerValue = Sheets("timer_test").Range("O2").Value
    t = Time
    Call ExcelStopWatch

End Sub

Sub ExcelStopWatch()

    Sheets("timer_test").Range("O2").Value = Format(Time - t + PreviousTimerValue, "hh:mm:ss")
    NextTick = Now + TimeValue("00:00:01")
    Application.OnTime NextTick, "ExcelStopWatch"

End Sub

Sub StopClock()

    On Error Resume Next
    Application.OnTime earliesttime:=NextTick, procedure:="ExcelStopWatch", schedule:=False

End Sub

Sub Reset()

    On Error Resume Next
    Application.OnTime earliesttime:=NextTick, procedure:="ExcelStopWatch", schedule:=False
    Sheets("timer_test").Range("O2").Value = 0

End Sub

Cell O2 on sheet "timer_test" contains the stopwatch itself. The workbook file name is "time_trials - First Run.xlsm" (without the quotes).

My existing timer_test sheet already contains buttons and associated VBA code, so I decided to just copy all of the stopwatch-related code above into my timer_test VBA code document. When I tried to run it by calling the StartTime function from within an existing Private Sub, I received the following error:

Cannot run the macro "time_trials - First Run.xlsm'!ExcelStopWatch'. The macro may not be available in this workbook or all macros may be disabled.

If I move all of the above code into a new module named StopWatch, everything works fine. Is my error message due to a requirement that the code above be in a separate module rather than in the sheet code, or is there some kind of syntax/parsing error due to the fact that I have spaces in the filename? I see there is an unmatched " in the error message, so I'm wondering if I need to use some sort of escape or additional quotes if I want to include all of the above code in the sheet code rather than in a separate module.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Untested, but I think if you want to call a procedure that's in sheet code you need to qualify the sheet, and the procedure itself must not be Private. In your case, maybe try this:

Call Sheets("timer_test").StartTime
 
Upvote 0
If you want to have the code in the worksheet module instead of having it in a standard module, you will need to qualify the Ontime Procedure name as follows :
Code:
 procedure:=[SIZE=3][COLOR=#ff0000][B]Me.CodeName &[/B][/COLOR][/SIZE] "[SIZE=3][COLOR=#ff0000].[/COLOR][/SIZE]ExcelStopWatch"
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top