Menu
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
540
asked Nov 21 '14 04:11
I rarely used ByRef in VBA since Objects are always passed by reference. The parameter just defines if the reference is passed by reference or by value more information here. The compile error should highlight a specific row.
You can pass an argument by value if you include the ByVal keyword in the procedure's declaration. Arguments passed by value consume from 2–16 bytes within the procedure, depending on the argument's data type. Larger data types take slightly longer to pass by value than smaller ones.
VBA allows you to pass variables into subroutines and functions in two ways. You can specify either ByVal or ByRef for each of the variables that are passed in. The ByVal and ByRef distinction for subroutine and function parameters is very important to make. In VBA all objects are passed by reference.
To pass an object as an argument we write the object name as the argument while calling the function the same way we do it for other variables. Syntax: function_name(object_name); Example: In this Example there is a class which has an integer variable 'a' and a function 'add' which takes an object as argument.
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.
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
