VBA add-in When is ActiveWorkBook activated?

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

In the ThisWorkBook module, add-ins

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 is running. So far, so good. Now if I open the file "Book1" which contains "AAA" in cell (1,1) of Sheet1, I get:

"Book1.xlsm AAA" (in the message box as expected) followed by "Box OK" as expected.
But then the error "Required object" refers to the line MsgBox "ActiveWorkBook.Name" So, at that moment Book1 is not yet an ActiveWorkBook. When will it be like this? Or how to do it? (Something like "Wb.Activate" before MsgBox doesn't help)

This problem manifests itself in a much more complex situation in the real world, which nevertheless seems to be related to security issues. I am trying to understand the behavior with a simple example


source to share

1 answer

You are not handling the case where the workbook is not active. Workbook_Open

is called before the opening workbook is opened, so it's Application.ActiveWorkbook

very possible Nothing

when this code runs - anytime Excel actually starts up.

The simple solution is to use link wb

in Workbook_Open

- ActiveWorkbook

will not be installed until this event completes. And if it is installed, then this is not the workbook that you think so: this is the workbook that was active at startup wb


See for yourself (in addition to the ThisWorkbook


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 will see an "open" message box (while the background still shows an empty workspace without any workbook) - and after that an activated message box appears as soon as Excel actually has a worksheet on display.



All Articles