Макрос не запускается при открытии файла / книги из Excel

Question

Я новичок в Macros и VBA в целом, но моя цель довольно проста, я считаю. Мой макрос должен искать в открытом документе Excel (автоматически) и создавать MessageBox с предупреждением, если функция WEEKNUM () используется только с одним параметром (датой).
Поэтому я создал свой макрос в редакторе Excel 2016/Visual Basic и сохранил его как .xlam (надстройка Excel).
Затем я скопировал файл в C:\Users\User\AppData\Roaming\Microsoft\AddIns потому что именно там находятся надстройки. Я убедился, что это расположение является надежным в настройках Excel Trust Center + Я включил все макросы в настройках.

Итак, мой макрос выглядит так:
Объекты Excel - ThisWorkBook:

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

Я использую событие Workbook_Open() для запуска моего макроса.
Затем мой модуль с именем kwTester, который тестирует документ:

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

UserForm не должен быть таким важным, поэтому я не буду его включать, он просто отображает ячейки, которые содержат функцию WEEKNUM.

Если я открываю документ Excel, который использует функцию WEEKNUM, все работает правильно - отображается мой MessageBox, я получаю свою пользовательскую форму, и она сообщает, какие ячейки используют функцию WEEKNUM. Именно так, как я хочу, поэтому я думаю, что мой код работает каким-то образом. :)

Я использовал следующие сайты для написания кода:
Автоматически запускать макросы Excel при открытии Excel
Запустить макрос

Описание моей проблемы

Excel закрывается - я открываю файл двойным щелчком по нему - мой макрос успешно выполнен и отображает мои результаты. Но если свернуть этот экземпляр Excel и открыть другой файл, мой макрос больше не выполняется. То же самое, если я открываю документ из Excel через диалог открытия файла.
Что случилось?
Это то, как событие Workbook_Open должно работать или я что-то упустил?
Моя надстройка расположена в правильном месте? Должен ли я проверить некоторые другие настройки?

Я надеюсь, что мой вопрос не считается не по теме. Я не был уверен, где спросить - Stackoverflow или Superuser. Сообщите мне, если я должен удалить этот вопрос :)

Любой вклад очень приветствуется.

Answer 1

Вы пытаетесь работать с событиями Workbook , которые являются локальными для объекта, которому они принадлежат (в данном случае, надстройка). Что вам нужно, это событие Application . У Чипа Пирсона есть страница, объясняющая концепцию.

Подводя итог, поместите этот код в объект ThisWorkbook вашей надстройки.

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

Answer 2

Я исправил эту проблему в моем Excel. Макрос не будет работать при открытии, если какой-либо лист в книгах был сохранен в предварительном просмотре страницы! Как только я снова переключился на нормальный вид, все снова заработало. Удачи!