VBA Squeeze Box

Just like mama in The Who's famous song, procedure arguments in VBA can go in and out and in and out and in and out...

In a previous post, I discussed the differences between passing by value and passing by reference.  With the explanation out of the way, I now want to discuss how and when I use each in my own code.

1. Pass most variables implicitly by reference.
2. Pass in/out variables explicitly by reference (via ByRef).
3. Windows API calls (Declare statements) often require ByVal.

Use the default mode...by default

In general, I pass all my variables by reference without explicitly stating that in my procedure declaration.  For example:

'>>> Condense("Long line of text.", 8)
'Long lin...
Function Condense(Text As String, MaxChars As Long, _
                  Optional ContinuationIndicator As String = "...") As String
    If Len(Text) <= MaxChars Then
        Condense = Text
    Else
        Condense = Left(Text, MaxChars) & ContinuationIndicator
    End If
End Function

Note that I don't use the ByVal or ByRef keywords at all in the above code.  Note also that I do not assign values to any of my function arguments within the body of the function.  Otherwise, I would be changing the value of the variable in the calling procedure.  

This is a critical concept to understand when writing VBA.  If you're not aware of it, you can slowly introduce a bunch of hard-to-diagnose bugs within your programs.

Use ByRef for "in/out" or "out" arguments

Some programming languages--like Ada--have syntactic support to specify whether an argument is an input, an output, or both.  Fun fact: most of my college programming courses were in Ada.  

I borrowed this concept for VBA.  There is no out keyword in VBA.  However, I have created my own de facto out keyword by only using ByRef when I intend to change the value of the argument outside of the procedure.

To be clear, the ByRef keyword is completely optional.  The program will run exactly the same whether the keyword is included or not.  But, we should always write code that is easy for humans to understand.  Using ByRef only in these situations is a concise way to convey important information about how we expect our program to behave.

Windows API calls

When accessing API calls via the Declare statement, most declarations require explicit declaration of arguments as ByVal.  For example, here is the useful Sleep API call declaration:

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Here are a few good resources for incorporating API calls into your VBA code:

• How to use Windows API in VBA: (includes link to Win32API_PtrSafe.txt)
• How to convert Windows API declarations in VBA for 64-bit
• AllAPI.net: an oldie but goodie; lots of great examples but will need to be converted for 64-bit compatibility (see above link)

Image by Rumberger_sound_products from Pixabay