On-line training in writing functions using VBA, for use either within other VBA programs or within Excel itself.
- Writing and Using Functions in Excel Visual Basic (this blog)
- Examples of a Function to Make Coding Easier
- Writing Functions for Use within Excel VBA
Posted by Andy Brown on 24 February 2012 | no comments
Writing and Using Functions in Excel Visual Basic
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:
- To help make code easier to write, read and maintain; or
- To supplement the list of built-in Excel functions.
This blog 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
'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:
- 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 blog), 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:
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.
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 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.