7.4 Create your own Functions in Excel VBA

Functions are closely related to the Subs you learned about in a previous section, and are set up in a similar way. The difference is that functions return a value (like the MsgBox function) whereas Subs don't return a value - they just get on and execute the code. You use a function when you want a chunk of code to return some sort of answer for you.

When you set up a function, you set it up like this:

Function function_name( ) As variable_type

You start with the word Function. After a space, you need to come up with a name for your function. Like Sub names, this should be something relevant to what the function does.

After the name of your function, you need a pair of round brackets. Just like Subs, the round brackets are used for any arguments you want to pass over to your function.

One of the big differences between setting up a Sub and setting up a Function is the return type at the end. This is exactly the same as setting up a variable type. So you can have As String, or As Boolean, or As Integer - any of the types you can use with ordinary variables can also be used with functions. If you miss off the As Type at the end then the function will be As Variant.

The function name you come up with is like a variable: whatever answer you want your function to produce will be stored in the function name.

To return a value from your functions you need this:

function_name = value

Whatever name you gave your function goes on the left of an equal sign. To the right of the equal sign is where you put the value to want your function to return. The return value must match any As Type you have at the start. So if you've set your function up with As Integer you can't place a string value to the right of the equal sign.

To call a function into action you need to do so on the right of an equal sign. To the left of the equal sign, you need a variable. Like this:

ReturnValue = function_name(arguments_here)

VBA will first execute the function on the right of the equal sign. When it has worked out the value of the function for you, it will place the result in your variable to the left of the equal sign. So it's just like normal variable assignment, except that VBA is working out the result of a function.

To clear all this up, let's have a coding example. What we'll do is to set up a function that does some error checking. We'll call the function from a Sub. The return value of the function will be a Boolean. A value of True means everything is OK, while a value of False means we need to bail out of the Sub.

Add the following as the first line of your function:

Function CheckCell(CellValue) As Boolean

Now press the enter key on your keyboard. When you do, you'll find that VBA adds End Function for you.

This function has the name CheckCell. The value that will be stored in CheckCell will be a Boolean value (As Boolean). Between the round brackets of our function we have a variable name. This is just like we did for Subs. Because this variable has no As Type ending it will be a Variant type.

As the code for your function, add the following:

If IsNumeric(CellValue) Then

CheckCell = True

Else

CheckCell = False

End If

Your coding window should look like this:

The function just checks the CellValue variable and tests if it is numeric or not. If it is, then this line gets executed:

CheckCell = True

This means that a value of True will be placed inside of the function name, which was CheckCell. This is what the function will return with, after it has been executed.

However, if the CellValue variable is not a number, we set CheckCell to False. The function will return with this value, after it has been executed, and CheckCell is not a number.

CheckCell = False

In other words, we've set up two alternatives for our Boolean function, one for True and one for False.

Now for the Sub that calls the function. Here it is:

The first line sets up a Boolean variable called ReturnValue. We need a Boolean value because the function returns either True or False (though we could have set ReturnValue to be As Variant).

The second line is this:

ReturnValue = CheckCell(ActiveCell.Value)

On the left of the equal sign, we have our Boolean variable. This is going to hold the value that CheckCell returns with. On the right of the equal sign is where the function call is:

CheckCell(ActiveCell.Value)

When VBA sees this line it looks for a function called CheckCell. It then goes off and executes any code for this function and returns a value to be stored on the left of the equal sign.

Because we set up the function with one argument between round brackets then the call to the function needs one value between its round brackets. Any more or any less and you'd get an error. The value we have between the round brackets of the calling line is ActiveCell.Value. The value of the active cell will then get handed over to the function. This value will end up in the variable we called CellValue in the function.

The rest of the code is this:

If ReturnValue = False Then

MsgBox ("Not a number - Exiting Sub")

Exit Sub

End If

Here, we're testing the value that was returned from the function. If the value is False then we display a message box and Exit the Sub. Exiting the Sub here means that any code below it won't get executed. If ReturnValue is True, however, then VBA will skip past the End If and run any code from that point on.

Two other points to make about functions before we leave the topic. First, you can set them up as Private, just like you can with Subs (the default is Public). Setting up a function as Private means it won't be available in the coding window of other worksheets. If you want to access a Public function you coded in another worksheet then you need the name of that worksheet first. So this:

Sheet1.CheckCell("ten")

And not just this:

CheckCell("ten")

The second point to make about functions is that you can Exit them early by typing Exit Function. This works in the same way as Exit Sub does.

OK, we'll move on. In the next section, you'll learn how to access Excel's own inbuilt functions with VBA code.

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney