vba Excel 中对单元格和工作表的引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6957945/
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
References to cells and worksheets in Excel
提问by user694971
I have a problem that I don't understand at all:
我有一个我完全不明白的问题：
i = 150
If ActiveWorkbook.Worksheets("foobar").Cells(i, 3).Value Like "*:*" Then
MsgBox "I found a colon!"
End If
As you might guess, the sheet foobar has at position (150, 3) a cell containing a colon, thus the message box is shown.
正如您可能猜到的，工作表 foobar 在位置 (150, 3) 处有一个包含冒号的单元格，因此显示了消息框。
Now I want to do this:
现在我想这样做：
i = 150
Cell = ActiveWorkbook.Worksheets("foobar").Cells(i, 3).Value 'fails right here
If Cell Like "*:*" Then
MsgBox "I found a colon!"
End If
Here it gives me an error saying "Object variable or With block variable not set. In fact saying:
在这里它给我一个错误说“对象变量或块变量未设置。实际上说：
Sheet = ActiveWorkbook.Worksheets("foobar")
gives a similar message. Why? What am I doing wrong? I just want a reference of that object, or at least a refence.
给出了类似的信息。为什么？我究竟做错了什么？我只想要该对象的引用，或者至少是引用。
回答by Tiago Cardoso
Bottomline:
底线：
- When handling objects, use
Set. - When handling primitivedata (integers, longs, strings*, variants*) you do not use it.
- 处理对象时，使用
Set. - 在处理原始数据（整数、长整型、字符串*、变体*）时，不要使用它。
Sheet, Workbook, Range, are objects. Therefore, you need to use Setwhen assigning them to variables.
Sheet, Workbook, Range, 是对象。因此，Set在将它们分配给变量时需要使用。
A Range.Valuereturns a Variant (that can be a long, a string, etc.) So, you cannotuse Set.
ARange.Value返回一个 Variant（可以是 long、字符串等）。因此，您不能使用Set.
==========================
==========================
Now, about your error message... I'd say then that maybe before in your code, Cellis being declared as object. Try use another variable name, or check the Cell variable type.
现在，关于你的错误信息......我会说可能之前在你的代码中，Cell被声明为 object。尝试使用其他变量名称，或检查 Cell 变量类型。
To check this, right click on it and then click in 'Definition'. Have it declared as Variantmight fix the problem (be aware of side effects it might cause, though).
要检查这一点，请右键单击它，然后单击“定义”。将其声明为Variant可能会解决问题（但请注意它可能导致的副作用）。
==========================
==========================
*I know these types aren't 'primitive'; I used as an example here for the sake of explanation's cleanliness.
*我知道这些类型不是“原始的”；为了解释的简洁起见，我在这里用作示例。
回答by Alex K.
As your assigning an object the line
作为您分配对象的行
Sheet = ActiveWorkbook.Worksheets("foobar")
should read
应该读
Set Sheet = ActiveWorkbook.Worksheets("foobar")
回答by rajah9
In your line
在你的行
Cell = ActiveWorkbook.Worksheets("foobar").Cells(i, 3).Value
you are trying to assign the value of cell (probably a string) to a cell. Take the .Valueoff and the assignment should work better. The Excel message you encountered is not the best: sometimes you get it when you assign variables of the wrong type.
您正在尝试将单元格（可能是字符串）的值分配给单元格。就拿.Value关和分配应更好地工作。您遇到的 Excel 消息并不是最好的：有时您会在分配错误类型的变量时得到它。
However, the If Cell Likemight not work. (Hint: the .Valuehas to get moved, not deleted.)
但是，If Cell Like可能不起作用。（提示：.Value必须移动，而不是删除。）
回答by anil.vemulapalli
I just tried the following code and it works probably in your code make sure the ActiveWorkBook is actually the Active Work book you want(if you are working with multiple work books)
我刚刚尝试了以下代码，它可能在您的代码中工作，请确保 ActiveWorkBook 实际上是您想要的活动工作簿（如果您正在使用多个工作簿）
Sub test()
i = 3
If ActiveWorkbook.Worksheets("Sheet1").Cells(i, 2).Value Like ":" Then
MsgBox "I found a colon!"
Else
MsgBox "didn't find a colon!"
End If
Cell = ActiveWorkbook.Worksheets("Sheet1").Cells(i, 2).Value
'fails right here
If Cell Like ":" Then
MsgBox "I found a colon!"
End If
End Sub
回答by Patrick Honorez
Use Dim MyCell as Variant(or as string) since Cell is an existing object
使用Dim MyCell as Variant（或作为字符串），因为 Cell 是一个现有对象
