Is it possible to pass arguments to a macro using "OnTime"?

[Glory]

Eg:

Sub Test()
 
b7 = ThisWorkbook.Path & ThisWorkbook.Name
 
Application.OnTime Now + TimeValue("00:00:02"), "Reopen(" & b7 & ")"
ThisWorkbook.Close SaveChanges:=True
 
End Sub
 
Public Function Reopen(Path, Workbook)
'==================================================
'Update 01: Reopens workbook after creation of new sheet
'Variable   Purpose
'i1         Workbook and Path
'==================================================
 
i1 = Path & Workbook
 
Workbooks.Open i1
 
End Function

This fails because the application looks for a macro named literally "Reopen(Workbook.Path & Workbook.Name)".

Is there a way to pass arguments to a macro using this method?

 

[GTO]

Hi there,

Firstly, it appears to me that you are only supplying one arg (b7) and there's two req'd in your function.

In the function, am I correct that Workbook is not an object, but a string representing the workbook's name?

 

[Glory]

Yeah that's right. I found some stuff on MrExcel that explains it only when I run this function something weird happens.

The workbook successfully opens, and then an error triggers (which DisplayAlerts = False cannot suppress) that looks like this:

Subs as follows:

Sub TEST()
b7 = ThisWorkbook.Path & "\" & ThisWorkbook.Name
Application.OnTime Now + TimeValue("00:00:02"), "'Reopen" & b7 & " '"
ThisWorkbook.Close SaveChanges:=True
End Sub
Public Sub Reopen(WorkbookPath)
'==================================================
'Update 01: Reopens workbook after creation of new sheet
'==================================================
'Application.DisplayAlerts = False
Workbooks.Open WorkbookPath
'Application.Wait Now + TimeValue("00:00:03")
'Application.DisplayAlerts = True
End Sub

 

[GTO]

Definitely not tested, but it looks to me as if you are setting an OnTime, then closing the wb?

 

[Glory]

That's the general idea.

The reason is because of an OLE-object induced mouse-over crash that occurs consistently when I create new copies of a worksheet with buttons.

So I wanted to try closing and reopening the workbook, since doing that manually solves the problem. Is there a better way?

Edit: I realize I could just include the path in the macro. I asked this question specifically to learn how to pass arguments using "OnTime". But if you know a better way to solve my original problem, I'm all ears.

 

[GTO]

Can you show enough of an example that we could replicate the mousemove error?

 

[Glory]

I'd rather not post the entire code. There's separate functions that it calls and all kinds of things I would want to excise.

I know the root of the problem but haven't found a good way around causing it (enabling and disabling trust access). It might be possible to reproduce the issue just by adding a button and inserting code, using that function to enable trust access. I'll post an example in a minute if the example causes the issue.

For now, I just know closing and reopening solves the problem. Is there another good way to close and reopen a workbook using a macro within the same workbook?

 

[Glory]

This example successfully reproduces the issue; if "Trust Access" is disabled, this will crash Excel.

Sub test2()
 
'Toggle VBA "Trust Access" settings if trust access is disabled
 
If Not TrustVBA Then
    Call SendKeys("%TMST%V{ENTER}")
        Do
            DoEvents
        Loop While Not TrustVBA
    b2 = True
End If
 
'Create button
 
ThisWorkbook.Sheets.Add After:=Sheets("Sheet3")
ThisWorkbook.Sheets("Sheet4").OLEObjects.Add _
                    ClassType:="Forms.CommandButton.1", _
                    Height:=32.25, _
                    Left:=290.25, _
                    Top:=72.75, _
                    Width:=102.75
 
'Text to insert
 
b6 = "Private Sub CommandButton1_Click" & Chr(10) & Chr(10) & _
     "MsgBox ""Hey""" & Chr(10) & Chr(10) & _
     "End Sub"
 
 
'Insert procedure
 
ThisWorkbook.VBProject.vbcomponents(Sheets("Sheet4").CodeName).CodeModule.insertlines 1, b6
 
 
'Disable VBA "Trust Access" if it had to be enabled toward the start of the function
 
If b2 Then
    Call SendKeys("%TMST%V{ENTER}")
        Do
            DoEvents
        Loop While Not TrustVBA
End If
End Sub
 
 
 
Public Function TrustVBA() As Boolean
 
'==================================================
'Checks VBA "Trust Access" settings
 
'Returns "True" if Excel will currently "Trust Access" to the project
'Returns "False" if Excel will not currently "Trust Access" to the project
'Variable   Purpose
'h1         Line Label
'==================================================
 
On Error GoTo h1
 
    If ThisWorkbook.VBProject.vbcomponents.Count <> 0 Then TrustVBA = True
    Exit Function
 
h1:
 
    TrustVBA = False
 
End Function

 

[shg]

Is there another good way to close and reopen a workbook using a macro within the same workbook?

Maybe ...

Sub x()
    With ThisWorkbook
        Application.OnTime Now + #12:00:05 AM#, .FullName & "!HereIAm"
        .Close SaveChanges:=True
    End With
End Sub
 
