The more I use Excel and Access, the more I expect them to be able to do for me, and it sometimes comes as a surprise that the programs don't know how to do a particular kind of calculation for me. Microsoft provided VBA so that its customers could essentially continue building the programs to suit their own specific needs. The ability to create custom functions (or UDFs - user defined functions) is a perfect illustration of this.

If you have ever asked the question "Why doesn't Excel have a function for..." then you need to learn how to create custom functions. Written in VBA, custom functions can be used from the interface of the program itself or from within VBA procedures (macros). Once you have discovered UDFs you'll wonder how you ever managed without them.

In this tutorial I have included some functions that I created in response to a specific requirement. They are all real-world examples:

**RandomNumbers**generates a random number within a specified range.**FindSaturday**returns the date of the first Saturday following a given date.**EOMonth**returns the last day of the month of a supplied date. It can be the last

day of the same month or one a specified number of months later.**RemoveSpaces**removes all the spaces from a string of text. It can be adapted

to remove any specified character.

If you are new to writing functions, or have not done any VBA coding before, you might like to take a look at the following tutorials first:

**Writing Your First VBA Function in Excel****Building Custom Functions for Your Access Applications**

Both tutorials are written for the beginner, or for the person who has not built their own functions in VBA before.

I often find that I need random data. Whenever you create a new spreadsheet or write a new macro you should test it thoroughly with real data. But often you don't have any data to work with. The answer is to make some.

Excel has a function that generates a random number (**=RAND()**).
It generates a random number greater than or equal to zero but less
than 1. I usually use it something like this:

**=INT(RAND()*1000) **... to give me a random
whole number between 0 and 999 (I multiplied by a number one greater
than my required maximum and then turned the result into a whole
number by rounding down with the =INT() integer function.

This is OK but I often want to specify a lowest
number as well as a highest one, i.e. I want random numbers within a
particular range. That means more typing (and I'm always forgetting
how to figure it out!) so it was an ideal candidate for a custom
function. Oh, and before you hit the email and tell me that Excel's
Analysis ToolPak contains the **RANDBETWEEN** function which the
same job, I already know that (but I didn't when I wrote the
function!). My function does have the advantage that is is
non-volatile, meaning that it does not automatically recalculate
every time the worksheet recalculates. Follow the link to the "new
improved" version below to see how that works.

Public Function RandomNumber(Lowest As Long, Highest As Long) ' Generates a random whole number within a given range Randomize RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest End Function

The function takes two arguments: **Lowest**
being the minimum of the range and **Highest** being the maximum.

The first line of the code uses the command **
Randomize** to initialise the program's random number generator (I
have no idea how this works - if you are interested, you can read
about it in VBA help). Next comes the line that does all the work...

I mentioned earlier that the number by which you
multiply the random number has to be one larger than your required
maximum. This only matters when you are ending up with whole
numbers. If you enter 1000 the largest number you might get is 999,
so if you want the possibility of getting 1000 you multiply by 1001.
I didn't want the user to have to remember this so I added the extra
1 to the **Highest** argument in the code. Because this function
allows the user to specify a range, the **Lowest** is subtracted
from **Highest** and the result is multiplied by the number
generated by the random number function (note that here I use VBA's
own random number function **RND**). I then add the resulting
random number to the desired minimum.

Confused? Here's an example... Supposing you want random numbers falling in the range 750 to 1000. The range of randomness you want is actually from zero to 250 (1000 minus 750). If the random number generator comes up with a zero you can add it to your minimum (750) and you get 750. If it comes up with 250 you get 1000. If it comes up with 137 you get 887.

The function is used like this:

**=RandomNumber(750,1000)** ... to give a random
number between 750 and 1000

**=RandomNumber(0,10)** ... to give a random number between 0 and
10

**=RandomNumber(36526,37621)** ... to give a random date between
January 1 2000 and December 31 2002. (Format the result as a date!).

