CODEWITHSUNDEEP
vbaexcel
Andrei Utkin
Andrei Utkin

Reputation: 71

Passing objects as parameters in Excel VBA

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

Upvotes: 7

Views: 32001

Answers (2)

chris neilsen
chris neilsen

Reputation: 53146

There are severla errors in your code

  1. 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.

  2. r.Select will error if Sheet1 is not active.

  3. select_cells (r) with the brackets is incorrect. Use

    select_cells r

  4. 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.

Upvotes: 6

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

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.

Upvotes: 11

Related Questions