在 Excel VBA 中将对象作为参数传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27054151/
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
Passing objects as parameters in Excel VBA
提问by Andrei Utkin
How do I pass an object to a private sub as a reference in Excel VBA? Below is what I am trying to do:
如何将对象作为 Excel VBA 中的引用传递给私有子？以下是我正在尝试做的事情：
Sub main()
Dim r As Range
Set r = Sheets("Sheet1").Range(Cells(1, 1), Cells(27, 27))
r.Select 'this works
select_cells (r) 'this doesn't work
End Sub
Private Sub select_cells(a As Range)
a.Select 'prompts Object Required error
End Sub
回答by Mathieu Guindon
select_cells (r) 'this doesn't work
select_cells (r) 'this doesn't work
You can't use parentheses to pass object parameters to a procedure. Just do this:
不能使用括号将对象参数传递给过程。只需这样做：
select_cells r
The archaic, obsolete Callkeyword can be used, if you reallywant to keep the parentheses.
Call如果您真的想保留括号，可以使用过时的、过时的关键字。
回答by chris neilsen
There are severla errors in your code
您的代码中有几个错误
Unqualified range references refer to the
ActiveSheet. SoSet r = Sheets("Sheet1").Range(Cells(1, 1), Cells(27, 27))will error if
Sheet1is not active.r.Selectwill error ifSheet1is not active.select_cells (r)with the brackets is incorrect. Useselect_cells ra.Selectin the Private Sub will error ifSheet1is not active.
非限定范围引用是指
ActiveSheet. 所以Set r = Sheets("Sheet1").Range(Cells(1, 1), Cells(27, 27))如果
Sheet1不活动会出错。r.Select如果Sheet1不活动会出错。select_cells (r)用括号是不正确的。用select_cells ra.Select如果Sheet1不活动，则在 Private Sub 中会出错。
Here's an modified version
这是修改后的版本
Sub main()
Dim r As Range
With Sheets("Sheet1")
Set r = .Range(.Cells(1, 1), .Cells(27, 27))
End With
Debug.Print r.Address ' for diagnostic purposes
select_cells r
End Sub
Private Sub select_cells(a As Range)
' Activate the worksheet first, so you can select a range on it
a.Worksheet.Select
a.Select
End Sub
Note on bracketed parameters
注意括号中的参数
When a Subor Functionparameter is a non-object variable, bracketing the parameter overrides the ByRef/ByValdefinition and passes the parameter ByVal
当SuborFunction参数是非对象变量时，将参数括起来覆盖ByRef/ByVal定义并传递参数ByVal
When a Subor Functionparameter isan object variable, bracketing the parameter causes an the default property to be passed to the Sub/Function. In the case of the OP, this is r.Value, which causes a type missmatch.
当SuborFunction参数是对象变量时，将参数括起来会导致将默认属性传递给Sub/ Function。在 OP 的情况下，这是r.Value，这会导致类型不匹配。
