One of the biggest keys to writing robust, flexible, powerful code in VBA is to write separate subroutines, and pass arguments (bits of information) to them. This tutorial explains the many ways to do this.
How Arguments Work
To understand how arguments work, consider the humble snowman (or snowwoman ...). Let's start with a basic instruction:
Here's what this might give you:
At its most basic, we'll assume that a snowman consists of a base, a middle and a head.
However, you can customise your snowman a bit. For example, you could try:
Build a snowman with a smiley face and buttons
A slightly more ambitious snowman - this has buttons and a face.
Or you could really go to town:
Build a snowman with hat, face, arms, tie and buttons
This snowman has a top hat, bow tie, face, buttons and arms.
Thus by tweaking the parameters of the build snowman command, we can get a wide range of output snowmen. These parameters are called arguments, and here they include:
Argument | Value for bottom snowman | Status |
|---|
NumberBodyParts | 3 | Compulsory |
FacialExpression | Smiley | Optional |
IfHat | True | Optional |
IfBowTie | True | Optional |
Buttons | 4 | Optional |
ArmType | Twigs | Optional |
TieType | Bow | Optional |
TieColour | Red | Optional |
Visual Basic for Applications Arguments
How does all of this relate to computer programming languages? Well, consider the command to display a message box on screen. At its most basic, this just displays a basic message:
Sub DisplayMessage()
MsgBox "What's a snowman?"
End Sub
The above subroutine would display a vanilla (basic) message box:
Because there are no arguments supplied, the title defaults to Microsoft Excel.
However, just like for the snowman instruction, we could add additional information to the message box command to tweak it. For example, the following subroutine would display two identical messages like this:
We'll change the title, buttons and icon for the message box.
Here's the subroutine to display this message box (twice):
Sub SnowmanQuestion()
MsgBox _
"Do you know what a snowman is?", _
vbYesNo + vbQuestion, _
"Snowman identification"
MsgBox _
prompt:="Do you know what a snowman is?", _
Buttons:=vbYesNo + vbQuestion, _
Title:="Snowman identification"
End Sub
In fact, the MsgBox command takes 5 possible arguments, all but the first of which is optional:
The first argument is compulsory - it doesn't have square brackets round it.
The 5 arguments are:
Argument | Notes |
|---|
Prompt | The message to be displayed |
Buttons | The combination of buttons to display |
Title | The title for the dialog box |
HelpFile | The help file to be displayed (not often used) |
Context | The importance of the help file (not often used) |
Creating your Own Arguments
Given all of this, the next step is to create your own subroutines, and pass in your own arguments. As an example, suppose that you want to write a subroutine which takes you to a particular worksheet (or displays an error if there isn't one in the active workbook). Here's what it could look like:
Sub SelectWorksheet(SheetName As String)
On Error GoTo NoSheet
Worksheets(SheetName).Select
On Error GoTo 0
Exit Sub
NoSheet:
MsgBox "Worksheet " & UCase(SheetName) & " not found"
End Sub
In this case the routine takes a single argument, which has to be a string of text (in fact, it's the name of the worksheet you want to select).
The number and type of arguments to a procedure is sometimes called its signature - so in the above example, the signature of the SelectWorksheet routine is a single argument of type String.
You could try running your procedure as shown above like this:
Sub Test()
SelectWorksheet "Silly sheet name"
End Sub
However, if the worksheet with this name doesn't exist (highly likely), you'll see an error message:
The error message displayed within the error trapping of your procedure.
An Example with Two Arguments - Moving Cell
Many people can't remember whether you move down then across, or vice versa, with the Offset method. You could write your own method to override the default behaviour:
Sub MoveCell(NumberCellsRight As Integer, NumberCellsDown As Integer)
ActiveCell.Offset( _
NumberCellsDown, _
NumberCellsRight).Select
End Sub
Sub MoveInCircle()
MoveCell 1, 0
MoveCell 0, 1
MoveCell -1, 0
MoveCell 0, -1
End Sub
In the above example, we've created two arguments for the MoveCell procedure:
Argument name | Type |
|---|
NumberCellsRight | Integer |
NumberCellsDown | Integer |
Now that you've learnt how to tweak a subroutine so that it does different things for different input arguments, it's time to look at other goodies, such as passing arguments by reference and by value, optional arguments and using the weird and wonderful ParamArray.
Returning Values using Arguments
In the previous part of this tutorial, we tested to see whether a worksheet existed or not, and displayed an error message if it didn't. However, it would usually be more useful to pass the information back to the calling routine:
Sub SelectWorksheet(SheetName As String, IfFound As Boolean)
On Error GoTo NoSheet
Worksheets(SheetName).Select
On Error GoTo 0
IfFound = True
Exit Sub
NoSheet:
IfFound = False
End Sub
In the above routine, IfFound will hold either True or False, depending on whether the worksheet was selected or not. You can then pick up on this in the procedure which calls SelectWorksheet:
Sub TryWorksheetSelection()
Dim IfWorksheetExists As Boolean
SelectWorksheet "Sheet1", IfWorksheetExists
If IfWorksheetExists Then
MsgBox "Worksheet found"
Else
MsgBox "No such worksheet"
End If
End Sub
Notice that in this case we call the argument different names in the calling and called routines. This is perfectly acceptable: it's the data type and position of the argument which matter, not the name.
Passing Arguments by Reference (ByRef)
The reason the code above works is that by default arguments are passed by reference. What this means is that rather than passing the value of the argument to and fro, the calling and called routines share the location of where the argument is stored in memory.
Imagine you tell a friend that you've hidden his sock in a hole in a wall. Your friend finds the sock, and swaps it for a rotten fish, which you then discover when you return to the hole in the wall. This is passing arguments by reference.
You could have written the subroutine declaration using ByRef:
Sub SelectWorksheet(SheetName As String, ByRef IfFound As Boolean)
The reason you don't need to do this is that it is the default behaviour in VBA.
Passing Arguments by Value (ByVal)
The alternative is to pass arguments by value, which means that you are passing a copy of the argument, and not a reference to where it is stored. This means that your subroutine can make any changes it likes to the argument passed, secure in the knowledge that these changes won't be passed back to the calling routine.
Suppose that you wanted to list out the first 10 square numbers. Code to do it could be this:
Sub ListSquares()
Dim i As Integer
For i = 1 To 10
ShowSquare i
Next i
End Sub
Sub ShowSquare(i As Integer)
i = i ^ 2
Debug.Print i
End Sub
However, this would produce only the following output:
The results of running the ListSquares routine above
The reason is that the subroutine is changing the value of the passed argument, and this changed value is being passed back to the calling routine. If you use ByVal instead, things will work perfectly:
Sub ShowSquare(ByVal i As Integer)
The output will then be what we wanted:
The results of running the routine with ByVal.
Most people, most of the time, can forget about all of this and just use the default ByRef behaviour, but it's nice to know what's going on behind the scenes!
Optional Arguments
In our BuildSnowman routine at the start of this tutorial, sometimes we wanted buttons and a bow tie, and sometimes we didn't. The way to get round this is to make arguments optional.
Suppose we want to be able to construct an address (perhaps for use in a mail merge, although this isn't shown here), and we don't know how many lines it will have. Here are 2 possible addresses we might have:
 |  |
One address line only | A full postal address |
A routine which would display an address whether it has one line, two lines or two lines and a postcode is shown below:
Sub CreateAddress( _
PersonName As String, _
AddressLine1 As String, _
Optional AddressLine2 As String = "", _
Optional PostCode As String = "")
Dim FullAddress As String
FullAddress = PersonName & vbCrLf & AddressLine1
If Len(AddressLine2) > 0 Then _
FullAddress = FullAddress & vbCrLf & AddressLine2
If Len(PostCode) > 0 Then _
FullAddress = FullAddress & vbCrLf & PostCode
MsgBox FullAddress
End Sub
By making the last two arguments optional you can miss them out (in which case they'll take the default value shown). The syntax of an optional argument is thus:
Optional ArgumentName as ArgumentType = DefaultValue
Here's how you could call the CreateAddress above with different argument combinations:
Sub TestAddresses()
CreateAddress "Bob the Builder", "Sunflower Valley"
CreateAddress "Tinky-Winky", "1 The Avenue", "Teletubbieland"
CreateAddress "William Pitt", "10 Downing Street", "London", "SW1"
End Sub
Optional Argument Syntax
The main rule of optional arguments is that they should come at the end of the list.:
This code shows an error because the optional arguments aren't at the end of the list
If you think hard about it, you'll see why: otherwise, how would VBA know which value to assign to which argument?
Using ParamArray
In our address example above, we could have addresses with anything from 0 to 6 lines. Rather than having to list 6 optional arguments, we could create an optional array, called a ParamArray. The easiest way to explain this is by example:
Sub CreateAddress( _
PersonName As String, _
ParamArray AddressLines() As Variant)
Dim FullAddress As String
FullAddress = PersonName
Dim AddressLine As Variant
For Each AddressLine In AddressLines
FullAddress = FullAddress & vbCrLf & AddressLine
Next AddressLine
MsgBox FullAddress
End Sub
You could then call this CreateAddress routine with any number of arguments you liked (including none at all):
Sub TestAddresses()
CreateAddress "Wise Owl"
CreateAddress "Bob the Builder", "Sunflower Valley"
CreateAddress "Tinky-Winky", "1 The Avenue", "Teletubbieland"
CreateAddress "William Pitt", "10 Downing Street", "London", "SW1"
End Sub
The main thing to notice about using ParamArray is that both the array and the variable defined to loop over it must be dimensioned with type Variant.
