Chapter 8 Using Excel, VBA and user defined functions in VBA

8.1 Naming functions

When naming functions it is good practice to give them a meaningful name which explains what they are intended to do. This is particularly important if other people are going to use your code, or if you plan to return to it at some later time. While it may be obvious to you now what each function does, it may not be clear to others (or even yourself) at a later date.

There are certain restrictions on the names that you can give to functions. VBA will not allow functions to begin with anything other than a letter, and various symbols (such as spaces, !, $, #, %, …) are not permitted anywhere in the name. For obvious reasons, the name of a user defined function cannot be the same as a built-in function in Excel or VBA.

Also note that names of functions are not case sensitive. Thus a user defined function called Profit could be referred to equally well as PROFIT or even PrOfiT. A good convention can be to describe what the function does by means of a two or three word phrase, such as FinalCost or Process_Data.

8.2 Built-in functions in VBA

Many of the Excel built-in functions are also available in VBA. However, many of them have slightly different names. Here are a few examples

Function	VBA name	Excel name
Square root	Sqr	SQRT
Sign	Sgn	SIGN
Inverse tan	Atn	ATAN

To find functions in VBA, together with explanations of what they do and how to use them, search for “Maths Functions” in the help function. There are many other functions available in VBA, and we will only pick out a handful here to illustrate some of the many possibilities.

A few more useful functions for manipulating numbers include ABS(number) which returns the absolute value of the given number, and Fix(number) and Int(number) which return the integer part of the given number. The difference between these latter two functions is their effect on negative numbers: for example

Fix(-4.5) = -4 \(\quad\quad\quad\quad\) Int(-4.5) = -5

There are an extensive array of date and time functions. The function Date returns the system date, while Time returns the system time. To obtain both simultaneously you can use the function Now. These function do not have any arguments (and do not need an empty pair of brackets, unlike the equivalent Excel functions).

To manipulate dates and times, there are a number of useful functions available. Given a variable date of type date, the functions Day(date), Month(date), and Year(date) return the day of the month, the month, or the year respectively. Similarly there are functions Hour(date), Minute(date), and Second(date). The function Weekday(date) returns a number indicating which day of the week the given date occurs on.

Another class of useful functions are those which manipulate strings of data. Given a variable string of type string, the function Len(string) returns the total number of characters in the string (including any spaces etc.). The function Left(string, n) returns the first \(n\) characters in the given string from the left (or the entire string if it has less than \(n\) characters). There is a similar function Right(string, n), and a function Mid(string, start, n) which takes the \(n\) symbols from string starting at position start. For example

Mid("The quick brown fox",7,5)

will return the value "ick b". The function Space(n) will return a string containing \(n\) spaces, while the function & can be used to combine two strings String1 and String2 into a single string: for example

"ABCD" & "CDEF"

will return the string "ABCDCDEF". A very useful function for avoiding input errors is the function LCase(string) which takes a string and returns the same string entirely in lower case characters. This can be used to avoid having to worry about whether user inputs will be capitalised or not.

The online help function, and Google, will provide you with descriptions of many other built-in VBA functions.

8.3 Using Excel functions

There are many functions not provided by VBA, often because the same functions are already available as part of Excel. In order to use an Excel function such as AND inside some VBA code we need to prefix it as

Application.WorksheetFunction.AND

or as

Application.AND

The advantage of the former method is that we get the same help prompts in VBA as we do in Excel, and so (for example) can use Tab to complete the name of a function, or can get help as to the sort of arguments required by the function. The latter method has its own advantages, particularly when it comes to error detection, as we will see in the next section.

If you use an Excel function that requires a range (such as VLookup), then you will need to enter the range in an appropriate format. If you want to implement the Excel command

=VLookup(name, A1:D50, 3, FALSE)

in VBA, then you can get away with entering

Application.WorksheetFunction.VLookup(name, [A1:D50], 3, False)

however, the range format used here is a little ad hoc, and a better way would be to enter

Application.WorksheetFunction.VLookup(name, Range("A1:D50"), 3, False)

For simple examples this second method may seem unnecessarily complicated, but it is more consistent with other uses of the Range command in VBA. We will often use the first method in simple examples.

Whenever you use a built-in function in VBA or Excel, you should always check that it does what you expect. For example, the VBA function Round does not round numbers in the usual way, but instead implements what is called “Bankers’ rounding”, which rounds numbers ending 0.5 to the nearest even number. Thus 3.5 is rounded to 4, but 6.5 is rounded to 6. To use the standard method of rounding you instead should use

Application.WorksheetFunction.Round

as the Excel =Round function behaves in the expected way. (Bankers’ rounding is used in bookkeeping, and has certain statistical advantages, so is less arbitrary than it might first appear.)

8.4 Handling errors arising from Excel functions

When using an Excel function, various errors can arise. For example, the function can be expecting a different type of variable from the one it is asked to calculate with, or may be performing some illegal operation, such as division by zero.

One of the important things to bear in mind when writing any kind of code is the possibility of unexpected errors. These may arise because of situations which you did not expect, or because the user does something which was not intended.

Well written code will have some ability to deal with at least the most common kinds of error. As an example of how this can be done, in this section we will consider how to write VBA code including Excel functions that may give errors.

We have already seen that an Excel function such as Vlookup can be invoked in VBA by using either Application.WorksheetFunction.Vlookup or Application.Vlookup to call it. When the function works correctly there is no practical difference between these two methods, but they handle errors very differently.

Suppose that you wish to use a Vlookup function to look for certain values depending on a name given by the user. You could include the line of code

answer = Application.WorksheetFunction.Vlookup(name,[A1:D10],4,FALSE)

If the value given to name does not occur in the first column of the range, then the Vlookup function will give an error and stop the whole VBA program from running. All that you will get is an error message. On the other hand, if you use the line

answer = Application.Vlookup(name,[A1:D10],4,FALSE)

then Vlookup will still return an error, but this time the variable answer will have the value of the error, and VBA will continue to run.

This is useful as we have commands in VBA which can detect errors. The VBA function IsError takes a single argument, and returns the value True if the value of the argument is an error, and False otherwise. This can be used to deal with errors as in the following example.

Suppose that we wish to write a simple function which looks for a name from a table of names in column A and returns the corresponding exam mark in column B. We want to be able to deal with cases where the given name does not occur in the table. The following code takes a name as input and either returns the mark or notes that the given name does not occur.

Function mark(name As String) As Variant
Dim value As Variant
value = Application.VLookup(name, [A1:B4], 2, False)
If IsError(value) = True Then
    mark = "This person did not take the exam"
Else
    mark = value
End If
End Function

We should note that there are also ways of handling such errors even if we use the the Application.WorksheetFunction method. Some authors think that this latter method is better from a stylistic point of view. We have used the former method here because it fits in well with our other material, and is relatively easy to explain.

Notice also that when we use this method the type of any variables involved has to be variant, even if the answer for correct inputs should always be a number, or a string. This is one of the disadvantages of using this method.

8.5 Using other user defined functions

We saw in lectures that we can put multiple user defined functions into a single module. Such functions are then all available to be used in the associated spreadsheets, but can also be used inside each other in just the same way as any built-in VBA function can be.

So far we have only considered the simplest case, where we use a single worksheet inside a single workbook, and have a single VBA module containing all of our functions. However, in general we can use many workbooks containing many worksheets, and have many associated modules. In such cases functions from one module can be used elsewhere, but the procedure is a little more complicated. For this reason we concentrate here on the simplest case, which will be sufficient for our needs.