Can a VBA function in Excel return a range?

calendar_today Asked Jan 13, 2009
thumb_up 36 upvotes
history Updated April 16, 2026

Question posted 2009 · +15 upvotes

I seem to be getting a type mismatch error when trying to do something like this:

In new workbook:

A1 B1
5  4

Function Test1() As Integer
    Dim rg As Range
    Set rg = Test2()
    Test1 = rg.Cells(1, 1).Value
End Function
Function Test2() As Range
    Dim rg As Range
    Set rg = Range("A1:B1")
    Test2 = rg
End Function

Adding =Test1() should return 5 but the code seems to terminate when returning a range from test2(). Is it possible to return a range?

Accepted answer +36 upvotes

A range is an object. Assigning objects requires the use of the SET keyword, and looks like you forgot one in your Test2 function:

Function Test1() As Integer
    Dim rg As Range
    Set rg = Test2()
    Test1 = rg.Cells(1, 1).Value
End Function

Function Test2() As Range
    Dim rg As Range
    Set rg = Range("A1:B1")
    Set Test2 = rg         '<-- Don't forget the SET here'
End Function

Top excel-vba Q&A (6)

+36 upvotes ranks this answer #4 out of 136 excel-vba solutions on this site — top 3%.
excel-vba