564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
Writing functions for Excel using VBA
Part one of a three-part series of blogs
On-line training in writing functions using VBA, for use either within other VBA programs or within Excel itself.
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 blog on classes).
There are two reasons that you might create a function:
This blog 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
There are 3 differences between this and a normal subroutine:
The numbered differences are explained below.
The differences are:
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 blog), 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:
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 " & _
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 blog.
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.
|Parts of this blog|
25 Aytoun Street