获取错误“对象变量或未设置块变量” [英] getting error "Object Variable or With block variable not set"

问题描述

我已经创建了一个Excel加载项，它确定了 ActiveSheet 和 ActiveWorkbook 的名称。我使用的代码如下。当我运行加载项时，会在消息框变量设置之后显示上述错误。但是当我以宏运行它是正常工作。我不明白加载项正在发生什么。有人可以帮助我吗？

I have created an Add-In for Excel which determines the name of ActiveSheet and ActiveWorkbook. The code I used is below. When I run the Add-In it is showing the above mentioned error after the message box "variables set". But when I run it in macros it is working fine. I don't understand what is happening with the Add-In. Could anyone help me with this?

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

推荐答案

导致此问题的常见问题忘记使用'Set'赋值给变量。

A common issue that causes this issue is forgetting to use 'Set' with assigning a value to a variable.

这篇关于获取错误“对象变量或未设置块变量”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！