557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Passing arguments to subroutines in VBA
Part one of a three-part series of blogs
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.
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.
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|
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)|
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.
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.
|Parts of this blog|
25 Aytoun Street