获取错误信息 "Object Variable or With block variable not set"

Question

我为Excel创建了一个插件，可以确定ActiveSheet和ActiveWorkbook的名称。我使用的代码如下。当我运行该插件时，在消息框“变量设置”后它显示上述错误。但是当我在宏中运行它时，它工作得很好。我不明白插件发生了什么事情。有人能帮我解决吗？

Sub sheetvalues()
    Dim bk As Workbook, sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
    Dim book As String, sht As String, i As Integer, j As Integer
    Dim att(1 To 4) As String, att_col(1 To 4) As Integer

    MsgBox ("variables set")

    book = ActiveWorkbook.Name
    sht = ActiveSheet.Name
    MsgBox ("names set")

    Set bk = Workbooks.Add
    With bk
        .Title = "MissingValues"
        .SaveAs Filename:="MissingValues.xls"
    End With

    Set sht1 = bk.Sheets.Add
    sht1.Name = "EndOne"
    Set sht2 = bk.Sheets.Add
    sht2.Name = "EndTwo"
    Set sht3 = bk.Sheets.Add
    sht3.Name = "EndThree"

    MsgBox (book & "  " & sht)
    MsgBox ("completed")
End Sub

- Santhi Kabir

---

你是否已经在工作簿打开时运行插件？ - Ripster

如果你的加载项是唯一加载的工作簿，可能就不存在 ActiveWorkbook... - Tim Williams

Answer 1

一个常见的问题是在给变量赋值时忘记使用“Set”关键字。

- Gerhard Powell

---

非常感谢您！ - Moffen

Answer 2

检查Excel中的工作簿是否询问您是否要打开一个受写保护的版本。我认为，在出现这个问题时，工作簿不被视为活动状态，其他任何内容也不是。

- Chris

Answer 3

就像@TimWilliams所说的那样，如果您的插件是唯一加载的工作簿，则会出现此错误。在这种情况下，没有活动工作簿，您的代码会在该行失败

book = ActiveWorkbook.Name

您可以通过添加以下代码行来检查工作簿是否存在：

Set bk = Application.ActiveWorkbook
If bk Is Nothing Then
    MsgBox ("No workbook open. Creating a new one.")
    Set bk = Workbooks.Add(xlWBATWorksheet)
    Set sht1 = bk.ActiveSheet
End If

所以你最终得到：

Sub sheetvalues()
    Dim bk As Workbook, sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
    Dim book As String, sht As String, i As Integer, j As Integer
    Dim att(1 To 4) As String, att_col(1 To 4) As Integer

    MsgBox ("variables set")

    Set bk = Application.ActiveWorkbook
    If bk Is Nothing Then
        MsgBox ("No workbook open. Creating a new one.")
        Set bk = Workbooks.Add(xlWBATWorksheet)
        Set sht1 = bk.ActiveSheet
    End If


    book = ActiveWorkbook.Name
    sht = ActiveSheet.Name
    MsgBox ("names set")

    Set bk = Workbooks.Add
    With bk
        .Title = "MissingValues"
        .SaveAs Filename:="MissingValues.xls"
    End With

    Set sht1 = bk.Sheets.Add
    sht1.Name = "EndOne"
    Set sht2 = bk.Sheets.Add
    sht2.Name = "EndTwo"
    Set sht3 = bk.Sheets.Add
    sht3.Name = "EndThree"

    MsgBox (book & "  " & sht)
    MsgBox ("completed")
End Sub

- Frank H.