vba - 为什么在电子表格中分配引用有时有效有时无效？

Question

我的 Excel 工作簿中有一些单元格可供客户输入自己的值。我希望工作簿使用默认值初始化这些单元格。为此，我有一个工作表“Arkusz do makr”，我在其中存储值。

在模块“GM”中，我声明一个变量来更轻松地引用我的工作表，如下所示:

Public M As Worksheet

然后我初始化这个变量并设置我的默认值，如下所示(在 ThisWorkbook 中):

Private Sub Workbook_Open()

    Set M = Worksheets("Arkusz do makr")

    Worksheets("Values").Range("Value1") = M.Range("Value1")
    Worksheets("Values").Range("Value2") = M.Range("Value2")
    Worksheets("Values").Range("Value3") = M.Range("Value3") `etc
End Sub

现在，有时这就像一个魅力，有时，当我打开工作簿时，我会得到一个

Run-time error '91': Object variable or With block variable not set.

有人可以向我解释一下这种行为吗？另外，我想问一下我的方法是否有意义，因为我很难掌握 Excel 中事件的顺序及其对象的范围。

编辑:另外我应该提到调试函数突出显示了我的代码中的第一行Worksheets...。在特定的工作表中，我也引用了 M 对象，尽管我认为它会改变这里的任何内容......

Answer 1

尝试如下更改此 Sub 的代码。

我添加了一个简单的错误处理 - 如果工作簿中没有工作表“Arkusze do makr”或“Values”，则会显示警告消息并且不会复制默认值。

您可以在代码中找到更多注释。

Private Sub Workbook_Open()
    Dim macrosSheet As Excel.Worksheet
    Dim valuesSheet As Excel.Worksheet
    '------------------------------------------------------------------


    With ThisWorkbook
        'This command is added to prevent VBA from throwing
        'error if worksheet is not found. In such case variable
        'will have Nothing as its value. Later on, we check
        'the values assigned to those variables and only if both
        'of them are different than Nothing the code will continue.
        On Error Resume Next
        Set macrosSheet = .Worksheets("Arkusz do makr")
        Set valuesSheet = .Worksheets("Values")
        On Error GoTo 0       'Restore default error behaviour.
    End With


    'Check if sheets [Values] and [Arkusz do makr] have been found.
    'If any of them has not been found, a proper error message is shown.
    'In such case default values are not set.
    If valuesSheet Is Nothing Then
        Call VBA.MsgBox("Sheet [Values] not found")
    ElseIf macrosSheet Is Nothing Then
        Call VBA.MsgBox("Sheet [Arkusz do makr] not found")
    Else

        'If both sheets are found, default values are copied
        'from [Arkusz do makr] to [Values].
        'Note that if there is no Range named "Value1" (or "Value2" etc.)
        'in any of this worksheet, another error will be thrown.
        'You can add error-handling for this case, similarly as above.
        With valuesSheet
            .Range("Value1") = macrosSheet.Range("Value1")
            .Range("Value2") = macrosSheet.Range("Value2")
            .Range("Value3") = macrosSheet.Range("Value3")
        End With
    End If

End Sub