TIP: When I'm using this function I
usually want to full a whole bunch of cells at one go, so here's
today's tip: **Excel Block-Fill. **Select a block of cells. Type
out your RandomNumber function and press **CTRL+ENTER**. What you
typed gets put into into all the cells at one go!

I use random numbers to generate all sorts of data, from oil prices to invoice numbers and dates of birth. This function is probably the best time-saver I've come up with to date.

Now go and check out the "NEW
IMPROVED" version in which you can specify how many decimal
places you want! (See: **An Improved Custom Random Numbers Function**)

I wasn't going to include this example because I thought it had limited appeal but, as I started writing this page, the Excel discussion group received a posting asking if anyone could suggest a function that would supply the date of the next Saturday following an given date.

I wrote it to help with an application I built for a client. They are commodity brokers dealing with prices quoted at various points in the future. One of these is "Weekend", referring to the Saturday and Sunday following the date of the quote. The trader supplies the date of the quote (which could be any day from Monday to Friday) and the system has to figure out the dates of the weekend. My function provides the Saturday. You add 1 to get the Sunday.

Public Function FindSaturday(InputDate As Date) ' Returns the date of the first Saturday following the Inputdate FindSaturday = FormatDateTime(InputDate + (7 - Weekday(InputDate))) End Function

The function takes a single argument: **InputDate**. This is
the date for which you want to find the following Saturday. It makes
use of two VBA functions: **Weekday** which returns a number from
1 to 7 depending on the day of the week of the supplied date
(1=Sunday, 2=Monday etc.), and **FormatDateTime** which displays
a date serial in your chosen date format (the default being General
Date).

The first job is to find out how many days there are between the InputDate and the following Saturday. I do this by using the Weekday function to find out what day number the InputDate is. Lets say it's a Tuesday (i.e. day 3). Saturday is day 7 so if we subtract Tuesday from Saturday (7-3) we get an answer of 4. So our InputDate is 4 days before the following Saturday. All we have to do is add 4 to our InputDate to get the date of the following Saturday.

This is accomplished by the calculation: **InputDate + (7 -
Weekday(InputDate))**

If I left it like this it would work fine inside a VBA procedure, but used on an Excel worksheet or in an Access query it wouldn't be perfect because the result would be returned as a number, the date serial. To solve this I wrapped the calculation inside the FormatDateTime function. This makes sure that the result is returned ready formatted as a date. I could specify any date format but I chose to accept the default.

This function can be used anywhere you might use a regular function. Here it is on an Excel worksheet...

Here is how I used it in an Access query where only the Input Date (called here the MarketDate) was supplied...

In the second column I have used the FindSaturday function in a calculated field to work out the date of the Saturday following the MarketDate. In the third column I have placed another calculated field that works out Sunday's date simply by adding 1 to Saturday's date. Here's the resulting datasheet...

Excel users benefit from a wide range of built-in functions, as well as additional functions in add-ins shipped along with the program. One that I use frequently is Excel's EOMONTH function that comes with the Analysis Toolpak add-in. This function can calculate the last day of the month of any given date. You can ask it to calculate the last day of the month of the date you supply, or of a date a specified number of months later.

The reason that the function is so useful is that it isn't an easy calculation to do otherwise. The month can have one of four different last dates (28th, 29th, 30th, 31st) depending upon which month it is. If you want anything other than the current month you have to know which that month will be. And, depending on what date is given, the resulting date may in in the same year or a different one.

So, thanks Microsoft for the EOMONTH function... except that I want to use it in Access and Access doesn't have the EOMONTH function! If I was going to do this calculation in Access I was going to have to build the function myself. After much head scratching, staring into space, and several cups of coffee I came up with my own version.

