dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Macro Sub and Function Procedures

Understanding Excel VBA's sub and function procedures and when and how to use them.

Last updated on 2018-08-28 by David Wallis.


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 VBE (Visual Basic Editor) it will have this form:

Sub MacroName()

Recorded process 1

Recorded process 2

Recorded process n

End Sub

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. I’ve adopted this general structure for my sub procedures:

Sub dmwMacroName()

Process 1

Process 2

Process n

End Sub

I use the prefix dmw for all of my macro names. Then whenever I’m viewing any list of macros I can quickly distinguish mine from any other that might be included in the list.

Excel Macro Error Trapping and Handling extols the virtue of always providing your precedures that will intercept any errors that may occur as your macro is running and deal with accordingly. Incorporating error handling into my procedule skeleton, the code goes like this:

Sub dmwMacroName()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error Goto errHandler

Process 1

Process 2

Process n

procDone:

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

Resume procDone

End Sub

If the macro encounters an error while executing Process 1 to 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. You’ll find more about making the message as meaningful as possible to the user in Excel Macro Error Trapping and Handling.


Function Procedures

You cannot record a function procedure. You have to write it in the VBE (Visual Basic Editor). Its general form is this:

Function FunctionName()

Process 1

Process 2

Process n

FunctionName = value

End Function

Your function procedures are not listed in Excel’s Macro dialog box:

DB empty

They will 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 This illustrates why I choose to prefix my procedure names with dmw. I’m able to quickly filter the list to my functions from the dozens of Excel’s 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. This is the subject of the next section.


Function 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 DMW’s 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

The 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 has occured 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.

There are a number of ways of achieving this. I’m going to suggest one that I hope you find easy to follow. This involves substituting MyOpenWB, MyCopyPaste and MyFormat — all three currently sub procedures— with function procedures. The starting place is to create the functions I need.

This is the skeleton for each of the three functions:

Function dmwFunctionName() As Long

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error Goto errHandler

Dim i&

Process 1

Process 2

Process n

i& = 0

procDone:

dmwFunctionName = i&

End Function

errHandler:

i& = Err.Number

Resume procDone

Exit Function

On runs when it successfully completes Process 1 to Process n, dmwFunctionName will return the integer value of zero, as stored in i&.

If the function suffers an error along the way, then it will return 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 dmwControlProgram

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim i&

i& = dmwOpenWB()

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 dmwControlProgram '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Dim i&

i& = i& = dmwOpenWB()

If i& <> 0 Then

Identify error to user and stop the program

Else

i& = dmwCopyPaste()

If i& <> 0 Then

Identify error to user and stop the program

Else

i& = dmwFormat()

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 as a result.

Sub dmwControlProgram

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim i&

Dim msg$, title$, icon&

msg$ = "Please make a note of this error: "

icon& = vbOKOnly + vbCritical

i& = dmwOpenWB()

If i& <> 0 Then

title$ = "Error Opening Workbook"

msg$ = msg$ & Error(i&);

Else

i& = dmwCopyPaste()

If i& <> 0 Then

title$ = "Error Copy and Pasting"

msg$ = msg$ & Error(i&);

Else

i& = dmwFormat()

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$ & vbNewLine & vbNewLine

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 dmwControlProgram

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Error Goto errHandler

Dim i&

Dim msg$, title$, icon&

msg$ = "Please make a note of this error: "

icon& = vbOKOnly + vbCritical

i& = dmwOpenWB()

If i& <> 0 Then

title$ = "Error Opening Workbook"

msg$ = msg$ & Error(i&);

Else

i& = dmwCopyPaste()

If i& <> 0 Then

title$ = "Error Copy and Pasting"

msg$ = msg$ & Error(i&);

Else

i& = dmwFormat()

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$ & vbNewLine & vbNewLine

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: "

msg$ = msg$ & vbNewLine & vbNewLine

msg$ = msg$ & Err.Number & ": " & Err.Description

msg$ = msg$ & vbNewLine & vbNewLine

msg$ = msg$ & "Please 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.

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied.


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

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)