Error in passing named range as argument in VBA

Question

I'm having a problem while passing ranges across two subprocedures. Can you suggest the possible error ?

I have defined two subprocedures in VBA. In the first subprocedure, I have a selection of cells (3X3 MATRIX), named under "ABC" which I'm copying to another selection of cells "PQR". This is working :)

I wanted to enable "all borders" when the values are copied on PQR from ABC. For this I recorded a macro.

But, whenever I run this subprocedure, it is giving an error that object doesn't exist.

I called the macro in the following manner :

All_border_test PQR

The code for subprocedure is as given below :

Sub All_borders_test(d As Range)
Range(d).Select

' Recorded Macro to enable all borders (its working) 
End Sub()

Just use d.Select instead of Range(d).Select. You already have a range object. — Tom, May 26 '15 at 07:32
Hi Tom, I made the change as you said .. d.select. While calling I made this change .. Call All_borders_test(PQR) --- It is giving a compile error" Byref argument type mismatch" — Gayathry, May 26 '15 at 07:36

score 0 · Accepted Answer · edited May 23 '17 at 12:21

1- Take a look at:
What's the RIGHT way to reference named cells in Excel 2013 VBA? (I know I'm messing this up)
and
VBA Reference Named Range ActiveSheet
2- fix your code to be like:

Sub All_borders_test(d As Range)
    d.Select ' old code was: Range(d).Select
End Sub 'no parenthesis here

3- you can call All_borders_test using one of the following calls:

Call All_borders_test([PQR])
Call All_borders_test(Range("PQR"))
Call All_borders_test(Sheet1.Range("PQR")) ' assuming that PQR range exists in Sheet1

Error in passing named range as argument in VBA

1 Answers1