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.

  1. Writing and Using Functions in Excel Visual Basic (this blog)
  2. Examples of a Function to Make Coding Easier
  3. Writing Functions for Use within Excel VBA

This blog is part of our Excel VBA tutorial.  Wise Owl's main business is running classroom-based training courses; have a look at our Excel courses or VBA courses, for example.

Posted by Andy Brown on 24 February 2012 | no comments

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Writing and Using Functions in Excel Visual Basic

Most programs you write will begin with the word Sub - but not all:

Insert Procedure dialog box

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

End Function

There are 3 differences between this and a normal subroutine:

3 differences with functions

The numbered differences are explained below.

The differences are:

  1. A function begins with Function and ends with End Function (rather than Sub and End Sub).
  2. 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).
  3. 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: 

Dialog box showing square Inputbox for typing in number
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.

Displaying the Value of a Function in the Immediate Window

You can do this in the usual way:

Three calls to the SQUARE function

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:

Typing in SQUARE function Showing result
Your Square function appears ... ... and gives you the answer!

The formula for the cell shown above might look like this:

The formula used

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.