What can you learn here?

  • Argument passing
  • ByREF versus byVAL
  • Optional and variable arguments


What can you learn here

Public versus Private
Variable visibility
External references


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.top
End Sub
Function getMpos() As tCursor
    Dim mp As tCursor
    mp.left = 100
    mp.top = 200
    getMpos = mp
End 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



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.