Most programs you write will begin with the word Sub - but not all:
When you choose Insert --> Procedure... from the VBA code editor menu, you can insert a Sub or a Function (the word Property is discussed in a later tutorial on classes).
There are two reasons that you might create a function:
To help make code easier to write, read and maintain; or
To supplement the list of built-in Excel functions.
This tutorial will show how to do each of these in turn, but first let's look at the syntax of a function.
How to Write Functions in VBA
The difference between a subroutine and a function in VBA is that a function returns a value. Consider the following very simple function to square a number:
Function Square(AnyNumber As Integer) As Long
Square = AnyNumber * AnyNumber
End Function
There are 3 differences between this and a normal subroutine:
The numbered differences are explained below.
The differences are:
A function begins with Function and ends with End Function (rather than Sub and End Sub).
A function has a data type (this one is of type Long, since we don't know how big an integer we'll have to return).
Within a function, you have to set the name of the function equal to something (here, the square of the number contained in the argument).
VB, C# and many other programmers should note that - irritatingly - there is no RETURN statement to return the value of a function in VBA.
You can call functions from Excel (shown in more detail in the final part of this tutorial), from another subroutine or from the Immediate window. Examples of each follow!
Calling Functions from Another Subroutine
One way to call a function is from another program, or procedure:
 |  |
We want to return this ... | ... when you type in a number. |
The program above (stripped of any error-checking) might read:
Sub ShowSquare()
Dim n As Integer
n = CInt(InputBox("Type in a number"))
MsgBox "The square of " & n & " is " & _
Square(n)
End Sub
As you see, you can use Square just as if it were a built-in function in VBA.
Displaying the Value of a Function in the Immediate Window
You can do this in the usual way:
Here we've called our Square function 3 times, with a different argument each time.
Again, you can treat your function as if it were one of the built-in ones in VBA.
Calling a Function from Excel
One other way to call a function - and perhaps the sexiest - is to type it into Excel:
 |  |
Your Square function appears ... | ... and gives you the answer! |
The formula for the cell shown above might look like this:
The function takes a single argument, which must be an integer. Here it is the value of cell C2.
One slight annoyance is that when you type the start of a function - in the case above, =Square( - Excel doesn't suggest possible arguments for it. You can get round this by using the function wizard, as shown in the final part of this tutorial.
Now that we've seen what a function is and how to run one, let's look at how you might incorporate them into Excel.
Example 1 - Adding a Worksheet
Whenever you naturally would want to ask a question in code, you should use a function. To see what I mean by that rather vague proposition, let's look at a worked example. Suppose you want to write a subroutine to allow a user to input a new worksheet:
After adding a new worksheet called test, it will appear in the list of worksheet tabs.
Here is some code to do this:
Sub AddWorksheet()
Dim SheetName As String
SheetName = InputBox("Type in new name", "Worksheet name")
Worksheets.Add
ActiveSheet.Name = SheetName
End Sub
When you run the AddWorksheet routine above, you will see an input box and be able to type in the name of a worksheet, which Excel will then create:
A user can type in the name of a worksheet in this dialog box and press Enter to create it.
The problem is: what happens if this worksheet already exists?
Example 1 - Pseudo-Code
In pseudo-code (half VBA, half English), here's what we're trying to do:
Display an input box
Store new worksheet name
If a worksheet with this name already exists
Display error
Otherwise
Create new sheet and give it the right name
What this shows is that we could really do with a function to determine if a worksheet with a given name already exists. We'll give it a name, and define the inputs and output.
What | Notes |
|---|
Function name | DoesSheetExist |
Inputs | SheetName - String |
Output | Whether the sheet exists or not - Boolean |
Given the above, it should be fairly easy to write our function.
Example 1 - The Final Code
Our function could now loop over the collection of worksheets trying to find one with a given name:
Function DoesSheetExist(SheetName As String) As Boolean
DoesSheetExist = False
Dim w As Worksheet
For Each w In Worksheets
If UCase(w.Name) = UCase(SheetName) Then
DoesSheetExist = True
Exit Function
End If
Next w
End Function
You could then call this function as follows:
Sub AddWorksheet()
Dim SheetName As String
SheetName = InputBox("Type in new name", "Worksheet name")
If DoesSheetExist(SheetName) Then
MsgBox "Worksheet with this name already exists"
Exit Sub
End If
Worksheets.Add
ActiveSheet.Name = SheetName
End Sub
Voila! Note that you could have done this without the function - that's always true - but we're aiming for neat, easy-to-read code.
Example 2 - A Conversion Function
Suppose you want to work out how tall you are in metres, given your height in feet and inches (these are weird, archaic units of measurement still commonly used in the UK - you don't need to understand what they are to use this example). Our Excel spreadsheet should look like this:
Cell C5 should calculate this trainer's height in metres (there are 12 inches in a foot, and 2.54 centimetres in an inch).
The Metres function we werite should take in two arguments:
The person's height in whole feet; and
The residual number of inches
and return the number of metres. Here's some code which would do this:
Function Metres(Feet As Single, Inches As Single) As Single
Const MetresPerInch As Single = 0.0254
Const InchesPerFoot As Integer = 12
Dim TotalInches As Single
TotalInches = Feet * InchesPerFoot + Inches
Metres = MetresPerInch * TotalInches
End Function
You can type in this function within Excel, but it's more fun to use the function wizard:
Click on the cell where the answer should go, then click on the function wizard tool.
You can now choose your function:
Choose to display user-defined functions (ie ones you've written).
Choose the Metres function.
Here's the Insert Function dialog box with the steps shown numbered:
The Insert Function dialog box
You now - at last - have help on the arguments, and can complete the dialog box in the usual way:
Complete the dialog box by specifying which cells will provide the feet and inches.
Advanced hint: user-defined functions are "volatile". What this means is if you calculate a spreadsheet containing user-defined functions their value will always be recalculated, even if it can't possibly have changed. This means that if you use VBA functions extensively your spreadsheet calculation speed may drop.
Personally I think Excel has got all of the functions I need, and I only ever use VBA functions within code to make programming easier.
