将范围对象传递给 excel-vba 中的函数——收到“需要对象”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15641693/
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 range objects to functions in excel-vba -- receive "Object Required" error
提问by user1860694
I'm trying to build a function that will take an input cell (say "B5") and return a range object that refers to a table (of which the supplied cell is the top-right entry)
我正在尝试构建一个函数，该函数将接受一个输入单元格（比如“B5”）并返回一个引用表格的范围对象（其中提供的单元格是右上角的条目）
Sub Macro1()
Dim testCell As Range
testCell = Worksheets("HMA").Range("B5")
ReturnTable testCell
End Sub
Function ReturnTable(cell As Range)
firstcell = cell
lastrow = firstcell.End(x1Down)
Table = Range(firstcell, lastrow + 5).Value
End Function
I've been running into a lot of problems here, and I feel like I'm missing something simple. The error that I'm getting is "Object Required" at the lastRow line.
我在这里遇到了很多问题，我觉得我错过了一些简单的东西。我得到的错误是 lastRow 行中的“Object Required”。
Looking through it in debug mode I see that testCell is assigned the value of the range object (I assume this is the default). What am I missing here?
在调试模式下查看它，我看到 testCell 被分配了范围对象的值（我假设这是默认值）。我在这里缺少什么？
Is my approach even sound? Should I be looking at solving this problem in a different way?
我的方法是否合理？我应该以不同的方式解决这个问题吗？
回答by Daniel M?ller
The End returns a Range object, so, lastrow must be a range variable.
End 返回一个 Range 对象，因此 lastrow 必须是一个范围变量。
Function ReturnTable(firstcell as Range) as Range 'add this as range to tell the result of the function is a range
dim LastCell as Range
Set LastCell = firstcell.END(xldown)
Set ReturnTable = Range(firstcell, lastcell) 'must use the same name of the function
End Function
If you want 5 cells below lastcell, use LastCell.Offset(5,0)
如果你想在 lastcell 下面 5 个单元格，请使用 LastCell.Offset(5,0)
And in Macro1, you probably will want something like
在 Macro1 中，您可能需要类似的东西
Set SomeVar = ReturnTable(testcell)
回答by deramko
When you assign Objects (like Range) you have to use "Set".
当您分配对象（如范围）时，您必须使用“设置”。
If you assign Range without "Set" VBA will assign the value of the Range and not the Range itself.
如果在没有“设置”的情况下分配范围，VBA 将分配范围的值而不是范围本身。
Set testCell = Worksheets("HMA").Range("B5")
