Pass a range object as a parameter

[Guest]

Hi all,

How to pass a range object instead of to pass it's default value?

Clara

 

[Guest]

Clara,

You would pass it like Range("A1") for example. The important part is that
the called sub or function must be expecting a range. To demonstrate, paste
the code below in a module. Run the test sub. You will see that Range("A1")
is passed to the two subroutines but PassTheRange gets the range object
because it is expecting a range. PassTheValue gets the default value because
it is expecting a string value.


Sub test()
PassTheRange Range("A1")
PassTheValue Range("A1")
End Sub
Sub PassTheValue(strValue As String)
Debug.Print strValue
End Sub
Sub PassTheRange(r As Range)
Debug.Print r.Address
End Sub

 

[Guest]

Hi Vergel,

Thank you very much! Syntax I used is like "SubName (RangeObjcet)", it
doesn't work, but when I use "Call SubName(RangeObject)" it works.

Clara

 

[Guest]

Hi Clara,

to call sub procedures, you can use

Call SubName(RangeObject)

or

SubName RangeObject

 

[Guest]

Hi,

Just add some...
If we call a procedure with arguments we have to include the correct
arguments or variable...
like :
Call SubName(RangeObject) or
SubName RangeObject

 

[NickHK]

Halim,
Whilst it is normal to follow that convention, the brackets have a meaning
themseleves as to how the arguments are passed. Check the debug output of
these examples below to see the trend:

Private Sub CommandButton1_Click()
Dim Arg1Value As Double
Dim Arg2Value As String

Arg1Value = Sin(22 / 7)
Arg2Value = "some string"
MySub1 Arg1Value, Arg2Value
Debug.Print Arg1Value, Arg2Value

Arg1Value = Sin(22 / 7)
Arg2Value = "some string"
MySub1 (Arg1Value)
Debug.Print Arg1Value, Arg2Value

Arg1Value = Sin(22 / 7)
Arg2Value = "some string"
Call MySub1(Arg1Value)
Debug.Print Arg1Value, Arg2Value

Arg1Value = Sin(22 / 7)
Arg2Value = "some string"
MySub1 (Arg1Value), (Arg2Value)
Debug.Print Arg1Value, Arg2Value

Arg1Value = Sin(22 / 7)
Arg2Value = "some string"
Call MySub1((Arg1Value), (Arg2Value))
Debug.Print Arg1Value, Arg2Value

Arg1Value = Sin(22 / 7)
Arg2Value = "some string"
Call MySub1(Arg1Value, Arg2Value)
Debug.Print Arg1Value, Arg2Value

End Sub

Sub MySub1(ByRef arg1 As Double, Optional ByRef arg2 As String)
arg1 = 10
arg2 = "New string"
End Sub

NickHK