What can you learn here?
- Argument passing
- ByREF versus byVAL
- Optional and variable arguments
Argument passing
Nowadays passing arguments to procedures is intuitive and the mechanics of how that happens is mainly the concern of compiler writers and serious geeks. In the old days it wasn’t always that way and even today, VBA developers still need to be aware of how data is passed to procedures.
Explicit Typing
Being able to send values that are used as parameters to affect the operation of sub or function is a fundamental requirement for any language. The underlying mechanism for how this is handled has barely changed from the early days of programming, but the ease of doing it has changed dramatically. Strongly ‘typing’ arguments is always best practice, although VBA allows you to not do this. Consider this Function proc (x) proc= x*2 end function versus Option Explicit Function proc (x as Long) as Long proc = x*2 End Function
Since VBA allows a variant type of variable (which takes the characteristic of whatever is populating it), and further, allows you to reference variables that are not declared (which become type variant), you don’t need to be explicit while coding. However this kind of programming just leads to errors that you can’t track down, and is just plain lazy. You should only use a variant when it’s necessary (sometimes it is), you should declare everything, and the first line in every module should be Option Explicit. It will pay back hundreds of times over.
Passing arguments to procedures
Assuming that everything is explicitly typed, lets look at argument passing. Firstly there is no connection between the name of the variable in the sending and receiving procedure. The sequence of arguments is what matters. Option Explicit . . dim z as long, y as long z=proc (y, 2) . . Function proc (x as Long, n as long) as Long proc = x*n End Function
Optional arguments
It is possible to provide a default value to a function by calling an argument optional, and giving it a default value in the receiving procedure Option Explicit . . dim z as long, y as long z=proc (y) . . Function proc (x as Long, optional n as long = 5) as Long proc = x*n End Function
Testing if optional argument was specified
There is a function isMissing() that can test if an optional argument was specified, but it only works with variant argument types, so the following will return -1. Sub testArgs() Dim z As Long z = proc(20) MsgBox (z) End Sub Function proc(x As Long, Optional v As Variant) As Long If IsMissing(v) Then proc = -1 Else proc = x End If End Function If you provide a default value, even with a variant, the argument is assumed to be not missing, so isMissing(v) will return false in the following case Function proc(x As Long, Optional v As Variant = "rubbish") As Long In the call string, once you start talking about optional arguments, every argument following also needs to be optional so this is ok Function proc(x As Long, Optional v As Variant = "rubbish", Optional z As Long = 1) As Long but this is not, and won’t be tolerated by the IDE Function proc(x As Long, Optional v As Variant = "rubbish", z As Long = 1) As Long
The Call Stack
Since time immemorial, arguments have been passed to procedures using a ‘call stack’. If you are not familiar with the concept of a stack, look here. Space is allocated for each argument, in strict last in first out order, in a special area of memory called the call stack, along with the address of the procedure that is doing the calling. The called procedure then has the responsibility as it exits to get its arguments off the stack, move the stackpointer back as if the call to it had never happened, and return control to the caller. In the early days of programming it was the programmer’s responsibility to do all this, but of course this is now all handled by the compiler or interpreter to make sure this all happens. This clear responsibility stack clean up is the basis of being able to nest calls to procedures. Generally you don’t have to worry about any of that, but different programming languages have different approaches to what they put on the stack depending on the type of argument that is being referenced. For example, C will put the value of an integer on the stack, whereas VB will put its address. That means that if you are calling non-VBA procedures (for example calls to the System API), you cannot rely on VBA to treat what’s on the stack in the correct way. There are also occasions that you might want to vary normal VBA operation, so lets look at that next.
ByRef versus ByVal
ByRef means that procedure is expecting that the ‘address of’ an argument will be on the call stack. This is sometimes also referred to as ‘pointer to’. ByVal on the other hand, means that the actual value of the argument will be on the call stack. The fundamental difference here is that with ByRef you know the variable’s location in the caller (and could therefore change its contents back there), whereas with ByVal you can only change the contents of the copy of the variable on the call stack, which will anyway be discarded on exit of the procedure. Consider the following code. (If you don’t understand classes, you may want to go and read this first) Sub testcallStack() Dim s As String, c As cMyClass, n As Long Set c = New cMyClass c.Init 1, "start" s = "start" n = 100 ' default... Debug.Print "Initial :"; s, c.Key, n callStackDefault s, c, n Debug.Print "After :"; s, c.Key, n ' by value callStackValue s, c, n Debug.Print "After :"; s, c.Key, n ' by reference callStackRef s, c, n Debug.Print "After :"; s, c.Key, n End Sub Sub callStackDefault(s As String, c As cMyClass, n As Long) s = "default" c.Key = 200 n = 200 End Sub Sub callStackValue(ByVal s As String, ByVal c As cMyClass, ByVal n As Long) s = "byvalue" c.Key = 300 n = 300 End Sub Sub callStackRef(ByRef s As String, ByRef c As cMyClass, ByRef n As Long) s = "byRef" c.Key = 400 n = 400 End Sub This produces the following output Initial :start 1 100 After :default 200 200 After :default 300 200 After :byRef 400 400 You can see that each of the 3 variations of procedures attempts to change the contents of its argument, so by looking at the values after it returns to the main procedure, we can see if changes made in the called procedure affects the contents of the variables in the caller. ByRef Call After calling callStackRef(ByRef s As String, ByRef c As cMyClass, ByRef n As Long), we can see that the values of s, c and n have all changed in the main procedure. This means that by default, VBA puts the address of each of the types tested on the call stack as this is the only way that the called procedure would be able to access the address of these variables in their original location. ByVal Call After calling callStackValue(ByVal s As String, ByVal c As cMyClass, ByVal n As Long), we can see that s and n have not changed but c.Key has. At first glance this looks odd. Why have s and n behaved as expected, but c has not? The reason is that the object c itself contains ‘addresses of’ its properties. In this case, putting the address of c, or the values of c makes no difference, since we are using the contents of c as further pointers to memory locations. Let’s prove that ByRef and ByVal call are different even for objects. Setting c to Nothing has no effect when called by value – that’s because we are setting the value on the stack to nothing, which i going to be discarded anyway. Sub callStackValue(ByVal s As String, ByVal c As cMyClass, ByVal n As Long) Set c = Nothing End Sub However doing the same thing when c was passed by reference causes a fatal error, since we have destroyed the original object in the caller. Sub callStackRef(ByRef s As String, ByRef c As cMyClass, ByRef n As Long) Set c = Nothing End Sub
Default Call
After calling callStackDefault(s As String, c As cMyClass, n As Long), we can see that the values of s, c and n have all changed in the main procedure. This means that by default, for these types, VBA uses ByRef by default.
Returning multiple results from functions
When the C language started to become popular all those years ago, programming habits changed. Passing arguments ByVal became the norm, and functions would be short and sweet, expecting only to return a single answer. Prior to that, ByRef was the norm, and procedures would be expected to modify the contents of their arguments – even though we recognize now that this is very bad practice. Functions can only return one answer in VBA, by assigning a value to the function name Option Explicit Function proc (x as Long) as Long proc = x*2 End Function However, we have earlier established that by default, VBA is passing arguments ByRef and we could do this if necessary Option Explicit Sub testprocref() Dim x As Long, y As Long x = 10 procref x, y Debug.Print x, y End Sub Sub procref(x As Long, y As Long) x = x * 2 y = x * 4 End Sub and we’d get this result as expected 20 80 But someone looking at the call to procref wouldn’t easily realize that x and y were going to be somehow modified . Especially if procref was part of some other library. If you must do this kind of thing, then at the very least be explicit in the definition of the procedure so that it is clear that the procedure is ‘out of the ordinary. Sub procref(ByRef x As Long, ByRef y As Long) x = x * 2 y = x * 4 End Sub
Using TypeDef to return multiple arguments
Another way of returning multiple arguments from a function is to use a class that has properties for each of the values that the procedure touches, but that could be overkill. A simpler way might be to define your own type using TypeDef. The example below defines a new type with 2 ‘properties’ Type tCursor left As Long top As Long End Type and can be used like this Sub testTypeDef() Dim mPos As tCursor mPos = getMpos Debug.Print mPos.left, mPos.topEnd SubFunction getMpos() As tCursor Dim mp As tCursor mp.left = 100 mp.top = 200 getMpos = mpEnd Function and will return 100 200
Variable number of arguments
Sometimes you don’t know how many arguments there will be to a procedure. This is where ParamArray comes in. The below shows how to use it Sub testmakeString() Debug.Print makeString("the result is ", "the", "comma", "test") End Sub Function makeString(a As String, ParamArray args() As Variant) As String Dim s As String, v As Variant s = "" For Each v In args s = s & CStr(v) & "," Next v If Len(s) > 0 Then s = left(s, Len(s) - 1) makeString = a & "(" & s & ")" End Function and the result the result is(the,comma,test)
Using scriptcontrol and argument passing
This is kind of an advanced topic – passing arguments between VBA and javaScript
Summary
You can see from the above that there are many things to learn around argument passing, typing and leveraging different call types. To re-iterate – keep away from using ByRef arguments to change values in calling procedures, always use Option Explicit and type everything as strongly as you can. That way you will build solid, reusable procedures that can be understood by you later on.In more advanced sections I will deal with other topics such as Array passing. All code mentioned here is available here and you may leave comments or questions in our forum.