excel - 如何在特定工作表中运行宏，但根据从另一个 Excel 文件/工作表维护的值控制开始？

Question

你好，
如何在特定工作表的后台运行宏，并且仅根据另一个 Excel 文件/工作表中维护的计算值使用和控制宏的触发器？
用例是我想运行一个名为“TargetCalc”的宏——它会触发特定工作表中的 Marcro_01 或 Marcro_02，具体取决于该工作表中的单元格 C3 的值是 1 还是 2。
如果在事件 Excel 工作表本身中维护或计算所有内容，则下面的宏示例效果很好。但是 - 如果我想从完全打开的另一个 Excel 文件(我的焦点所在的位置)控制单元格 C3 中的值，那么我会收到错误消息“运行时错误'9':订阅超出范围”
测试设置:

保存一个名为“DisplayExcelFile.xlsm”的 Excel 文件并将“Sheet1”重命名为“DisplaySheet1”

复制下面的 VBA 代码

在文件“DisplayExcelFile.xlsm”中 - 在单元格 C3 中，输入:=IF(D3<100,1,2)

在文件“DisplayExcelFile.xlsm”中 - 在单元格 D3 中，输入值 105

结果:
a) 单元格 C3 将显示 2，因为单元格 D3 为 105，大于 100 => 触发 Macro_01
b) 如果您手动将 Cell D3 从 105 更新为 78 => 触发 Macro_02
到目前为止一切都很好——这个例子很好用。

在同一台机器上打开第二个 Excel - 并将“Sheet1”重命名为“CalcSheet”并使用名称“CalcFromExcelFile.xlsm”

保存此 Excel 文件

在文件“CalcExcelFile.xlsm”中 - 在单元格 A2 中，输入值 130

下一步:在同一台机器上打开两个 Excel 文件 - 并返回第一个 Excel 文件“DisplayExcelFile.xlsm”单元格 D3 并将单元格 D3 从值(105 或 78...)更改为引用另一个单元格的公式另一个打开的 Excel 文件中的 A2:=[CalcFromExcelFile.xlsm]CalcSheet!$A$2

结果:
a) 只要“焦点”位于第一个“DisplayExcelFile.xlsm”中名为“DisplaySheet1”的工作表中，当使用不同数字更新单元格 D3 时，该功能就可以正常工作。
b)但是:如果您在第二个 Excel 文件“CalcExcelFile.xlsm”中具有“焦点”，并将第二个 Excel 文件中的单元格 A2 更新为值 97 - 然后在第一个“DisplayExcelFile”中的工作表“DisplaySheet1”中的宏.xlsm”不起作用。
弹出的 VBA 错误说:
运行时错误‘9’:订阅超出范围
当我“调试”时——它指向 Macro_01 的行:
“工作表(“DisplaySheet”).Range(“A3”).Select”
此外:

我追求的工作流程是在同一台机器上打开两个 Excel 文件——其中第一个 Excel 文件“DisplayExcelFile.xlsm”用于显示目的并运行宏——但所有手动更新(焦点)都在第二个 Excel 上文件“CalcFromExcelFile.xlsm”。

在第一个“DisplayExcelFile.xlsm”中，工作表将始终命名为“DisplaySheet1”——因此无需修改代码，以便在我重命名工作表时可以正常工作。我希望当我让这个用例工作时，我将在同一个 Excel 文件中创建第二张和第三张工作表(“DisplaySheet2”和“DisplaySheet2”——这样我也可以为这些工作表复制相同的内容。

当我试图让 VBA 代码工作时——我试图直接引用工作表名称——我还试图改进“选择”的使用，但我卡住了。

编码
模块1

Option Explicit

Public TargetValue As Variant
Private Const cTarget As String = "C3"

Sub TargetCalc(ws As Worksheet)
'
If ws.Range(cTarget) <> TargetValue Then
    Application.EnableEvents = False
    Select Case ws.Range(cTarget).Value
        Case 1
            Macro_01
        Case 2
            Macro_02
    End Select
    TargetValue = ws.Range(cTarget).Value
    Application.EnableEvents = True
End If
End Sub

Sub Macro_01()
'
    Worksheets("DisplaySheet").Range("A3").Select
    ActiveCell.FormulaR1C1 = "Hi_01"
    Application.Wait Now + TimeValue("0:00:01")
    ActiveCell.FormulaR1C1 = "There_01"
End Sub

Sub Macro_02()
'
    Worksheets("DisplaySheet").Range("A3").Select
    ActiveCell.FormulaR1C1 = "Hi_02"
    Application.Wait Now + TimeValue("0:00:01")
    ActiveCell.FormulaR1C1 = "There_02"
End Sub

Sheet1 (DisplaySheet)

Option Explicit

Private Sub Worksheet_Calculate()
    TargetCalc Me
End Sub

本工作手册
...空的...
非常感谢!

Answer 1

不要选择任何东西，[几乎]永远。如果你告诉 VBA 地址，VBA 会找到它。因此，您需要非常小心地整理您的工作簿和工作表。我通常在开始编程之前这样做。这就是为什么您在下面我的代码顶部找到定义的原因。
不要担心工作表名称。左侧的第一个选项卡始终是 Worksheets(1) .您可以通过它的索引号来解决它。我认为您可能会在显示书中使用“DisplaySheet1”之类的名称，但在计算工作簿中使用索引。要点是:准确识别工作簿和工作表。请注意，工作表一旦定义为 Set语句，知道它所属的工作簿，您可以使用“Ws.Workbook.Name”检索该工作簿的名称。
请注意 ThisWorkbook标识代码所在的工作簿，而 ActiveWorkbook是有重点的那个。它们可以相同，但不一定相同。
不要为宏数字而烦恼。相反，了解论点。一个基本上完成一项工作的宏，如您的 Macro_01 和 Macro_02，可以组合成一个宏，并将 1 或 2 作为参数提供。
下面的代码将为您提供帮助。

Option Explicit

Sub Main()
    ' 149
    
    Dim WsDisplay           As Worksheet
    Dim WsCalc              As Worksheet
    Dim Arg                 As String
    
    Set WsDisplay = ThisWorkbook.Sheets(1)
    Set WsCalc = ActiveWorkbook.Sheets(1)
    
    If WsDisplay.Cells(3, "D").Value < 100 Then
        Arg = 1
    Else
        Arg = 2
    End If
'    ' Note: I would use the following in place of the above
'    '       because it's only a single line
'    Arg = 2 + (WbDisplay.Worksheets(1).Cells(3, "D").Value < 100)
    
    Action WsDisplay, Arg
End Sub

Private Sub Action(WsDisplay As Worksheet, _
                   ByVal Switch As Integer)
    ' 149

    Dim Target              As Range
    Dim Txt                 As String
    
    Select Case Switch
        Case 1
            Set Target = WsDisplay.Cells(3, 1)     ' 1 = column A
            Txt = "Hi"
        Case 2
            Set Target = WsDisplay.Cells(3, 2)     ' 2 = column B
            Txt = "Hello"
        Case Else
            MsgBox "Invalid switch"
            Exit Sub
    End Select
    
    With Target
        .Value = Txt
        .Offset(1).Value = "there!"
    End With
End Sub

在 Main 中查找工作表和工作簿定义程序。设计是让 CalcSheet 成为事件的，但在后面的代码中，该工作表从未使用过。所有操作都在非事件的 DisplaySheet 上。