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.

  1. Passing Arguments (this blog)
  2. Passing Arguments by Reference and Value
  3. 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 | 1 comment

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:

Build a snowman

Here's what this might give you:

Basic snowman

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
Snowman with face

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
Snowman with hat and arms

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:

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:

Snowman question message box

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:

MsgBox - full syntax

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:

Error message if no sheet

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.

 

This blog has 1 comment

Comment added on 20 February 2013 at 23:53 GMT
Hello,

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.

Regards,

Dave
Reply from Andy Brown
I have to say I haven't used them often!

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.