excel - 如何通过激活另一个工作簿中的宏来更改工作簿的全局变量

Question

我有两个 Excel 文件，一个 patcher.xlsm 和一个 file.xlsm。 file.xlsm 上有一个按钮，当单击它时，它会在 Sheet 1 上执行一个过程，并显示一个消息框:神奇数字是:5。

现在，我有一个 patcher.xlsm，它将重新导入修补后的 Sheet 1 和带有全局变量的标准模块，并在 file.xlsm 上运行宏> 将此数字设置为 7。预期结果是，用户单击 file.xlsm 现在会看到神奇数字是:7。

由于某种原因，每当我执行patcher.xlsm时，魔数(Magic Number)总是重置为0，就好像内存已被清除或项目重置一样。

有没有办法从另一个工作簿分配全局变量？

过去，我看过几个 stackoverflow 线程，例如，更改变量的范围(私有(private)、公共(public)、全局等)、激活工作簿、查看 Excel 的隐藏 namespace 。

Sub Proc()
  MsgBox "The magic number is: 5"
End Sub

file.xlsm 中的 Sheet1

Call CopyModule("patcher_b_progress_bar.xlsm", "file_b.xlsm", "Sheet1", True)
Call CopyModule("patcher_b_progress_bar.xlsm", "file_b.xlsm", "GlobalModule", True)
Application.Run ("'file_b.xlsm'!GlobalModule.setDefaultMagicNumber")

patcher.xlsm “修补”工作表 1 并通过模块设置魔数(Magic Number)

CopyModule 
' export "patched GlobalModule" from patcher.xlsm as temp.bas
' remove GlobalModule from file.xlsm
' re-import "patched GlobalModule" to file.xlsm
(too lengthy to post here, referenced from http://www.cpearson.com/excel/vbe.aspx)

patcher.xlsm的CopyModule

Option Explicit

Private MAGIC_NUMBER As Integer

Public Function getMagicNumber() As Integer
    getMagicNumber = MAGIC_NUMBER 
End Function

Public Function setDefaultMagicNumber() As Integer
    MAGIC_NUMBER = 7
    setDefaultMagicNumber= getMagicNumber()
End Function

patcher.xlsm的GlobalModule

Sub Proc()
  If GlobalModule.getMagicNumber= 7 Then
    MsgBox "Magic number is correct: " & GlobalModule.getMagicNumber
  Else
    MsgBox "Magic number is wrong: " & GlobalModule.getMagicNumber
  End If
End Sub

patcher.xlsm 的 Sheet1(将覆盖到 file.xlsm)

编辑:我已经修改了这个问题。即使“GlobalModule.setDefaultMagicNumber”行成功执行，变量 MAGIC_NUMBER 似乎也重新初始化为“0”。

Answer 1

欢迎来到SO并祝贺您提出了一个真正的好问题

这只是部分解决方案，根本无法令人满意。不过，我将其发布，因为它会以某种方式满足您将 Magic_Number 更改为某个所需整数的代码目的。

我尝试了一些不同的设置，目标略有不同(无法实现)。

在File.Xlsm工作簿打开事件

Private Sub Workbook_Open()
SetMagic
End Sub

在File.Xlsm的Module1中

Global Magic_Number As Integer
Sub Test_Magic()
MsgBox Magic_Number
End Sub

Sub SetMagic()
Magic_Number = 5
ThisWorkbook.Sheets(1).Range("F1").Value = Magic_Number
End Sub

在Sheet1中File.Xlsm的更改事件

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1:E20"), Target) Is Nothing Then
    Magic_Number = Magic_Number + 1
    Range("F1").Value = Magic_Number
    End If
End Sub

最后在Patch.xlsm模块1

Sub ChangeMagic()
Dim wb As Workbook, Opn As Boolean , Ln as Long
    Onp = False
    For Each wb In Application.Workbooks
    If wb.Name = "File.xlsm" Then
    Opn = True
    Exit For
    End If
    Next

    If Opn = False Then
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\File.xlsm")
    End If


    With wb.VBProject.VBComponents("Module1").CodeModule
    Ln = .ProcBodyLine("SetMagic", vbext_pk_Proc)
    .ReplaceLine Ln + 1, "Magic_number =  100"
    End With

    Application.OnTime Now + TimeValue("00:00:03"), "'File.xlsm'!Module1.SetMagic"
    wb.Activate
End Sub

线路

Application.OnTime Now + TimeValue("00:00:03"), "'File.xlsm'!Module1.SetMagic"

发挥作用。替换 VBE 中所需的代码行后，Patch.xlsm 中的宏结束，并且在 File.xlsm 激活时延迟一段时间后执行 SetMagic。 Magic_Number 将为 100。

但它仍然没有达到我的预期目的，因为在试验期间，一旦替换 VBE 代码模块行(即在执行 Sub SetMagic 之前)，Magic_Number 就会重置为 0。但您的目的将会得到解决，Magic_Number 将从 Patch.xlsm 中获取我们设置的任何值。

对于我动态设置Magic_Number = Magic_Number + 100的目标无法实现。为了实现这个目的，我必须解决方法并将 Magic_Number 的最新值存储在某些单元格等中并从那里检索(这将失去预期代码的真正精神)