VBA: const parameter for a Sub - avoiding modification of the value of an argument passed by value

arguments, constants, excel, vba

Solution

VBA is not designed to prevent parameters from being modified inside of a function. If you want to do that, you have to implement those protections yourself as a workaround. Below are two suggestions of how one might implement const protections in VBA.

The OP's final solution can be found here.

Protecting Parameters with Let and Get

Here is an example of a way one might use to prevent a function from modifying the member variables of a class using Let and Get Properties.

Create a class clsPerson with the following code:

Option Explicit

Private m_strName As String
Private m_intAge As Integer
Private m_strAddress As String

Public LockMemberVariables As Boolean

Public Property Get Name() As String
    Name = m_strName
End Property

Public Property Let Name(strName As String)
    If Not LockMemberVariables Then
        m_strName = strName
    Else
        Err.Raise vbObjectError + 1, , "Member variables are locked!"
    End If
End Property

Public Property Get Age() As Integer
    Age = m_intAge
End Property

Public Property Let Age(intAge As Integer)
    If Not LockMemberVariables Then
        m_intAge = intAge
    Else
        Err.Raise vbObjectError + 1, , "Member variables are locked!"
    End If
End Property

Public Property Get Address() As String
    Address = m_strAddress
End Property

Public Property Let Address(strAddress As String)
    If Not LockMemberVariables Then
        m_strAddress = strAddress
    Else
        Err.Raise vbObjectError + 1, , "Member variables are locked!"
    End If
End Property

Create a normal code module with these functions:

Option Explicit

Public Sub Main()
    Dim Bob As clsPerson
    Set Bob = New clsPerson

    Bob.Name = "Bob"
    Bob.Age = 30
    Bob.Address = "1234 Anwhere Street"

    Bob.LockMemberVariables = True

    PrintPerson Bob
    AlterPerson Bob

End Sub

Public Sub PrintPerson(p As clsPerson)
    MsgBox "Name: " & p.Name & vbCrLf & _
        "Age: " & p.Age & vbCrLf & _
        "Address: " & p.Address & vbCrLf
End Sub

Public Sub AlterPerson(p As clsPerson)
    p.Name = "Jim"
End Sub

Run the `Main()` function to test the clsPerson class. This is a state-based runtime method to prevent a class' members from being modified that can be turned on and off. Each Property Let in clsPerson checks to see if LockMemberVariables is true, and if so throws an error when an attempt is made to change its value. If you were to modify the code so that `Bob.LockMemberVariables = False` and then call AlterPerson, it would be able to modify the Name with no errors.

Protecting Parameters with an Interface

The other way you might implement protection is through interfaces. In fact, you might prefer this since it works at compile-time. Suppose you create an interface (class) called IProtectedPerson which only supports Get for the member variables:

Public Property Get Name() As String

End Property

Public Property Get Age() As Integer

End Property

Public Property Get Address() As String

End Property

Then you have your clsPerson class rewritten so that it has normal Let and Get, but also implements the IProtectedPerson interface:

Option Explicit

Implements IProtectedPerson

Private m_strName As String
Private m_intAge As Integer
Private m_strAddress As String

Public Property Get Name() As String
    Name = m_strName
End Property

Public Property Let Name(strName As String)
    m_strName = strName
End Property

Public Property Get Age() As Integer
    Age = m_intAge
End Property

Public Property Let Age(intAge As Integer)
    m_intAge = intAge
End Property

Public Property Get Address() As String
    Address = m_strAddress
End Property

Public Property Let Address(strAddress As String)
    m_strAddress = strAddress
End Property

'IProtectedPerson Interface Implementation

Private Property Get IProtectedPerson_Name() As String
    IProtectedPerson_Name = Me.Name
End Property

Private Property Get IProtectedPerson_Age() As Integer
    IProtectedPerson_Age = Me.Age
End Property

Private Property Get IProtectedPerson_Address() As String
    IProtectedPerson_Address = Me.Address
End Property

You would then create functions which take an IProtectedPerson as a parameter. The code will fail to compile if you try to make an assignment to the private member variables because the Let functions aren't available on that interface:

Option Explicit

Public Sub Main()
    Dim Bob As clsPerson
    Set Bob = New clsPerson

    Bob.Name = "Bob"
    Bob.Age = 30
    Bob.Address = "1234 Anwhere Street"

    AlterPerson Bob
    AlterProtectedPerson Bob
End Sub

Public Sub AlterPerson(p As clsPerson)
    p.Name = "Jim"
End Sub

Public Sub AlterProtectedPerson(p As IProtectedPerson)
    p.Name = "Sally"
End Sub

The code fails to compile because p in the `AlterProtectedPerson` function does not have a Property Let for Name.

Problem

Original source