Hi all,

Using Excel 2010.

Trying to return a Range Object from a Function, but I receive "Object Required" from the calling Sub().

I printed the Range Address from the Function that means the Range exists, at least in the Function.

Thoughts on what I am doing wrong?

thx
w

debug.print output
rng $A$1:$N$7602
GetExcelRangeFunc $A$1:$N$7602 
Option Explicit
Sub Foo()
Dim GetRange As Range
Set GetRange = GetExcelRangeFunc(lngRows:=7, _
                                                 lngCols:=2)
End Sub

Public Function GetExcelRangeFunc(lngRows As Long, _
                                  lngCols As Long, _
                                  Optional lngSheetIndex As Long = 1) As Range

    'Declare objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rng As Range
        
    'Declare variables
        Dim strFile As String
        Dim lngMeRows As Long
        Dim lngMeCols As Long

    'Get file for import
        strFile = GetFDObjectName(strDialogType:="File", _
                                  strTitle:="Select A File For Import")
        
    'Open the import file
        Set wb = Workbooks.Open(strFile)
        Set ws = wb.Worksheets(lngSheetIndex)
        
    'Get last column - use row 7
        lngMeCols = GetLast(ws:=ws, _
                            RC:="c", _
                            lngRowColumn:=lngRows)
                                
    'Get last row - use col 2
        lngMeRows = GetLast(ws:=ws, _
                            RC:="r", _
                            lngRowColumn:=lngCols)
                                
    'Create range
        With ws
            Set rng = .Range(.Cells(1, 1), .Cells(lngMeRows, lngMeCols))
        End With
        
    'Pass object to function
        Set GetExcelRangeFunc = rng
        
        Debug.Print "rng", rng.Address
        Debug.Print "GetExcelRangeFunc", GetExcelRangeFunc.Address
        
    'Tidy up
        wb.Close
        Set rng = Nothing
        Set ws = Nothing
        Set wb = Nothing
   
End Function