模块变量无法幸免于CodeModule.InsertLines调用 [英] Module variables don't survive CodeModule.InsertLines call

问题描述

我正在尝试在运行时向我的工作表添加一个按钮.此按钮应仅显示在运行时也在其上创建的不同工作表.我添加了这样的按钮:

I am trying to add a button to my worksheet during run-time. This button should just display different worksheet that is also created on during run-time. I added button like this:

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 & "_" & AddClickHandler_GetId() & "_Click"
  Dim LineNum As Long
  Dim VBCodeMod As CodeModule
  Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(moduleName).CodeModule
  With VBCodeMod
    LineNum = .CountOfLines + 1
      .InsertLines LineNum, _
        "Sub " & methodName & "()" & Chr(13) & _
        "    " & ws.CodeName & ".Select" & Chr(13) & _
        "End Sub"
  End With
  AddClickHandler_ShowSheet = moduleName & "." & methodName
End Function

创建按钮的功能位于一个模块中，而AddClickHandler_ShowSheet位于另一个模块中.

Function that creates the button is in one module while AddClickHandler_ShowSheet is in another.

我的想法是，我将有一个单独的模块，其中包含所有这些单击处理程序，以便可以轻松删除所有这些处理程序.这样就可以了.将创建处理程序，并且按钮将按预期工作.我的问题是，当调用此InsertLines方法时，包含按钮创建功能的模块中的所有模块变量都丢失了.我有4个模块变量

The idea is that I would have separate module that would contain all these click handlers so that I could easily delete all of them. This works ok. The handlers are created and buttons work as expected. The issue that I have is that, when this InsertLines method is called, all of my module variables in a module that contains the function for button creation are lost. I have 4 module variables

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

在调用InsertLines之后，除了xmlDataMapLast包含正确值14之外，所有其他字符都变为空.

after a call to InsertLines all of them became empty except for xmlDataMapLast which contains correct value of 14.

如果我在跳过InserLines调用时尝试调试此方法，则会收到错误消息此时无法进入中断模式".在功能结束之前，我无法调试任何东西.如果我注释掉对AddClickHandler_ShowSheet的调用，我的变量将保持不变，因此它必须与该调用有关.

If I try to debug this method when I step over InserLines call I get an error "Can't enter break mode at this time." and I can't debug anything until my function ends. If I comment out the call to AddClickHandler_ShowSheet my variables remain intact, so it must be something related to that call.

我是在尝试实现不可能的目标，还是只是以错误的方式来做?

Am I trying to achieve the impossible or am I just doing it the wrong way?

推荐答案

重置模块也就不足为奇了.
实际上，我希望值类型也会重置.有点意外他们生存了.

It's no surprise the module is reset.
In fact, I would expect value types to reset, too. It's a bit of surprise they survive.

为什么需要在用于代码生成的模块中存储变量?
将它们存储在单独的模块中，并且仅将此模块用于代码生成.

Why would you need to store variables in the module you use for code generation?
Store them in a separate module and only use this module for code generation.

话虽如此，为什么首先要动态添加代码?
OnAction 支持参数:

Having that said, why would you dynamically add code in the first place?
OnAction supports parameters:

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

请注意，调用字符串中没有括号，并在其两端加上了单引号.

Note the absence of parentheses in the call string, and the single quotes around it.

这篇关于模块变量无法幸免于CodeModule.InsertLines调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！