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

[Special K]

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):

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.

 

[JoeMo]

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

 

[Jaafar Tribak]

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 :

 procedure:=[SIZE=3][COLOR=#ff0000][B]Me.CodeName &[/B][/COLOR][/SIZE] "[SIZE=3][COLOR=#ff0000].[/COLOR][/SIZE]ExcelStopWatch"