Function Return Range

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

A range object refers to a range in an open workbook; your sub closes the workbook before exiting.

Thanks Shg,

Revised Function below.
The lesson learned: keep Function really simple to a singular purpose.

thx
w

Public Function GetExcelRangeFunc(ws As Worksheet, _
                                  lngRows As Long, _
                                  lngCols As Long) As Range
    'Declare objects
        Dim rng As Range
        
    'Declare variables
        Dim lngMeRows As Long
        Dim lngMeCols As Long
        
    '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
        
    'Tidy up
        Set rng = Nothing
   
End Function

Good job.

Or, more compactly,

Public Function GetExcelRangeFunc(wks As Worksheet, _
                                  iRow As Long, _
                                  iCol As Long) As Range
  Set GetExcelRangeFunc = wks.Range("A1").Resize(GetLast(wks, "r", iCol), _
                                                 GetLast(wks, "c", iRow))
End Function

Function GetLast(wks As Worksheet, sRowCol As String, iRowCol As Long) As Long
  With wks
    Select Case sRowCol
      Case "R", "r"
        GetLast = .Cells(.Rows.Count, iRowCol).End(xlUp).Row
      Case "C", "c"
        GetLast = .Cells(iRowCol, .Columns.Count).End(xlToLeft).Column
    End Select
  End With
End Function