contact@consultdmw.com

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:

Excel 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.

Excel function drop-downExcel function dropdown list 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:

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

Else

i& =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.

“Can't wait until someone develops the excel function where the worksheet does exactly what I want it to by simply dreaming it up in my head.”

JT, Excel Theatre Blog (2018-02-28)

“I see no progress in this industry. These clocks are no faster than the ones they made a hundred years ago.”

Henry Ford (while visiting a museum)