Macro does not run when opening a File/Workbook from within Excel

macrosmicrosoft excelvba

I'm new to Macros and VBA in general but my goal is pretty simple I believe. My macro should search an opened Excel document (automatically) and create a MessageBox with a warning if the WEEKNUM() function is used with only one parameter (the date).
So I've created my Macro in excel 2016/Visual Basic editor and have saved it as .xlam (a Excel Add-In).
Then I have copied the file to C:\Users\User\AppData\Roaming\Microsoft\AddIns because that's where the Add-Ins are located I guess. I've made sure that this location is a trusted one in the Excel Trust Center settings + I've enabled all macros in the settings.

So my macro looks like this:
Excel Objects – ThisWorkBook:

Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue("00:00:02"), "Start"
  MsgBox ("Hello World!")
End Sub

I'm using the Workbook_Open() event to start my macro.
Then my Module named kwTester which tests the document:

Private Sub Start()
'Sheet is the current sheet
Dim Sheet As Worksheet
'my Regex
Dim regEx As New RegExp
'the search pattern
Dim strPattern As String: strPattern = "^\=WEEKNUM\(\w\d+\)$"
'is used to tell my if there is just one parameter
Dim verwendetKalenderwoche As Boolean
verwendetKalenderwoche = False
'CellAddress and CellFormula are just for debugging purposes
Dim CellAddress As Variant
Dim CellFormula As Variant
'Setting up the RegEx
If strPattern <> "" Then
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPattern
    End With
End If
'Code should be repeated for every opened Workbook and Sheet
'Dim book As Workbook
For Each book In Workbooks
For Each Sheet In book.Worksheets
    With Sheet.UsedRange
        For Each cell In Sheet.UsedRange
            On Error Resume Next
            CellFormula = cell.Formula
            On Error Resume Next
            If cell.Formula <> "" And regEx.Test(cell.Formula) Then
                verwendetKalenderwoche = True
                CellAddress = CellAddress & vbNewLine & Cells(cell.Row, cell.Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
            End If
        Next
    End With
Next
Next
'if there is a WEEKNUM function with just one parameter then display msgBox and UserForm
If verwendetKalenderwoche = True Then
    MsgBox "Sie verwenden die Funktion KALENDERWOCHE mit nur einem Parameter. Ab Office 2007 wird hier die amerikanische Rechenweise zur Bestimmung der Kalenderwoche verwendet." & vbNewLine & "Diese ist nicht mit der geltenden ISO-Regelung in Deutschland kompatibel." & vbNewLine & "Biite verwenden Sie wenn möglich den zusätzlichen Parameter <21> um diesen Fehler zu beheben." & vbNewLine & "Beispiel:" & vbNewLine & "KALENDERWOCHE(A2;21)", vbExclamation, "KALENDERWOCHE-Warnung"
    'UserForm anzeigen um die betroffenen Zellen darzustellen
    UserForm1.TextBox1.MultiLine = True
    UserForm1.TextBox1.Text = CellAddress
    UserForm1.Caption = "Betroffene Zellen"
    UserForm1.TextBox1.ScrollBars = fmScrollBarsVertical
    UserForm1.Show vbModeless
End If
End Sub

The UserForm should not be that important so I'll not include it, it just displays the cells which contain the WEEKNUM function.

If I open up a excel document which uses the WEEKNUM function everything is working properly – My MessageBox is displayed, I receive my UserForm and this tells my which cells are using the WEEKNUM Function. Exactly how I want it to be, so I guess my code works in some way. 🙂

I've used the following sites to write the code:
Automatically Run Excel Macros Upon Opening Excel
Run a macro

Description of my Problem

Excel is closed – I open a file by double clicking on it – my macro is executed successfully and displays my results. But if minimize this Excel-instance and open up another file my macro is not being executed again. Same thing if I open a document from within Excel via File-Open dialogue.
What is wrong?
Is this the way the Workbook_Open Event is supposed to work or am I missing something?
Is my add-in located at the correct place? Do I have to check some other settings?

I hope my question is not considered off-topic. I was not sure where to ask – Stackoverflow or Superuser. Notify me if I should delete this question 🙂

Any input is very welcome.

Best Answer

You are attempting to work with the Workbook events, which are local to the object to which they belong (in this case, the add-in). What you need is an Application event. Chip Pearson has a page explaining the concept.

To summarize, put this code inside the ThisWorkbook object of your add-in.

Option Explicit
Private WithEvents App As Application

Private Sub Workbook_Open()
     Set App = Application
 End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "Hello world"
End Sub
Related Question