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
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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. |
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 |
---|---|---|
NumberBodyParts | 3 | Compulsory |
FacialExpression | Smiley | Optional |
IfHat | True | Optional |
IfBowTie | True | Optional |
Buttons | 4 | Optional |
ArmType | Twigs | Optional |
TieType | Bow | Optional |
TieColour | Red | Optional |
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) |
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.
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.