VBA Add-in When does ActiveWorkBook become active?

Question

Context: Excel 2013 VBA. When using an Add-in, the term "ActiveWorkBook" is supposed to refer to the document being edited, while "ThisWorkBook" refers to the add-in in the background. Consider the code

In the ThisWorkBook module of the Add-in

Private WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_WorkBookOpen(ByVal Wb As Workbook)

MsgBox Wb.Name & " " & Wb.Worksheets(1).Cells(1, 1)
If Wb.Worksheets(1).Cells(1, 1) = "AAA" Then
    MsgBox "Cell OK", 
    MsgBox ActiveWork.Name
End If
End Sub

The Add-in is enabled, and Excel started. So far so good. Now if I open a file "Book1" which contains "AAA" in cell(1,1) of Sheet1 I receive:

"Book1.xlsm AAA" (in the messagebox, as expected), then "Cell OK", as expected.
But then the error "Object Required" referring to the line MsgBox "ActiveWorkBook.Name" So at that point Book1 is not yet the ActiveWorkBook. When does it become so? Or how do I make it so? (Something like "Wb.Activate" before the MsgBox doesn't help)

This problem is showing up in a much more complex real-world situation, which moreover seems tied up with security issues somewhow. I'm trying to understand the behaviour with a simple example

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2015-05-12T15:56:58

You're not handling the case where no workbook is active. Workbook_Open is called before the opening workbook gets activated, so Application.ActiveWorkbook is very possibly Nothing when that code runs - anytime Excel starts actually.

The simple solution is to use the wb reference in Workbook_Open - ActiveWorkbook won't be set until after that event has completed. And if it's set, then it's not the workbook you think it is: it's the workbook that was active when wb started opening.

See for yourself (in addin's ThisWorkbook code-behind):

Private WithEvents app As Application

Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
    MsgBox "activated"
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "opened"
End Sub

Private Sub Workbook_Open()
    Set app = Excel.Application
End Sub

When the add-in starts, you'll see the "opened" message box (while the background still shows an empty workspace without any workbook) - and then the "activated" message box pops up once Excel actually has the worksheet on display.

VBA Add-in When does ActiveWorkBook become active?

1 Answers