How come excel vba code not working on open?

Question

I have code that is supposed to run on open, but I don't understand why it's not working. If I step through the code I will work just fine. And I can run it manually just fine, but the code never works on open.

I have the code in ThisWorkbook, and I've searched for other threads but I can't find a fix. Any idea what's going on?

 Private Sub RefreshAndClose_Open()

    If DateDiff("h", "12", Hour(Now)) <= 0 And DateDiff("n", "50", Minute(Now)) <= 50 Then
        ThisWorkbook.RefreshAll
        DoEvents
        ThisWorkbook.Save
        DoEvents
        Application.Quit
    End If

End Sub

Answer 1

Imagine you're Excel.

You have a Workbook opened, and a Worksheet is active. Now the user clicks somewhere and the Selection changes. Lots of low-level boilerplate non-VBA code needs to run for this to happen, but at one point or another, this happens (pseudo-code) somewhere in the Worksheet object's guts:

RaiseEvent SelectionChange(ActiveCell)

This raises the Worksheet.SelectionChange event:

When this event is raised, if there's a handler for this event, that's when it will be invoked. This handler needs to be wired up - you can do that yourself using a WithEvents object variable, but Excel's document modules make it very easy to wire up a handler - simply go to the Worksheet module you want to handle worksheet events for, select Worksheet from the code pane's left-side dropdown...

...and the VBE creates the handler procedure for you - note the contents of the two dropdowns:

The handler procedure is named in a very specific way:

LeftSideDropdownValue_RightSideDropdownValue

The "left-side dropdown value" is the interface you want to implement a member for; the "right-side dropdown value" is the specific member you're implementing.

If you placed the caret inside your procedure, the left-side dropdown would say (General) - that means it's just another procedure, and if nothing is invoking it then it's just another unused procedure that might as well not exist.

So, you're the Excel application, and you're opening a workbook. The exact same thing happens: lots of low-level boilerplate non-VBA code needs to run as a bunch of objects are being deserialized from the .xlsm macro-enabled workbook file loaded from disk, and when everything is good to go this happens (pseudo-code) somewhere in the opened Workbook object's guts:

RaiseEvent Open

This raises the Workbook.Open event:

Once again, we could declare a WithEvents object variable and wire-up a handler ourselves, but a VBA project hosted in Excel will always have a Workbook document module that already does this for us - so in order to handle the Workbook.Open event, we can go to the ThisWorkbook module, select Workbook from the left-side dropdown, and have the VBE automatically generate a well-formed handler for us:

Private Sub Workbook_Open()

End Sub

Again, the reason VBA knows this procedure is handling the Workbook.Open event, is because the name says so:

[Interface]_[Member]

If we go and change the name to anything else, we break that link, and we know the link is broken because the left-side code pane dropdown will be saying (General) and not Workbook.

This special naming scheme is also the reason why you should avoid using underscores in your own procedure names: they make procedures look like event handlers, but they're not. In certain specific advanced scenarios, using underscores in member names can even cause compile errors. Best take the good habit early on, of sticking to simple PascalCase for procedure names.

If we go and change the list of parameters in any way that makes it incompatible with the event's declaration, we get a compile error:

You can safely tweak the parameter names (although, I can't think of any good reason to do this), but the parameter data types and their ordering cannot be modified.

See everything you ever wanted to know about events for more info (I wrote that article).