ByVal and ByRef for objects

[aru_aakash]

Hello All,

I need your help in understanding this.

What does ByVal and ByRef signify when we use them in respect of object like ranges or worksheet or etc??

For eg: following is the UDF

1) Function MrExcel(ByVal ABC as range)

2) Function MrExcel(ByRef ABC as range)

Thanks

 

[Comfy]

Argument Passing ByVal and ByRef
Passing Variable ByRef And ByVal

ByRef allows the procedure to change the variable passed to it, ByVal doesn't.

 

[aru_aakash]

Thanks comfy.

This i knew. I want to know what difference will it make if we use any of them (i.e ByVal or ByRef) in the following example

<code>
Function aakash(ByVal/ByRef k As Range)
If k.Count = 1 Then
If IsNumeric(k) Then
aakash = k.Value + 1
Else
aakash = "applies to numerics"
End If
Else
aakash = "Refer one cell only"
End If
End Function
</code>


Thanks</code>

 

[RoryA]

If you pass an Object ByVal, you pass a copy of the pointer to that object. You can still manipulate the object just as if you passed it ByRef, you simply cannot alter which object the original pointer referred to.

 

[aru_aakash]

Thanks Rory. But i am still unclear about your explanation. I am aware about the concept of Pointers and i can very well relate variables of the type Integer, Boolean, String, etc with it. However, i am fail to relate the same with Objects like Range, worksheet,etc.

 

[RoryA]

A pointer is basically a memory address. For the purposes of your code (and most code) it makes no practical difference whether you pass an object ByVal or ByRef but ByVal is probably safer.
If you pass an object variable ByRef to a procedure, that procedure can change which object the variable refers to (e.g. it might change to a different range) but if you pass ByVal it can't, but either way the procedure can change properties of that object and call its methods.

Here's an example that may help to clarify:

Sub demo()
   Dim someRange As Range
   Set someRange = Range("A1")
   Call CannotChangeMe(someRange)
   MsgBox someRange.Address
   Call ChangeMe(someRange)
   MsgBox someRange.Address
End Sub
Sub CannotChangeMe(ByVal rIn As Range)
   ' can change the value
   rIn.Value = rIn.Value & vbLf & "Changed by CannotChangeMe sub"
   ' this change will not affect the calling sub
   Set rIn = Range("B2")
End Sub
Sub ChangeMe(ByRef rIn As Range)
   ' can chagne the value
   rIn.Value = rIn.Value & vbLf & "Changed by ChangeMe sub"
   ' can also change which object is referred to
   Set rIn = Range("B2")
End Sub

 

[aru_aakash]

Many Thanks Rory.