Is there a VBA equivalent (or way to replicate) passing parameters as 'Out' like C#?

Question

I generally use VBA but have been reading up on programming techniques in The C# Programming Yellow Book which, obviously, is more specific to C#. Anyway, it mentions a technique of passing parameters using the Out keyword.

I already know that VBA supports byVal and byRef and am fairly certain there is no direct equivalent for Out. Passing parameters using Out is subtly different to passing parameters by Ref.

This Answer https://stackoverflow.com/a/388781/3451115 seems to give a good explanation of the difference between Out & Ref.

The Ref modifier means that:

The value is already set and The method can read and modify it.

The Out modifier means that:

The Value isn't set and can't be read by the method until it is set. The method must set it before returning.

In the code base that I've inherited there are several places where values are assigned to variables using methods that accept parameters byRef. It seems to me that while passing byRef does the job, passing by Out would be safer... So (and here is the question) is there a way of safely / reliably replicating Out in VBA?

In my first iteration (original question) I imagined that the code would have a pattern like:

Sub byOutExample(byRef foo As String)
    ' Check before running code:
    ' foo must = vbNullString
    If foo <> vbNullString then Err.Raise(someError)

    ' Do Something to assign foo
    foo = someString

    ' Check before exiting:
    ' foo must <> vbNullString 
    If foo = vbNullString then Err.Raise(someError)
End Sub

Other considerations: is it worth doing, is there a better way, what could go wrong?

Edit: I noticed in the comments for the above definition of Ref vs Out that the passed parameter need not be null, nothing, empty etc. it can be preassigned - the main criteria seems that it is re-assigned.

In light of @ThunderFrame's answer below and the comment that a parameter passed by Out can be pre-assigned (and used), perhaps the following is a better approach:

 Sub byOutExample(ByRef foo As String)

    Dim barTemp As String
    barTemp = foo

    ' Do Something to assign a new value to foo (via barTemp)
    barTemp = someString

    ' Must assign new variable
    foo = barTemp

End Sub

In which case would it be true to say that, as long as foo only appears in the 2 locations shown above, the above code is an accurate way to replicate passing a parameter by Out in VBA?

Answer 1

You can pseudo enforce an out type parameter in VBA by passing it in ByRef, and then checking that it is Nothing (or the default value for a value type) before continuing, much as you have done with the String in your example.

I wouldn't impose the exit condition - sometimes an empty string is a perfectly valid return value, as is a Nothing reference.

Answer 2

The answer is unequivocally 'no' you cannot replicate the C# out parameter modifier in VBA. From https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/out-parameter-modifier:

Variables passed as out arguments do not have to be initialized before being passed in a method call. However, the called method is required to assign a value before the method returns.

These aspects simply don't exist in VBA. All variables in VBA are initialised with default values, ie the concept of an unitialised variable does not exist in VBA, so point 1 isn't possible; and the compiler cannot object if a specified parameter has not had a value assigned within the procedure, so point 2 isn't possible either.

Even the coding patterns in your example would rely on the Do Something to assign foo not to resolve to the relevant data type's default value (which is obviously not the same as being unitialised). The following, for example, would wrongly throw an error:

Public Sub Main()
    Dim income As Long, costs As Long
    Dim result As Long

    income = 1000
    costs = 500
    ProcessSpend income, costs, result

End Sub

Private Sub ProcessSpend(income As Long, costs As Long, ByRef outValue As Long)
    Const TAX_RATE As Long = 2
    Dim netCosts As Long
    Dim vbDefaultValue As Long

    netCosts = costs * TAX_RATE
    outValue = income - netCosts

    If outValue = vbDefaultValue Then Err.Raise 5, , "Unassigned value"
End Sub

So we're really left with the question of is there a way of getting close to the characteristics of out in VBA?

1. Unitialised variables: the closest I can think of are a Variant or Object type which by default initialise to Empty and Nothing respectively.
2. Assign value within the procedure: the simplest way would be to test if the address of the assigning procedure matches your desired procedure address.

It's all leaning towards a helper class:

Option Explicit

Private mNumber As Long
Private mTargetProc As LongPtr
Private mAssignedInProc As Boolean
Public Sub SetTargetProc(targetProc As LongPtr)
    mTargetProc = targetProc
End Sub
Public Sub SetNumber(currentProc As LongPtr, val As Long)
    mAssignedInProc = (currentProc = mTargetProc)
    mNumber = val
End Sub
Public Property Get Number() As Long
    If mAssignedInProc Then
        Number = mNumber
    Else
        Err.Raise 5, , "Unassigned value"
    End If
End Property

And then the previous example would look like this:

Public Sub Main()
    Dim income As Long, costs As Long
    Dim result As clsOut

    income = 1000
    costs = 500
    ProcessSpend income, costs, result

    Debug.Print result.Number
End Sub

Private Sub ProcessSpend(income As Long, costs As Long, outValue As clsOut)
    Const TAX_RATE As Long = 2
    Dim netCosts As Long

    If outValue Is Nothing Then
        Set outValue = New clsOut
    End If

    outValue.SetTargetProc AddressOf ProcessSpend
    netCosts = costs * TAX_RATE
    outValue.SetNumber AddressOf ProcessSpend, income - netCosts
End Sub

But that's all getting very onerous... and it really feels as if we are trying to force another language's syntax onto VBA. Stepping back a little from the out characteristics and developing in a syntax for which VBA was designed, then a function which returns a Variant seems the most obvious way to go. You could test if you forgot to set the 'out' value by checking if the function returns an Empty variant (which suits point 1 and 2 of the out characteristics):

Public Sub Main()
    Dim income As Long, costs As Long
    Dim result As Variant

    income = 1000
    costs = 500
    result = ProcessedSpend(income, costs)
    If IsEmpty(result) Then Err.Raise 5, , "Unassigned value"

End Sub

Private Function ProcessedSpend(income As Long, costs As Long) As Variant
    Const TAX_RATE As Long = 2
    Dim netCosts As Long

    netCosts = costs * TAX_RATE
    'Comment out the line below to throw the unassigned error
    ProcessedSpend = income - netCosts
End Function

And if you wanted the option of passing in a pre-assigned value, then could just define an optional argument as a parameter to the function.