Sub HereIAm()
    ThisWorkbook.Activate
    MsgBox "I'm back!"
End Sub

 

[GTO]

..For now, I just know closing and reopening solves the problem. Is there another good way to close and reopen a workbook using a macro within the same workbook?

A different take, as I'm used to running with security at medium; which brings up the dialog when OnTime trys to re-open the wb. A bit kludgy, but seems to work. If your system allows vbscript to run, then I would add Kill code to rid the temp script.

Option Explicit
    
Declare Function ShellExecute Lib "shell32.dll" _
                 Alias "ShellExecuteA" (ByVal hwnd As Long, _
                                        ByVal lpOperation As String, _
                                        ByVal lpFile As String, _
                                        ByVal lpParameters As String, _
                                        ByVal lpDirectory As String, _
                                        ByVal nShowCmd As Long _
                                        ) As Long
    
Sub exa3()
Dim FSO         As Object '<--- FileSystemObject
Dim fsoTStream  As Object '<--- TextStream
Dim FakeRet
    
    '// Set a reference to FSO and to a created textstream.                         //
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fsoTStream = FSO.CreateTextFile(ThisWorkbook.Path & "\temp.vbs")
    
    With fsoTStream
        '// Write the script and close the file                                     //
        
        '// A bit bleary eyed, but this should...                                   //
        '// 1) Create a reference to Excel (I beleive this can fall over if there   //
        '//    are multiple instances existing),                                    //
        '// 2) Loop until it finds the workbook closed, or times out.               //
        '// 3) Presuming no timeout, re-open the wb (The Open event might be a good //
        '//    place to check existance and delete the temp script).                //
        .WriteLine "Dim oExcel, wb, bolStillOpen, StartTime"
        
        .WriteLine "  Set oExcel = GetObject(,""Excel.Application"")"
        
        .WriteLine "  StartTime = Timer + 5"
        .WriteLine "  On Error Resume Next"
        .WriteLine "  Do"
        .WriteLine "    bolStillOpen = False"
        .WriteLine "    Set wb = oExcel.Workbooks(""" & ThisWorkbook.Name & """)"
        .WriteLine "    If err.Number <> 0 Then"
        .WriteLine "      err.Clear"
        .WriteLine "      bolStillOpen = False"
        .WriteLine "    Else"
        .WriteLine "      bolStillOpen = True"
        .WriteLine "    End If"
        .WriteLine "  Loop While bolStillOpen And Timer < StartTime"
        'Test
'        .WriteLine "  StartTime = Timer + 7"
'        .WriteLine "  Do While Timer < StartTime"
'        .WriteLine "  Loop"
        
        .WriteLine "  If Not bolStillOpen Then"
        .WriteLine "    oExcel.Workbooks.Open """ & ThisWorkbook.FullName & """"
        .WriteLine "  Else"
        .WriteLine "    MsgBox ""Took too long"""
        .WriteLine "  End If"
        
        .Close
    End With

This example successfully reproduces the issue; if "Trust Access" is disabled, this will crash Excel.

I ran your code once I declared the variables, w/o crashing. I would suggest changing your options so that Option Explicit is inserted, and declaring all variables.

You have b6 as a string and b2 as a Boolean. When seeing what is going "Kaboom!", or just trying to remember what one wrote a month ago, at least to me, it is easier to follow and thus debug, if variables are given meaningful names.

Anyways, with the same TrustVBA function, maybe:

Option Explicit
    
Sub Test3()
Dim wks         As Worksheet
Dim oleButton   As OLEObject
Dim ObjModule   As Object  ' CodeModule
Dim lLine       As Long
    
    '// SendKeys can be spotty; if fails, I'm not sure looping would help.          //
    If Not TrustVBA Then
        Application.SendKeys "%TMST%V{ENTER}"
        If Not TrustVBA Then
            MsgBox "Please contact ...", vbInformation, vbNullString
            Exit Sub
        End If
    End If
    
    With ThisWorkbook
        Set wks = .Worksheets.Add(After:=.Worksheets("Sheet3"), Type:=xlWorksheet)
        Set oleButton = wks.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                                           Link:=False, _
                                           DisplayAsIcon:=False, _
                                           Height:=32.25, _
                                           Left:=290.25, _
                                           Top:=72.75, _
                                           Width:=102.75)
        '// Name the bttn so that we can refer to it later.                         //
        oleButton.Name = "cmdMyButton"
        oleButton.Object.Caption = "My Button"
        Set ObjModule = .VBProject.VBComponents(wks.CodeName).CodeModule
        
        '// Create the event procedure, returning one line down, so we can insert   //
        '// code in the event.                                                      //
        lLine = ObjModule.CreateEventProc("Click", "cmdMyButton") + 1
        
        ObjModule.ReplaceLine lLine, _
            vbTab & "MsgBox ""Code added to "" & Me.Name, 0, vbNullString"
        
        '// I don't know if I'm missing something, but VBIDE pops up when I run it, //
        '// and I don't recall if this is expected.  Anyways, hide it.              //
        .VBProject.VBE.MainWindow.Visible = False
    End With
End Sub

Hope that helps,

Mark