ByVal - Creates a whole new replica of the object with all of the same
settings as the object that is being passed onto it,

Nope. Peter is correct that ByVal and ByRef specify only how the
pointer to the object is passed, not whether the object itself, or any
sort of clone of the object, is passed. It would tremendous overhead
to create a clone or replica of the object and then pass that. You
can pass the Application object around using ByVal and certainly no
replica of the Application is created. That would mean creating
replicas of all worksheets in all open workbook and all that. That's
just not how it works.

ByVal and ByRef work the same way regardless of whether the object is
defined in the typelib, such as a Range, or whether it is a custom
object like Class1. Internal to VBA's "compiled" code, I don't think
such as distinction is even made.

You can see this quite simply with the following code. First, create a
class name Class1 containing

Public Text As String

Then in a normal module, use

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''
Dim C1 As Class1
Dim C2 As Class1

Sub AAA()
Set C1 = New Class1
Set C2 = New Class1
C1.Text = "111"
C2.Text = "222"
Debug.Print "#1 Before PassByVal: C1: " & C1.Text & " C2: " & C2.Text
' pass ByVal
PassByVal C1
Debug.Print "#2 After PassByVal: C1: " & C1.Text & " C2: " & C2.Text

' reset
C1.Text = "111"
C2.Text = "222"
Debug.Print "#3 Before PassByRef: C1: " & C1.Text & " C2: " & C2.Text
' pass ByRef
PassByRef C1

Debug.Print "#4 After PassByRef: C1: " & C1.Text & " C2: " & C2.Text

End Sub

Sub PassByVal(ByVal C As Class1)
Set C = C2
End Sub

Sub PassByRef(ByRef C As Class1)
Set C = C2
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''

The immediate window will show:

#1 Before PassByVal: C1: 111 C2: 222
#2 After PassByVal: C1: 111 C2: 222
#3 Before PassByRef: C1: 111 C2: 222
#4 After PassByRef: C1: 222 C2: 222


Line #1 shows that C1 has text 111 and C2 has text 222, as one would
expect. After C1 is passed ByVal to PassByVal, line #3 shows that its
Text value is still 111. This indicates that the original object C1
was not changed to point to another object. *WITHIN PassByVal*, C is
changed to point to C2, but since the pointer was ByVal, the object is
unchanged back in Sub AAA.

Line #3 is just from the reset to original values. Line #4 shows that
BOTH C1 and C2 now have a Text value of 222, which is so because in
PassByRef, the parameter C (pointing to C1) is changed to point to C2.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 12 Dec 2008 11:53:30 -0500, "Ronald R. Dodge, Jr."
wrote:

The way I understood the ByRef and ByVal are the following:

ByRef - References to the object, and any changes done to the object is done
for any variable referencing to that object, rather it be done in the
original code or in the code referencing to the object.

ByVal - Creates a whole new replica of the object with all of the same
settings as the object that is being passed onto it, but any changes done to
the original object is not reflects in the new object, just like any changes
done in the new object is not reflected in the original object.

Though I haven't fully tested it to be sure with object variables, but
simple data type variables does work like this.