Passing range objects to functions in excel-vba -- receive "Object Required" error

Question

I'm trying to build a function that will take an input cell (say "B5") and return a range object that refers to a table (of which the supplied cell is the top-right entry)

Sub Macro1()
Dim testCell As Range

testCell = Worksheets("HMA").Range("B5")
ReturnTable testCell

End Sub

Function ReturnTable(cell As Range)
    firstcell = cell
    lastrow = firstcell.End(x1Down)
    Table = Range(firstcell, lastrow + 5).Value
End Function

I've been running into a lot of problems here, and I feel like I'm missing something simple. The error that I'm getting is "Object Required" at the lastRow line.

Looking through it in debug mode I see that testCell is assigned the value of the range object (I assume this is the default). What am I missing here?

Is my approach even sound? Should I be looking at solving this problem in a different way?

You need to use Set when assigning the value of testCell (or any other object variable), otherwise you're assigning the default property of the range (in this case .Value) — Tim Williams
– Tim Williams, Commented Mar 26, 2013 at 16:13

Daniel Möller · Accepted Answer · 2013-03-26 16:14:30Z

4

The End returns a Range object, so, lastrow must be a range variable.

Function ReturnTable(firstcell as Range) as Range 'add this as range to tell the result of the function is a range
    dim LastCell as Range
    Set LastCell = firstcell.END(xldown)
    Set ReturnTable = Range(firstcell, lastcell) 'must use the same name of the function
End Function

If you want 5 cells below lastcell, use LastCell.Offset(5,0)

And in Macro1, you probably will want something like

Set SomeVar = ReturnTable(testcell)

answered Mar 26, 2013 at 16:14

Daniel Möller

86.8k24 gold badges202 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

deramko · Accepted Answer · 2013-03-26 16:15:35Z

3

When you assign Objects (like Range) you have to use "Set".

If you assign Range without "Set" VBA will assign the value of the Range and not the Range itself.

Set testCell = Worksheets("HMA").Range("B5")

answered Mar 26, 2013 at 16:15

deramko

2,8441 gold badge20 silver badges27 bronze badges

Collectives™ on Stack Overflow

Passing range objects to functions in excel-vba -- receive "Object Required" error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related