VBA Squeeze Box
I discussed previously the differences between passing by value and passing by reference. I now want to discuss how and when I use each in my own code.
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.
- Pass most variables implicitly by reference.
- Pass in/out variables explicitly by reference (via
- Windows API calls (
Declarestatements) often require
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
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.
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