This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

Passing Arguments
Passing Arguments by Reference and Value (this blog)
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

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:

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:

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!

Parts of this blog
Passing Arguments Passing Arguments by Reference and Value (this blog) Optional Arguments and ParamArrays

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Passing Arguments by Reference and Value

Returning Values using Arguments

Passing Arguments by Reference (ByRef)

Passing Arguments by Value (ByVal)

Head office

London

Manchester