Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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.
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
'return the square of any integer
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!
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
'get the number user wants to square
n = CInt(InputBox("Type in a number"))
'show this square
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.
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.
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.
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()
'the name of the new worksheet
Dim SheetName As String
'ask user to type in name
SheetName = InputBox("Type in new name", "Worksheet name")
'add a new worksheet, which becomes the active sheet
Worksheets.Add
'rename this sheet to the new name typed in
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?
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.
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
'ARGUMENTS
'=========
'SheetName The sheet name we're testing for
'initialise the function (assume it's false)
DoesSheetExist = False
Dim w As Worksheet
For Each w In Worksheets
'for each worksheet, see if the name matches
If UCase(w.Name) = UCase(SheetName) Then
'it does!
DoesSheetExist = True
'no need to test any more
Exit Function
End If
Next w
'if we get here, we didn't find the sheet
'(but function initialised to false, so no need
'to do anything more)
End Function
You could then call this function as follows:
Sub AddWorksheet()
'the name of the new worksheet
Dim SheetName As String
'ask user to type in name
SheetName = InputBox("Type in new name", "Worksheet name")
'add a new worksheet, which becomes the active sheet
'(but first check whether exists or not)
If DoesSheetExist(SheetName) Then
MsgBox "Worksheet with this name already exists"
Exit Sub
End If
Worksheets.Add
'rename this sheet to the new name typed in
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.
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
'first work out how may inches tall the person is
Dim TotalInches As Single
TotalInches = Feet * InchesPerFoot + Inches
'now return number of metres
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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.