# ByVal and ByRef for objects

#### aru_aakash

##### Board Regular
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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### aru_aakash

##### Board Regular
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
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

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
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)
Call ChangeMe(someRange)
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:

#### aru_aakash

##### Board Regular
Many Thanks Rory.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,139
Messages
5,835,638
Members
430,373
Latest member
swartzfeger

### 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.

### Which adblocker are you using?

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

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