VBA/Excel/Access/Word/Language Basics/ByVal ByRef

ByVal and ByRef

 
Private Sub Main()
    Dim num1 As Integer
    Dim num2 As Integer
    num1 = 10
    num2 = 15
    Call passByRef(num1)
    Call passByVal(num2)
    MsgBox (num1 & "  " & num2)
End Sub
Private Sub passByRef(ByRef num3 As Integer)
    num3 = 20
End Sub
Private Sub passByVal(ByVal num2 As Integer)
    num2 = 20
End Sub

Creating Your Own VBA Functions: ByVal

 
Public Function PowerDB(ByVal number As Double, n As Single) As Double
    number = number ^ n
    PowerDB = number
End Function
Private Sub testPower()
    Dim number As Double
    Dim n As Single
    Dim result As Double
    number = 2
    n = 3
    result = PowerDB(number, n)
    MsgBox (number & "^" & n & " = " & result)
End Sub

Passing Arguments with ByVal and ByRef

 
Private Sub Main()
    Dim num1 As Integer
    Dim num2 As Integer
    num1 = 10
    num2 = 20
    Call PassByValue(num1)
    Call PassByReference(num2)
    MsgBox num1 & " " & num2
End Sub
Private Sub PassByValue(ByVal num1 As Integer)
    num1 = num1 ^ 2
End Sub
Private Sub PassByReference(num3 As Integer)
    num3 = num3 ^ 2
End Sub

Pass variable by value

 
Sub CubeRoot(ByVal dblNumber As Double)
    dblNumber = dblNumber ^ (1 / 3)
End Sub
Sub CubeRootWrapper()
    Dim dblVariable As Double
    dblVariable = 8
    Debug.Print "Before: " & dblVariable
    CubeRoot dblVariable
    Debug.Print "After: " & dblVariable
End Sub