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

Question

I mean to pass a parameter to a VBA Sub, ensuring that it is not modified. I would do that in C as void mysub( const int i );.

What is the recommended (i.e., simplest, most portable, etc.) way of achieving the same in VBA, if any?

The question is about item 3 below. There is an accepted answer, but the question is open to alternatives (there is likely no last word on this).

EDIT Clarification of the equivalence in functionality VBA vs. C, needed as per answers and comments:

1. Passing by reference.
   In VBA, Sub mysub(i as Integer) (the default, or Sub mysub(ByRef i as Integer)), which takes arguments by reference without "asking for permission" from the caller (who uses Call mysub(j)), has no exact equivalent in C.
   In C, the closest would be void mysub(int * i);. But in C one would have to complement this with a call as mysub(&j);, i.e., it is also up to the caller for this to work.
   The Sub can modify the value of "the variable" (i, in VBA; *i, in C) inside the called Sub. If it does, it automatically modifies the value in the caller.

2. Passing by value.
   In VBA, Sub mysub(ByVal i as Integer), which takes arguments by value from the caller (who uses Call mysub(j), without even knowing whether mysub takes ByVal or ByRef), corresponds to
   In C, void mysub(int i);.
   The Sub can modify the value of "the variable" (i, in VBA or C) inside the called Sub. If it does, it does not affect the value in the caller.

3. Passing by value, qualifying with const.
   In C, void mysub(const int i);.
   The Sub cannot modify the value of "the variable" (i, in C) inside the called Sub. Of course, nothing happens to the value in the caller either.
   

Answer 1

Try this:

Sub mysub(ByVal i As Integer)


End Sub

http://msdn.microsoft.com/en-us/library/office/aa164263(v=office.10).aspx

Important Text: When you define a procedure, you have two choices regarding how arguments are passed to it: by reference or by value. When a variable is passed to a procedure by reference, VBA actually passes the variable's address in memory to the procedure, which can then modify it directly. When execution returns to the calling procedure, the variable contains the modified value. When an argument is passed by value, VBA passes a copy of the variable to the procedure. The procedure then modifies the copy, and the original value of the variable remains intact; when execution returns to the calling procedure, the variable contains the same value that it had before being passed..

EDIT:

I now understand you want to prevent the variable being modified in the called sub routine, not the caller sub. Its not possible in the way you suggested. VBA only has byval and byref for passing arguments. You could try something like this (which is not full proof):

In a class module named: ConstInteger:

Private i As Variant
Public Property Get Value() As Integer
    Value = i
End Property
Public Property Let Value(Value As Integer)
    If IsEmpty(i) Then i = Value
End Property

Testing:

Sub Caller()
    Dim clsInt As New ConstInteger
    clsInt.Value = 1
    Call Called(clsInt)
End Sub
Sub Called(clsInt As ConstInteger)
    Debug.Print clsInt.Value
    clsInt.Value = 2
    Debug.Print clsInt.Value
End Sub

Answer 2

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.