Passing variables in VBA from a private sub to a module

nlillianm 20 Reputation points
2023-07-20T16:30:57.7133333+00:00

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, macors, 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
Microsoft 365
Microsoft 365
Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
5,772 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,175 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,275 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. HansV 966 Reputation points MVP
    2023-07-21T20:12:07.1733333+00:00

    The Workbook_Open procedure is called immediately when the workbook is opened. At that point, Ontimer_s does not have a value yet, so you must specify it in Workbook_Open:

    Private Sub Workbook_Open()
        Ontimer_s = Now + TimeSerial(0, 1, 0)
        Application.OnTime Ontimer_s, "SaveBook"
    End sub
    

    The SaveBook procedure should not have any arguments. And do you really want to use Save As each time? Why not simply save the workbook.

    Also, I wouldn't use 1 second as interval. It would make editing the workbook virtually impossible.

    Public Sub SaveBook()
        ThisWorkbook.Save
        Ontimer_s = Now() + TimeSerial(0, 1, 0)
        Application.OnTime Ontimer_s, "SaveBook"
    End Sub
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.