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
Posted by Andy Brown on 09 February 2012 | 1 comment
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|
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:
MsgBox "What's a snowman?"
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):
'two different ways to ask the same question
"Do you know what a snowman is?", _
vbYesNo + vbQuestion, _
prompt:="Do you know what a snowman is?", _
Buttons:=vbYesNo + vbQuestion, _
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:
|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)
'SheetName The name of the worksheet to select
'trap any error
On Error GoTo NoSheet
'try to go to sheet
'if get here, reset error trap and exit
On Error GoTo 0
'if get here, failed to select sheet
MsgBox "Worksheet " & UCase(SheetName) & " not found"
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:
'try selecting a worksheet
SelectWorksheet "Silly sheet name"
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)
'NumberCellsRight The number of cells to go right
'NumberCellsDown The number of cells to go down
'move the required number of cells down/right
'move round in a circle
MoveCell 1, 0
MoveCell 0, 1
MoveCell -1, 0
MoveCell 0, -1
In the above example, we've created two arguments for the MoveCell procedure:
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.
This blog has 1 comment
Just came across your site, great tutorials! I've been programming in VBA for a while now, and am embarrassed to admit that I've never used ParamArrays before. I just had a quick look through my selection of (excellent) VBA reference books to see what they had to say about ParamArrays. Much to my surprise, the only example I found was used in such simplistic way, that I couldn't see the point of it. Mostly, it was not mentioned at all. But thanks to your instructive code, I can now add them to my list of possibilities for future projects.
A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.