Public Function EOMonth(InputDate As Date, Optional MonthsToAdd As Integer) ' Returns the date of the last day of month, a specified number of months ' following a given date. Dim TotalMonths As Integer Dim NewMonth As Integer Dim NewYear As Integer If IsMissing(MonthsToAdd) Then MonthsToAdd = 0 End If TotalMonths = Month(InputDate) + MonthsToAdd NewMonth = TotalMonths - (12 * Int(TotalMonths / 12)) NewYear = Year(InputDate) + Int(TotalMonths / 12) If NewMonth = 0 Then NewMonth = 12 NewYear = NewYear - 1 End If Select Case NewMonth Case 1, 3, 5, 7, 8, 10, 12 EOMonth = DateSerial(NewYear, NewMonth, 31) Case 4, 6, 9, 11 EOMonth = DateSerial(NewYear, NewMonth, 30) Case 2 If Int(NewYear / 4) = NewYear / 4 Then EOMonth = DateSerial(NewYear, NewMonth, 29) Else EOMonth = DateSerial(NewYear, NewMonth, 28) End If End Select End Function

The function accepts two arguments, one of which is optional. The
required argument **InputDate** is the specified date from which
the function will calculate a month end. The optional argument **
MonthsToAdd** is a whole number (integer) being the number of
months on from the specified date that the month end has to be. For
example:

**=EOMonth(myDate)** returns the last day of the same month
(e.g. *27 September 2002* gives *30 September 2002*)

**=EOMONTH(myDate,6)** returns the last day of the month that
is 6 months after the given date (e.g. *27 September 2002*
gives *31 March 2003*).

First of all I declare three variables: **TotalMonths**, **
NewMonth**, and **New Year**, that I will be using in the
calculation.

Next comes an **IF** statement that checks to see whether the
optional **MonthsToAdd** argument has been supplied. This uses
the **IsMissing()** function, which is only ever used for this
purpose. If the optional argument is not supplied, it is assumed to
be 0 (zero).

The three variables are now supplied with values:

**TotalMonths**is given a value of the month supplied plus the number of months to be added. To continue the example above, September is month 9. If no months are to be added then**TotalMonths**is given a value of**9**(9+0). If six months are added then**TotalMonths**is given a value of**15**(9+6).**NewMonth**is the month that you arrive at having added a certain number of months. Clearly, there isn't a 15th month of the year, but if we divide the**TotalMonths**by 12 we can use the remainder as the new month (15 divided by 12 = 1 remainder 3). Our 15th month becomes the third month, i.e. March.**NewYear**is the year that we arrive at after adding the months. If the**TotalMonths**value is 12 or less then the year is the same as the year supplied, but if it exceeds 12, the number of times we can divide it by 12 gives us the number of years to add. We can divide 15 by 12 once, so we need to add 1 year to the year supplied.- Now we have to correct an anomaly in the maths that allows the
NewMonth to be calculated as zero when it ought to be 12. When
this happens the NewYear is also calculated incorrectly being one
larger than it should. An
**If Statement**puts things right.

So, we now know the answer to the *Month* and the *Year*.
We just have to calculate the *Day*. Fortunately, that's the
easy bit!

- A
**Case Statement**looks at the different possibilities of what month we could end up with. We know that one group of months have 31 days, and another group of months have 30 days. That takes care of the first two Cases. - The third case deals with February (month 2) which has 28
days, unless it is a leap year, when it has 29 days. So how do we
figure out if it is a leap year. The test is that a leap year is
evenly divisible by 4 (i.e. nothing left over), so we need to
divide the year by 4 and see whether the answer is a whole number
or not. That's where the
**If Statement**comes in. If the answer is a whole number, then it's a leap year so the answer is 29, otherwise it isn't a leap year and the answer is 28. - OK, so how do you figure out whether a number is a whole
number or not? If we make our number into a whole number by
removing any fractions, we can compare the result with the
original. If they are the same then the original must have been a
whole number already (and boy did it take me a while to figure
that one out!). That calculation is the basis of the If Statement.
It uses the
**Int()**function to make an integer (whole number) out of the calculation**Year/4**and compares it with the straight calculation of**Year/4**. - Finally, each part of the Case Statement uses the
**DateSerial(Year,Month,Day)**function. This function takes three separate whole numbers (for the year, the month, and the day) and turns them into a date. Because the DateSerial function produces a date serial number rather than a string of numbers and slashes, it makes my EOMonth function completely independent of date conventions. I don't need to worry about whether the user uses dd/mm/yy, mm/dd/yy or any other date format.

