Passing objects as parameters in Excel VBA

Question

How do I pass an object to a private sub as a reference in Excel VBA? Below is what I am trying to do:

Sub main()
    Dim r As Range
    Set r = Sheets("Sheet1").Range(Cells(1, 1), Cells(27, 27))
    r.Select 'this works
    select_cells (r) 'this doesn't work
End Sub

Private Sub select_cells(a As Range)
    a.Select 'prompts Object Required error
End Sub

try call in front of select_cells: call select_cells (r) worked for me on a local test — tospig
– tospig, Commented Nov 21, 2014 at 4:37

Mathieu Guindon · Accepted Answer · 2014-11-21 05:09:58Z

10

select_cells (r) 'this doesn't work

You can't use parentheses to pass object parameters to a procedure. Just do this:

select_cells r

The archaic, obsolete Call keyword can be used, if you really want to keep the parentheses.

edited Nov 21, 2014 at 5:09

answered Nov 21, 2014 at 4:38

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

3 Comments

HowToTellAChild Over a year ago

Is there any particular reason why should avoid parentheses when passing Object as parameter?

Mathieu Guindon Over a year ago

@HowToTellAChild objects can (and a lot do!) have default properties - parentheses are forcing the evaluation of an expression, so what the procedure gets isn't the intended object but its default property. Usually that causes a type mismatch error, but things can get ugly/tricky with Variant parameters. Drop the parentheses, they're not just "for readability" - they change the meaning of your code. For non-object arguments, they're purely redundant.

Mathieu Guindon Over a year ago

If the object doesn't have a default member, then VBA fails with an error that means "I can't find that member", and execution stops.

chris neilsen · Accepted Answer · 2014-11-21 08:41:20Z

6

There are severla errors in your code

Unqualified range references refer to the ActiveSheet. So
```
Set r = Sheets("Sheet1").Range(Cells(1, 1), Cells(27, 27))
```
will error if Sheet1 is not active.
r.Select will error if Sheet1 is not active.
select_cells (r) with the brackets is incorrect. Use
```
select_cells r
```
a.Select in the Private Sub will error if Sheet1 is not active.

Here's an modified version

Sub main()
    Dim r As Range
    With Sheets("Sheet1")
        Set r = .Range(.Cells(1, 1), .Cells(27, 27))
    End With
    Debug.Print r.Address ' for diagnostic purposes
    select_cells r
End Sub

Private Sub select_cells(a As Range)
    ' Activate the worksheet first, so you can select a range on it
    a.Worksheet.Select
    a.Select
End Sub

Note on bracketed parameters

When a Sub or Function parameter is a non-object variable, bracketing the parameter overrides the ByRef/ByVal definition and passes the parameter ByVal

When a Sub or Function parameter is an object variable, bracketing the parameter causes an the default property to be passed to the Sub/Function. In the case of the OP, this is r.Value, which causes a type missmatch.

edited Nov 21, 2014 at 8:41

answered Nov 21, 2014 at 4:40

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

1 Comment

Rory Over a year ago

"passing an Object by value has no meaning" That's not really true - most events pass objects ByVal, for example. It just means you pass a copy of the pointer, not the original pointer. Using the parens forces the compiler to try and evaluate the default property of the object and pass that result-which may be an object or data type.

Collectives™ on Stack Overflow

Passing objects as parameters in Excel VBA

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related