Passing variables in VBA from a private sub to a module

Question

I have a Private Sub Workbook_Open() where I call on a module

Application.OnTime Ontimer_s, "SaveBook"

I need to pass public variables (called for on the workbook object) like:

Option Explicit

Public Ontimer_s As Date

when the code gets to "savebook" module I have brought in variables like shown, but it does not seem to get them correctly. Is this the correct way to do this? I don't think I have a good understanding of subs, modules, macros, and objects, so that may be my main problem.

Public Sub SaveBook(ByVal SavePath As String, ByVal Ontimer_s As Date)   
Application.DisplayAlerts = False   
ThisWorkbook.SaveAs FileName:=SavePath & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled     
Ontimer_s = Now() + TimeValue("00:00:01")   
Application.OnTime Ontimer_s, "SaveBook"   End Sub

Overall I want to save the workbook every second by overwriting a file that I am making through the macro. Maybe there is a better way to do it?

You have to pass parameters to `SaveBook` in `OnTime`. Please ref to https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime — taller_ExcelHome, Jul 20 '23 at 19:16
If both `SavePath` and `Ontimer_s` are public variables then you can remove the parameters from `SaveBook` — Tim Williams, Jul 20 '23 at 19:50

score 0 · Answer 1 · answered Jul 21 '23 at 14:35

The Sub you define with Application.OnTime cannot have parameters. The solution to the problem is to declare variables in a module, their modification can be done anywhere in VBA and the call of another Sub or Function through the one you have defined in Application.OnTime, with these variables as parameters. I will write a simple example to make it understandable:

Option Explicit

Public Ontimer_s As Date

' the below public variables can take value anywhere in the programm
Public param1 as String, param2 as Long
Const TIMER_SUB = "SaveBook"

Public Sub start_timer()
   Ontimer_s = Now() + TimeValue("00:00:01")  
   Application.OnTime Ontimer_s, TIMER_SUB
End Sub

Public Sub stop_timer()
   If Ontimer_s = 0 Then Exit Sub
   Application.OnTime Ontimer_s, TIMER_SUB, , Schedule:=False
   Ontimer_s = 0
End Sub


public Sub SaveBook()
   ' HERE CALL THE HEPLER SUB/FUNCTION WITH THE PARAMETERS
   Call jobToDo(param1, param2) 
   Ontimer_s = Now() + TimeValue("00:00:01")   
   Application.OnTime Ontimer_s, TIMER_SUB
End Sub

' THIS IS CALLED ΒΥ SaveBook WITH THE VARIABLES AS PARAMETERS
Public Sub jobToDo(par1 as String, par2 as Long)
   ' here your commands to execute onTime....
   Debug.Print "executing by timer..."
End Sub

Passing variables in VBA from a private sub to a module

1 Answers1