Microsoft® Excel Macro Sub and Function Procedures
Understanding Excel Visual Basic for Applications (VBA) Sub and Function procedures and when and how to use them.
Last updated on 2024-05-15.
Types of Procedures
Procedure is the term used in computing to describe a self-contained set of instructions intended to perform a task (process) or number of tasks. These instructions are written in the programming language of the application in which they are invoked. In Excel that language is VBA (Visual Basic for Applications).
The two types of procedures you are most likely to create are Sub and Function.
The main distinction between the two is that a Sub Procedure completes a task, or sequence of tasks; a Function Procedure completes tasks but further, on completion, returns a value.
Sub Procedures
Whenever you record a macro in Excel the procedure the recording creates is a sub procedure. When you view your macro in the Visual Basic Editor (VBE) it will have this form:
Sub MacroName()
Recorded process 1
Recorded process 2
…
Recorded process n
End Sub
When you run the macro it will execute the recroded processes in sequence, starting with Recorded process 1.
A write-your-own macro is an alternative to recording. It can start as a recorded macro which you have chosen to modify in order to improve its functionality:
Sub myMacroName()
My process 1
My process 2
…
My process n
End Sub
Excel Macro Error Trapping and Handling extols the virtue of always incorporating in your precedures error handling code that intercepts any error that may occur as your macro is running and deal with it accordingly.
Incorporating error handling into a sub procedule, the code goes like this:
Sub myMacroName()
On Error Goto errHandler
My process 1
My process 2
…
My process n
procDone:
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
Resume procDone
End Sub
If the macro encounters an error while executing My process 1 to My process n, the macro will stop running and pop up a message to the user that contains the number alloted by VBA to that error and a description.
In Excel Macro Error Trapping and Handling you'll find more about making the message as meaningful as possible to the user.
Function Procedures
You cannot record a function procedure. You have to write it in the Visual Basic Editor (VBE). Its general form is this:
Function myFunctionName()
My process 1
My process 2
…
My process n
myFunctionName = value
End Function
Your function procedures are not listed in Excel's Macro dialog box:
Function procedures show up when you start to create a formula in a worksheet cell. Type = and the beginning of your function's name and the drop-down list will include your functions.
This illustrates why my functions — their names prefixed with my initials — will filter the list of functions from the dozens of Excel's own functions with names beginning with “D”.
All this is assuming that my functions are intended to return values to worksheet cells.
There's another reason for creating function procedures and that is to facilitate structured process flow within a complex program — the subject of the next section.
Procedures and Structured Programming
If you're going to write your own program to automate a number of activities in Excel, then it's best to adopt structured programming from the outset.
On the Macros and VBA Programming in Excel page you'll find an introduction to the structured approach to programming. There, you find this suggestion for the sub procedure myControlProgram that runs a sequence of three macros — each one a sub procedure in itself:
Sub myControlProgram()
Call myOpenWB
Call myCopyPaste
Call myFormat
End Sub
Each Call command causes the macro in question to run. Thus running the myControlProgram macro causes the three macros to run in sequence, without intervention from you.
That's the theory. Now to the practice.
I want my control program to monitor the progress of its three macros:
- I want it to check that one has completed without error before initiating the next one
- If an error occures along the way, I want the control program to report it to the user and to stop the program continuing beyond the point of error.
One way of achieving this is by substituting all three sub procedures — myOpenWB, myCopyPaste and myFormat — with function procedures named fnOpenWB, fnCopyPaste and fnFormat.
We start by writing the functions. All three will follow the form applied here to fnOpenWB:
Function fnOpenWB() As Long
On Error Goto errHandler
Dim i&
Process 1
Process 2
…
Process n
i& = 0
procDone:
fnOpenWB = i&
End Function
errHandler:
i& = Err.Number
Resume procDone
Exit Function
When it successfully completes executing Process 1 to Process n, fnOpenWB returns the integer value of zero, as stored in i&.
If the function suffers an error along the way, then it returns the integer value — alloted by errHandler to i& — that in VBA represents the error in question.
Now we can set up a control program to respond to the function procedures that is overseeing. Here's a start on it, with the inclusion of a call to the first function procedure:
Sub myControlProgram
Dim i&
i& = fnOpenWB()
If i& <> 0 Then
Identify error to user and stop the program
Else
Process second function procedure
End If
Exit Sub
On including the two other function procedures we have the full flow of the control program:
Sub myControlProgram
Dim i&
i& = fnOpenWB()
If i& <> 0 Then
Identify error to user and stop the program
Elsei& =fnCopyPaste()
If i& <> 0 Then
Identify error to user and stop the program
Else
i& = fnFormat()
If i& <> 0 Then
Identify error to user and stop the program
Else
Program has completed without error
End If
End if
End If
Exit Sub
Thus we have the skeleton of our structured copy-and-format program. In the next section we'll apply the flesh to the bones.
Keeping the User Informed
Now to furnish the error messages to be displayed to the user as a message box, MsgBox, should any of the function procedures fail. Our objective is to intercept any error and tell the user what's happened.
Sub myControlProgram
Dim i&
Dim msg$, title$, icon&
msg$ = "Please make a note of this error: "
icon& = vbOKOnly + vbCritical
i& = myOpenWB()
If i& <> 0 Then
title$ = "Error Opening Workbook"
msg$ = msg$ & Error(i&)
Else
i& = myCopyPaste()
If i& <> 0 Then
title$ = "Error Copy and Pasting"
msg$ = msg$ & Error(i&)
Else
i& = myFormat()
If i& <> 0 Then
title$ = "Error Formatting Workbook"
msg$ = msg$ & Error(i&)
Else
title$ = "Changes to Workbook"
msg$ = "Copying and formatting completed OK"
icon& = vbOKOnly + vbInformation
End If
End If
End If
If i& <> 0 Then
msg$ = msg$ & _
"Please check your workbook before saving."
End If
MsgBox msg$, icon&, title$
Exit Sub
Error(i&) extracts from Excel's internal list of errors the description of the error identified by the number assigned to i&.
Final Error Handling
To intercept any unanticipated errors in the control program itself, I'm going to give it its own error handling routine:
Sub myControlProgram
On Error Goto errHandler
Dim i&
Dim msg$, title$, icon&
msg$ = "Please make a note of this error: "
icon& = vbOKOnly + vbCritical
i& = myOpenWB()
If i& <> 0 Then
title$ = "Error Opening Workbook"
msg$ = msg$ & Error(i&);
Else
i& = myCopyPaste()
If i& <> 0 Then
title$ = "Error Copy and Pasting"
msg$ = msg$ & Error(i&);
Else
i& = myFormat()
If i& <> 0 Then
title$ = "Error Formatting Workbook"
msg$ = msg$ & Error(i&);
Else
title$ = "Changes to Workbook"
msg$ = "Copying and formatting completed OK"
icon& = vbOKOnly + vbInformation
End If
End If
End If
If i& <> 0 Then
msg$ = msg$ & _
"Please check your workbook before saving"
End If
procDone:
MsgBox msg$, icon&, title$
Exit Sub
errHandler:
title$ = "Unanticipated Error"
msg$ = "Please make a note of this error: " & _
vbNewLine & vbNewLine & _
Err.Number & ": " & Err.Description & _
vbNewLine & vbNewLine & _
"Check your workbook before saving"
Resume procDone
End Sub
This completes the procedure. If you're going to use the code, then please test it thoroughly on a workbook that doesn't matter if something goes wrong.
Disclaimer
David Wallis does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
Thanks, in anticipation.