Блог

Я пытаюсь добавить кнопку на свой рабочий лист во время выполнения. Эта кнопка должна просто отображать другой рабочий лист, который также создается во время выполнения. Я добавил кнопку, подобную этой:

 Dim btnShowTable
Set btnShowTable = ActiveSheet.Buttons.Add(rowRange.Left   10, rowRange.Top   10, rowRange.Width - 20, rowRange.Height - 20)
btnShowTable.Caption = "Show table data"
btnShowTable.OnAction = AddClickHandler_ShowSheet("ClickModule", "TableView", tableSheet)


Function AddClickHandler_ShowSheet(ByVal moduleName As String, ByVal btnName As String, ws As Worksheet)
  Dim methodName As String
  methodName = btnName amp; "_" amp; AddClickHandler_GetId() amp; "_Click"
  Dim LineNum As Long
  Dim VBCodeMod As CodeModule
  Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(moduleName).CodeModule
  With VBCodeMod
    LineNum = .CountOfLines   1
      .InsertLines LineNum, _
        "Sub " amp; methodName amp; "()" amp; Chr(13) amp; _
        "    " amp; ws.CodeName amp; ".Select" amp; Chr(13) amp; _
        "End Sub"
  End With
  AddClickHandler_ShowSheet = moduleName amp; "." amp; methodName
End Function
  

Функция, создающая кнопку, находится в одном модуле, а AddClickHandler_ShowSheet — в другом.

Идея в том, что у меня был бы отдельный модуль, который содержал бы все эти обработчики щелчков, чтобы я мог легко удалить их все. Это работает нормально. Обработчики созданы, и кнопки работают должным образом. Проблема, с которой я сталкиваюсь, заключается в том, что при вызове этого метода InsertLines все переменные моего модуля в модуле, который содержит функцию для создания кнопок, теряются. У меня есть 4 переменные модуля

 Dim xmldoc As New MSXML2.DOMDocument
Dim xmlDataMap() As DataNode
Dim xmlDataMapLast As Integer
Dim xmlTables As Collection
  

после вызова InsertLines все они стали пустыми, за исключением xmlDataMapLast, который содержит правильное значение 14.

Если я попытаюсь отладить этот метод, когда я перехожу к вызову InsertLines, я получаю сообщение об ошибке «В данный момент не удается перейти в режим прерывания». и я ничего не могу отладить, пока моя функция не завершится. Если я закомментирую вызов AddClickHandler_ShowSheet, мои переменные останутся нетронутыми, так что это должно быть что-то, связанное с этим вызовом.

Я пытаюсь достичь невозможного или просто делаю это неправильно?

Неудивительно, что модуль сбрасывается.
На самом деле, я бы ожидал, что типы значений также будут сброшены. Немного удивительно, что они выживают.

Зачем вам хранить переменные в модуле, который вы используете для генерации кода?
Сохраните их в отдельном модуле и используйте этот модуль только для генерации кода.

С учетом сказанного, зачем вам вообще динамически добавлять код?
OnAction поддерживает параметры:

 Sub asdff()
  Worksheets(1).Buttons(1).OnAction = "'Module1.ParametrizedHandler 5, ""Hi there""'"
End Sub

Public Sub ParametrizedHandler(ByVal foo As Long, ByVal bar As String)
  MsgBox foo, vbInformation, bar
End Sub
  

Обратите внимание на отсутствие круглых скобок в строке вызова и одинарных кавычек вокруг нее.