This function is primarily designed for Access although I have used it in the code of an Excel application when I couldn't be sure that the user would have the necessary Excel add-in installed. Here I am using it in a Access query to month-end dates at various times after the start date. The design view of the query shows three calculated fields...

The result shows the calculated dates...

This is one of those functions that, having built it, I wondered
how I had ever managed without! It came about because I wanted to
build a list of email addresses from a list of people's names. It
was a big list! The plan was simple enough... the email address
would be: **firstname-dot-lastname-@-domainname**.
*Martin Green
*would become: *martin.green@fontstuff.com*.

Then I came across a problem. Some people had names with spaces
in them (e.g. *Anne Marie*, or *van Linden*). I had to
find and get rid of the spaces because this would have created
illegal email addresses.

The function I came up with can be modified to remove any chosen character from a string of text, such as the hyphen (-). I've used it to tidy up data like telephone numbers, postal codes, ISBN numbers and other kinds of data that can be typed in a variety of ways.

Public Function RemoveSpaces(strInput As String) ' Removes all spaces from a string of text Test: If InStr(strInput, " ") = 0 Then RemoveSpaces = strInput Else strInput = Left(strInput, InStr(strInput, " ") - 1) _ & Right(strInput, Len(strInput) - InStr(strInput, " ")) GoTo Test End If End Function

This function accepts a single argument, **strInput**, the
string of text from which any spaces have to be removed. At the core
of the function is the **InStr()** function. The **InStr()**
function takes two arguments: the first being the string of text to
be examined, and the character to be located. It then returns the
position of the first occurrence of that character.

If I entered **InStr("Martin Green","t")** the result would be
**4**, the first letter "t" being the 4th character in the
string.

If I entered **InStr("Martin Green"," ")** the result would be
**7**, the first "space" being the 7th character in the string.

- The first line of the function is a label
**Test:**marking the start of the procedure. This is a reference point for the function to return to so that it can run again if necessary. - Next comes an
**If Statement**which uses the**InStr()**function to test the string to see if there are any spaces. If the function returns a zero, then the string does not contain any spaces and the function finishes, leaving**strInput**as it was. - If the
**InStr**function returns anything other than zero, it means that a space has been found. So the**Else**part of the**If Statement**removes it with the aid of the text functions**Len()**,**Left()**and**Right()**.

The **Len()** function returns the number of characters in a
string (including any spaces), so **Len("Martin Green")** returns
**12**.

The **Left()** function returns a given number of characters
from the left side of a string, so **Left("Martin Green",6)**
returns **Martin**.

The **Right()** function returns a given number of characters
from the right side of a string, so **Right("Martin Green",5)**
returns **Green**.

- Armed with these functions I can figure out how long is the
**strInput**string. The**Instr()**function tells me where the first space is, so I can use the other text functions to take the characters that are before the space, and the characters that are after the space, and concatenate them (a fancy word for joining them together) omitting the space. - But there might be more than one space in the string. This
method removes only the first space. So the next line:
**GoTo Test**sends the procedure back to the beginning, where is checks for spaces. If there are none the procedure finishes, otherwise the process repeats until all the spaces are removed.

This function can be used in Excel or Access to remove spaces from a string. I often use it within a VBA procedure. Here it is on an Excel worksheet...

Here is an example of how I used an Access query to create email addresses whilst finding names containing spaces (using the InStr() function) and purging them...

Here's the result...

To have the function remove a different character, just change the " " (quote-space-quote) parts of the function (it occurs 3 times) with your chosen character in quotes, for example: "-" (quote-hyphen-quote) will remove hyphens from a string.

The function could be further adapted to remove groups of characters from a string, but this is a little more complicated requiring the length of the group to be calculated. I'm working on it... watch this space.

©2002 Martin Green - www.fontstuff.com - martin@fontstuff.com - All rights reserved