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 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:
Both tutorials are written for the beginner, or for the person who has not built their own functions in VBA before.
Random Numbers
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 Randomize RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest End Function
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 10
=RandomNumber(36526,37621) ... to give a random date between January 1 2000 and December 31 2002. (Format the result as a date!).
=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 wow many decimal places you want!
Find Saturday
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))) End Function
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 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.
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...
End Of Month
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.
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 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
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 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 theTotalMonths 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.
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 image]...
The result shows the calculated dates...
Remove Spaces
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.
Here's the code:
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
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 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.
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.
No comments:
Post a Comment