vba 为什么我不能将我的工作簿定义为对象？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11054482/
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
Why can't I define my workbook as an object?
提问by Tommy Z
Why can't I define a workbook either of these ways? (I have the range bit in there just for a quick test.) How do I fix it?
为什么我不能通过这两种方式定义工作簿？（我有范围位只是为了快速测试。）我该如何解决？
This produces a "Compile Error: Type Mismatch"
这产生了一个 "Compile Error: Type Mismatch"
Sub Setwbk()
Dim wbk As Workbook
Set wbk = "F:\Quarterly Reports12 Reports\New Reports\ _
Master Benchmark Data Sheet.xlsx"
Range("A2") = wbk.Name
End Sub
And this creates a "Runtime Error '91': Object variable or with block variable not set"
这创造了一个 "Runtime Error '91': Object variable or with block variable not set"
Sub Setwbk()
Dim wbk As Workbook
wbk = "F:\Quarterly Reports12 Reports\New Reports\ _
Master Benchmark Data Sheet.xlsx"
Range("A2") = wbk.Name
End Sub
What am I missing here? I've been hammering away at VBA for a month, gotten pretty sophisticated, but this has me stumped. I'm missing something elemental.
I just want to define a workbook so I don't have to type it all out!
我在这里缺少什么？我已经在 VBA 上锤炼了一个月，变得非常复杂，但这让我很难过。我错过了一些基本的东西。
我只想定义一个工作簿，所以我不必全部输入！
回答by Doug Glancy
It's actually a sensible question. Here's the answer from Excel 2010 help:
这实际上是一个明智的问题。以下是 Excel 2010 帮助中的答案：
"The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel."
“Workbook 对象是 Workbooks 集合的成员。Workbooks 集合包含当前在 Microsoft Excel 中打开的所有 Workbook 对象。”
So, since that workbook isn't open - at least I assume it isn't - it can't be set as a workbook object. If it was open you'd just set it like:
因此，由于该工作簿未打开 - 至少我认为它没有打开 - 它不能被设置为工作簿对象。如果它是打开的，您只需将其设置为：
Set wbk = workbooks("Master Benchmark Data Sheet.xlsx")
回答by Gaffi
You'll need to open the workbook to refer to it.
您需要打开工作簿才能引用它。
Sub Setwbk()
Dim wbk As Workbook
Set wbk = Workbooks.Open("F:\Quarterly Reports12 Reports\New Reports\ _
Master Benchmark Data Sheet.xlsx")
End Sub
* Follow Doug's answerif the workbook is already open. For the sake of making this answer as complete as possible, I'm including my comment on his answer:
*如果工作簿已经打开，请遵循Doug 的回答。为了使这个答案尽可能完整，我将我对他的回答的评论包括在内：
Why do I have to "set" it?
为什么我必须“设置”它？
Setis how VBA assigns objectvariables. Since a Rangeand a Workbook/Worksheetare objects, you must use Setwith these.
Set是 VBA 分配对象变量的方式。由于 aRange和 a Workbook/Worksheet是objects，您必须Set与这些一起使用。
