Passing arguments to subroutines in VBA
Part two 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
  2. Passing Arguments by Reference and Value (this blog)
  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

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 by Reference and Value

Returning Values using Arguments

In the previous part of this blog, we tested to see whether a worksheet existed or not, and displayed an error message if it didn't.  However, it would usually be more useful to pass the information back to the calling routine:

Sub SelectWorksheet(SheetName As String, IfFound As Boolean)

'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

IfFound = True

Exit Sub

NoSheet:

'if get here, failed to select sheet

IfFound = False

End Sub

In the above routine, IfFound will hold either True or False, depending on whether the worksheet was selected or not.  You can then pick up on this in the procedure which calls SelectWorksheet:

Sub TryWorksheetSelection()

Dim IfWorksheetExists As Boolean

'try selecting a worksheet

SelectWorksheet "Sheet1", IfWorksheetExists

'say whether found or not

If IfWorksheetExists Then

MsgBox "Worksheet found"

Else

MsgBox "No such worksheet"

End If

End Sub

Notice that in this case we call the argument different names in the calling and called routines.  This is perfectly acceptable: it's the data type and position of the argument which matter, not the name.

Passing Arguments by Reference (ByRef)

The reason the code above works is that by default arguments are passed by reference.  What this means is that rather than passing the value of the argument to and fro, the calling and called routines share the location of where the argument is stored in memory.

Imagine you tell a friend that you've hidden his sock in a hole in a wall.  Your friend finds the sock, and swaps it for a rotten fish, which you then discover when you return to the hole in the wall.  This is passing arguments by reference.

You could have written the subroutine declaration using ByRef:

Sub SelectWorksheet(SheetName As String, ByRef IfFound As Boolean)

The reason you don't need to do this is that it is the default behaviour in VBA.

Passing Arguments by Value (ByVal)

The alternative is to pass arguments by value, which means that you are passing a copy of the argument, and not a reference to where it is stored.  This means that your subroutine can make any changes it likes to the argument passed, secure in the knowledge that these changes won't be passed back to the calling routine.

Suppose that you wanted to list out the first 10 square numbers.  Code to do it could be this:

Sub ListSquares()

Dim i As Integer

'show first ten squares

For i = 1 To 10

ShowSquare i

Next i

End Sub

Sub ShowSquare(i As Integer)

'square this number

i = i ^ 2

'display result

Debug.Print i

End Sub

However, this would produce only the following output:

Output of squares

The results of running the ListSquares routine above

 

The reason is that the subroutine is changing the value of the passed argument, and this changed value is being passed back to the calling routine.  If you use ByVal instead, things will work perfectly:

Sub ShowSquare(ByVal i As Integer)

The output will then be what we wanted:

First 10 squares

The results of running the routine with ByVal.

 

Most people, most of the time, can forget about all of this and just use the default ByRef behaviour, but it's nice to know what's going on behind the scenes!

 

  1. Passing Arguments
  2. Passing Arguments by Reference and Value (this blog)
  3. Optional Arguments and ParamArrays
This blog has 0 threads Add post