Someone has asked me to summarize this thread.
The shortest summary would be:
Don't rely on defaults. Specify what you want to happen.
My favorite example of this isn't in the ByVal/ByRef area, but with Split. Specifying the delimiter in Split, arrX = Split(strX, " "), makes revision easier and saves me from having to remember Split's default delimiter.
A longer summary would include a discussion of ByRef and ByVal.
When an argument is passed to a procedure (Sub, Function or Property (Get or Let)), if it is passed ByRef, any changes that the receiving procedure are reflected in the variable that the calling procedure passed. If it is passed ByVal, those changes are NOT reflected in the calling procedure's variables.
Code:
Sub aTest()
Dim aString As String
aString = "A"
ByValSub aString
MsgBox "after byVal Sub: " & aString
aString = "A"
ByRefSub aString
MsgBox "after byRef Sub: " & aString
End Sub
Sub ByRefSub(ByRef X As String)
X = X & X
MsgBox "inside value: " & X
End Sub
Sub ByValSub(ByVal X As String)
X = X & X
MsgBox "inside value: " & X
End Sub
This distinction also applies when the argument being passed is an object, but one has to remember that ByRef and ByVal effect
variables not the objects that they represent.
In the following example bValSub and vRefSub make the same changes to the objects (worksheets) that are passed to them, but the variable's that represent those objects are handled differently.
Code:
Sub test2()
Dim aWorksheet As Worksheet
Set aWorksheet = ThisWorkbook.Sheets(1)
Call bValSub(aWorksheet)
MsgBox "after ByVal: " & aWorksheet.Name
Set aWorksheet = ThisWorkbook.Sheets(1)
Call bRefSub(aWorksheet)
MsgBox "after ByRef: " & aWorksheet.Name
End Sub
Sub bValSub(ByVal mySheet As Worksheet)
With ThisWorkbook
Set mySheet = .Sheets((mySheet.Index Mod .Sheets.Count) + 1)
End With
mySheet.Range("A1").Value = mySheet.Range("A1").Value + 1
End Sub
Sub bRefSub(ByRef mySheet As Worksheet)
With ThisWorkbook
Set mySheet = .Sheets((mySheet.Index Mod .Sheets.Count) + 1)
End With
mySheet.Range("A1").Value = mySheet.Range("A1").Value + 1
End Sub
A general rule could be that when an argument is changed by a procedure, that argument should be passed ByVal, unless reading that change is what is desired.
Passing an argument ByRef is one way to get multiple results from one procedure, as in the following.
Code:
Sub testTrig()
Dim Angle As Double, Dist As Double
Dim xVal As Double, yVal As Double
xVal = -3: yVal = 4
Angle = RectToPolarAngle(xVal, yVal, Dist)
MsgBox "Cartesian(" & xVal & ", " & yVal & ") = polar (" & WorksheetFunction.Degrees(Angle) & "°, " & Dist & ")"
End Sub
Function RectToPolarAngle(X As Double, y As Double, Optional ByRef Distance As Double) As Double
RectToPolarAngle = Atn(y / X)
If X < 0 Then RectToPolarAngle = (Sgn(y) * WorksheetFunction.Pi / 2) - RectToPolarAngle
Distance = Sqr(X ^ 2 + y ^ 2)
End Function
Another example of where ByVal is important is recursive routines. The primary argument of a recursive routine should be ByVal, but "pass through" arguments (like Descending and Delimiter) should be ByVal, so that no extra memory is used creating unneeded duplicates of the same value.
Code:
Sub tester()
MsgBox SortedString("bat,c,e,ace,cat", True)
End Sub
Function SortedString(ByVal DelimitedString As String, Optional ByVal Descending As Boolean, Optional ByVal Delimiter As String = ",") As String
Dim Words As Variant
Dim strLeft As String, strPivot As String, strRight As String
Dim i As Long
Words = Split(DelimitedString, Delimiter)
If UBound(Words) < 1 Then
SortedString = DelimitedString
Else
strPivot = Words(0)
For i = 1 To UBound(Words)
If Words(i) < strPivot Xor Descending Then
strLeft = strLeft & Delimiter & Words(i)
Else
strRight = strRight & Delimiter & Words(i)
End If
Next i
strLeft = Mid(strLeft, Len(Delimiter) + 1)
strRight = Mid(strRight, Len(Delimiter) + 1)
If strLeft <> vbNullString Then
strLeft = SortedString(strLeft, Descending:=Descending, Delimiter:=Delimiter)
strPivot = strLeft & Delimiter & strPivot
End If
If strRight <> vbNullString Then
strRight = SortedString(strRight, Descending:=Descending, Delimiter:=Delimiter)
strPivot = strPivot & Delimiter & strRight
End If
SortedString = strPivot
End If
End Function
This thread also mentioned passing arrays to procedures. My Mac handles arrays differently than Windows machines, so I always pass them as Variants.
From what I can tell, the default for all procedures is to pass arguments ByRef. But if it matters, that should be specified. (Excel is going to apply the default anyway, there is no loss by specifying.)
I hope this summary is what was sought by the request.
I almost forgot to mention:
User Defined Classes (in class modules) can be passed either ByRef or ByVal
User defined Types cannot be passed ByVal, they may only be passed ByRef.
Code:
' works
Type MyType
Tag As String
End Type
Sub atest()
Dim aVal As MyType
aVal.Tag = "hello"
Call typeTag(aVal)
End Sub
Sub TypeVal(ByRef aThing As MyType)
MsgBox aThing.Tag
End Sub
Code:
' gives Compile Error
Type MyType
Tag As String
End Type
Sub atest()
Dim aVal As MyType
aVal.Tag = "hello"
Call typeTag(aVal)
End Sub
Sub TypeVal(ByVal aThing As MyType)
MsgBox aThing.Tag
End Sub
