vba 收到错误“对象变量或未设置块变量”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17691458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
getting error "Object Variable or With block variable not set"
提问by Santhi Kabir
I have created an Add-In for Excel which determines the name of ActiveSheetand 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?
我为 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
回答by Gerhard Powell
A common issue that causes this issue is forgetting to use 'Set' with assigning a value to a variable.
导致此问题的一个常见问题是忘记使用“Set”为变量赋值。
回答by Frank H.
Like @TimWilliams said, you will get this error if your add-in is the only workbook loaded. In that case there is no active workbook and your code is failing on the line
就像@TimWilliams 所说的那样，如果您的加载项是唯一加载的工作簿，您将收到此错误。在这种情况下，没有活动的工作簿并且您的代码在线失败
book = ActiveWorkbook.Name
You can check for the existence of a workbook by adding the following lines:
您可以通过添加以下行来检查工作簿是否存在：
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
So you end up with:
所以你最终得到：
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
回答by Chris
Check if the workbook in Excel is asking you if you want to open a write-protected version or not. I think while this question is present the workbook is not considered Active, nor is any other
检查 Excel 中的工作簿是否询问您是否要打开写保护版本。我认为虽然存在这个问题，但工作簿不被视为活动，也不是任何其他
