动态添加VBA代码后Public变量丢失问题求助

嘿，这个问题我太熟了！当你用VBIDE动态往工作表里加代码时，Excel会偷偷触发VBA项目的重新编译/重新加载，所有模块级的Public变量都会被清零——不管你把变量放在标准模块、ThisWorkbook还是工作表里，都逃不过这个重置。既然不能用单元格存值，给你两个靠谱的解决方案：

方案1：用工作簿名称集合（Names）存储值（最推荐） #

这个方法直接利用Excel内置的Names对象来存数值，属于工作簿级别的存储，完全不依赖单元格，而且不会因为VBA项目编译而丢失值，甚至保存关闭工作簿后再打开，值还能保留。

代码示例：

' 替代原来的Public变量teller，用Names存储累加值
Sub Countteller()
    Dim currentVal As Long
    ' 先检查名称是否存在，不存在就初始化
    On Error Resume Next
    currentVal = Evaluate(ThisWorkbook.Names("teller").RefersTo)
    If Err.Number <> 0 Then
        currentVal = 0
        ' 创建一个隐藏的名称，避免在名称管理器里显示
        ThisWorkbook.Names.Add Name:="teller", RefersTo:="=0", Visible:=False
    End If
    On Error GoTo 0
    
    ' 执行累加逻辑
    currentVal = currentVal + 1
    ' 更新名称存储的值
    ThisWorkbook.Names("teller").RefersTo = "=" & currentVal
    
    MsgBox "当前teller值：" & currentVal
End Sub

' 你的动态添加事件代码的过程（示例）
Sub AddCode()
    Dim vbComp As VBIDE.VBComponent
    Dim codeMod As VBIDE.CodeModule
    Dim lineNum As Long
    
    Set vbComp = ThisWorkbook.VBProject.VBComponents("Sheet1")
    Set codeMod = vbComp.CodeModule
    
    ' 先检查事件代码是否已存在，避免重复添加
    lineNum = codeMod.Find("Private Sub Worksheet_BeforeRightClick", 1, 1, -1, -1)
    If lineNum = 0 Then
        lineNum = codeMod.CountOfLines + 1
        codeMod.InsertLines lineNum, "Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)"
        codeMod.InsertLines lineNum + 1, "    Cancel = True" ' 这里替换成你的实际业务逻辑
        codeMod.InsertLines lineNum + 2, "    MsgBox ""右键点击触发事件"""
        codeMod.InsertLines lineNum + 3, "End Sub"
    End If
End Sub

方案2：用Application级别的自定义属性（仅当前会话有效） #

如果不需要把值保存到工作簿，只是在当前Excel打开的会话里保留，可以用Application的自定义属性来存值，重启Excel后值会丢失，但不会受VBA编译影响：

Sub Countteller()
    Dim currentVal As Long
    ' 检查属性是否存在
    On Error Resume Next
    currentVal = Application.GetCustomListContents(100) ' 用一个未被占用的自定义列表索引
    If Err.Number <> 0 Then
        currentVal = 0
        Application.AddCustomList Array(currentVal)
    End If
    On Error GoTo 0
    
    currentVal = currentVal + 1
    ' 更新自定义列表的值
    Application.DeleteCustomList 100
    Application.AddCustomList Array(currentVal)
    
    MsgBox "当前teller值：" & currentVal
End Sub

不过这个方案需要注意自定义列表索引的占用问题，不如方案1稳妥，所以优先推荐方案1。

另外要提醒你：使用VBIDE对象模型需要在Excel信任中心开启「信任对VBA项目对象模型的访问」，你既然能动态添加代码，应该已经设置好了，但如果遇到权限问题可以检查这个设置。

内容的提问来源于stack exchange，提问作者Merocky