ByVal and ByRef for objects

aru_aakash

Board Regular
Joined
May 22, 2009
Messages
74
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 :)
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

aru_aakash

Board Regular
Joined
May 22, 2009
Messages
74
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,065
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
May 22, 2009
Messages
74

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,065
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,333
Messages
5,595,560
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top