More Custom Functions for Access and Excel
The Swiss Army Knife of the VBA Toolkit
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
- 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
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.
Here's the code...
Public Function RandomNumber(Lowest As Long, Highest As Long)
' Generates a random whole number within a given range
RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
How does it work?
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.
How do you use it?
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
=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 wow many decimal
places you want!
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.
Here's the code:
Public Function FindSaturday(InputDate As Date)
' Returns the date of the first Saturday following the Inputdate
FindSaturday = FormatDateTime(InputDate + (7 - Weekday(InputDate)))
How does it work?
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
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 -
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.
How do you use it?
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
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.
Here's the code:
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
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
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)
If Int(NewYear / 4) = NewYear / 4 Then
EOMonth = DateSerial(NewYear, NewMonth, 29)
EOMonth = DateSerial(NewYear, NewMonth, 28)
How does it work?
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
=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
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
- 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
- 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.
How do you use it?
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 [click the thumbnail to see the full-sized
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 email@example.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
Here's the code:
Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, " "))
How does it work?
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
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
- 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
The Left() function returns a given number of characters
from the left side of a string, so Left("Martin Green",6)
The Right() function returns a given number of characters
from the right side of a string, so Right("Martin Green",5)
- 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.
How do you use it?
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.