如何从 vba 中的用户定义函数返回范围对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7371549/
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
How to return a range object from a user defined function in vba
提问by eagleye
I have this piece of code in excel:
我在excel中有这段代码：
Private Function RelCell(NmdRng as String) as Range
Set RelCell = Range(NmdRng).Cells(1,1)
End Function
it gives the runtime error "91': object variable or with block variable not set.
它给出了运行时错误“91”：对象变量或未设置块变量。
I really don't know what is the problem with my function.. someone does?
我真的不知道我的功能有什么问题..有人吗？
回答by aevanko
I don't know if this is the problem but your are only setting the range and aren't returning anything from the function.
我不知道这是否是问题所在，但您只是在设置范围并且没有从函数中返回任何内容。
Try declaring a range variable with a different name as the function and return that.
尝试声明一个与函数名称不同的范围变量并返回。
回答by JMax
Actually, you should be able to return a range from a UDF as described in this MSDN Thread.
实际上，您应该能够从 UDF 返回一个范围，如本MSDN Thread 中所述。
Here is the code given by the MVP:
这是MVP给出的代码：
Function GetMeRange(rStartCell As Range, lRows As Long, iColumns As Integer) As Range
Set GetMe = rStartCell.Resize(lRows, iColumns) ' note the use of Set here since we are setting an object variable
End Function
(and it works)
（它有效）
Tiago's comment points out a very right thing, as you want to access a named range, it should be defined first.
You can try to set a breakpoint in your UDF and see if the Range(NmdRng)is defined.
Tiago 的评论指出了一件非常正确的事情，当您要访问命名范围时，应该首先定义它。
您可以尝试在 UDF 中设置断点并查看是否Range(NmdRng)已定义。
回答by deasa
Your named range already has a cell reference attached to it, so you shouldn't need to have the .Cells(1,1)at the end of it.
您的命名范围已经附加了一个单元格引用，因此您不需要.Cells(1,1)在它的末尾加上 。
Using the .Range(nmdRng)property alone will return the range object you are looking for.
.Range(nmdRng)单独使用该属性将返回您正在寻找的范围对象。
Try:
尝试：
Private Function RelCell(NmdRng as String) as Range
Set RelCell = Range("NmdRng")
End Function
回答by Qbik
Please rewrite your code and test it as follows :
请重写您的代码并进行如下测试：
Private Function RelCell(NmdRng as String) as Range
Dim TestRange As Range
Set TestRange=Range(NmdRng)
TestRange.Activate 'I think that error will occur here because, NmdRng is somehow invalid
Set RelCell = TestRange.Cells(1,1)
End Function
