564 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 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.
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)
'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
IfFound = True
'if get here, failed to select sheet
IfFound = False
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:
Dim IfWorksheetExists As Boolean
'try selecting a worksheet
SelectWorksheet "Sheet1", IfWorksheetExists
'say whether found or not
If IfWorksheetExists Then
MsgBox "Worksheet found"
MsgBox "No such worksheet"
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.
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.
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:
Dim i As Integer
'show first ten squares
For i = 1 To 10
Sub ShowSquare(i As Integer)
'square this number
i = i ^ 2
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|
25 Aytoun Street