BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
To become an efficient programmer, you need to stop writing one long subroutine and start writing lots of small ones instead - and to do this, you're going to need to know everything there is to know about passing arguments.
- Passing Arguments (this blog)
- Passing Arguments by Reference and Value
- Optional Arguments and ParamArrays
You can find more articles on this subject in our Excel Visual Basic tutorial blog - or book onto one of our Excel or VBA training courses.
Posted by Andy Brown on 09 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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:
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:
A slightly more ambitious snowman - this has buttons and a face.
Or you could really go to town:
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()
'desert-dweller's message
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()
'two different ways to ask the same question
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)
'ARGUMENTS
'=========
'SheetName The name of the worksheet to select
'trap any error
On Error GoTo NoSheet
'try to go to sheet
Worksheets(SheetName).Select
'if get here, reset error trap and exit
On Error GoTo 0
Exit Sub
NoSheet:
'if get here, failed to select sheet
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()
'try selecting a worksheet
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)
'ARGUMENTS
'=========
'NumberCellsRight The number of cells to go right
'NumberCellsDown The number of cells to go down
'move the required number of cells down/right
ActiveCell.Offset( _
NumberCellsDown, _
NumberCellsRight).Select
End Sub
Sub MoveInCircle()
'move round in a circle
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.
- Passing Arguments (this blog)
- Passing Arguments by Reference and Value
- Optional Arguments and ParamArrays