Passing objects as parameters in Excel VBA

Question

How to pass an object to a private sub as a link in Excel VBA? Below I am trying to do the following:

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 

Answer 1

There are severla errors in your code

• Links to unqualified ranges apply to ActiveSheet . So

   Set r = Sheets("Sheet1").Range(Cells(1, 1), Cells(27, 27)) 

  it will be an error if Sheet1 not active.

• r.Select will be an error if Sheet1 is inactive.

• select_cells (r) with brackets is incorrect. Use

   select_cells r 

• a.Select in a private Sub will be an error if Sheet1 not active.

Here is the 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 about parameter brackets

When a Sub or Function parameter is a variable other than an object, the parameter bracket overrides the definition of ByRef / ByVal and passes the ByVal parameter

If the Sub or Function parameter is an object variable, then as a result of bracketing, this parameter sets the default property to Sub / Function . In the case of OP, this is r.Value , which causes type flaws.

Answer 2

 select_cells (r) 'this doesn't work 

You cannot use parentheses to pass object parameters to a procedure. Just do the following:

 select_cells r 

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