无法将单元格范围的地址从一个函数返回到另一个函数，我得到一个“对象必需”错误

I get an 'object required' error when I try to return the value of a range from one function to another function. I read the other answers about 'object required' but I cannot figure out how that relates to my code. Forgive me but I'm new to VBA. Here's my code:

当我尝试将范围的值从一个函数返回到另一个函数时,我得到“对象必需”错误。我阅读了有关“需要对象”的其他答案,但我无法弄清楚它与我的代码有什么关系。原谅我,但我是VBA的新手。这是我的代码:

Function functionOne()
    Dim r As Range
    Workbooks("workbook1.xls").Activate
    Sheets("sheet1").Select
    Set r = GetRange("A1")
    Application.Goto r
End Function

Function GetRange(strStart As String) As Variant
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Set rng1 = Cells.Find("*", [A1], , , xlByRows, xlPrevious)
    Set rng2 = Cells.Find("*", [A1], , , xlByColumns, xlPrevious)
    Set rng3 = Range([strStart], Cells(rng1.Row, rng2.Column))
    GetRange = rng3.Value
End Function

The 'object required' error happens at the Set r = GetRange("A1")in functionOne(). Thanks for your help.

“对象必需”错误发生在functionOne()中的Set r = GetRange(“A1”)处。谢谢你的帮助。

1 个解决方案

#1

Change your GetRange function to return Range object:

更改GetRange函数以返回Range对象:

Function GetRange(strStart As String) As Range
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Set rng1 = Cells.Find("*", [A1], , , xlByRows, xlPrevious)
    Set rng2 = Cells.Find("*", [A1], , , xlByColumns, xlPrevious)
    Set rng3 = Range([strStart], Cells(rng1.Row, rng2.Column))
    Set GetRange = rng3
End Function