Passing Arguments
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 blog 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:
Build a snowman
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.
