MS Excel VBA Code
I Get It! Development has been creating custom
applications using Excel VBA and teaching Excel VBA to clients for years. Excel
is such a well-known standard that companies use it for database dumps,
reconciling accounts, analyzing lab tests... just about everything.
If you've ever used Macro Recorder, you're
partially familiar with VBA. Macro recorder creates simple VBA. Macro Recorder
doesn't provide the ability to make decisions, create loops or call custom
functions. For that, you must write your own code in the VB Editor.
Custom functions provide additional
functionality beyond Excel's already impressive list of mathematical and
financial functions. In this example, we write a function to look at a text
string and try to find a numerical date (i.e. 1/13/02, not January 13, 2002) in
it. This is useful for analyzing a 'Notes' or 'Comments' field from which you
hope to extract a date.
Excel Webinars
CPE Link is a new provider of web-based continuing professional education (CPE)
for accounting professionals. Practitioners can choose from approximately ten live
webcasts scheduled per month plus online self-study courses. To view all CPE Link’s
products and services, visit
http://www.cpelink.com |
This example uses string arrays, pattern
matching, and looping. To use this function in Excel, copy and paste the code
into a module in the VB Editor. Then go to spreadsheet cell A1 and type "This
was typed in on 1/13/02". In cell B1 type the formula =GetDate(A1).
When using functions like this on large
spreadsheets, be sure to copy the results and
paste them back as values
--
otherwise they are all recalculated each time the spreadsheet is updated!
Function GetDate(strInput As String) As Date
'---------------------- Start module ------
'I Get It! Development provides programming examples
'for illustration only, without warranty either
'expressed or implied, including, but not limited to,
'the implied warranties of merchantability and/or
'fitness for a particular purpose. This article
'assumes that you are familiar with the programming
'language being demonstrated and the tools used to
'create and debug procedures. These examples assume that
'you have licensed copies of all relevant software installed
'on the machine upon which the examples will be run.
'This function returns a date from
comment text.
'We will use pattern matching to determine the date's format then
'loop through the string looking for the date
'If no date can be found, we need a backup... use today's date
Dim DateFormat() As String
Dim intDateLength As Integer
Dim intMaxFormat As Integer
Dim intFrmtCtr As Integer
Dim intPosition As Integer
'These lines will allow us to quickly
add common date formats to check
intMaxFormat = 6
ReDim DateFormat(1 To intMaxFormat)
'Set up our array of common date
formats
DateFormat(1) = "*##[-/]##[/-]####*"
DateFormat(2) = "*#[-/]##[-/]####*"
DateFormat(3) = "*##[-/]#[-/]####*"
DateFormat(4) = "*##[-/]##[-/]##*"
DateFormat(5) = "*#[-/]##[-/]##*"
DateFormat(6) = "*#[-/]#[-/]##*"
'Set the default date to now, we'll
overwrite it later (if we can)
GetDate = Now
'We loop through each of the date
formats we want to check
For intFrmtCtr = 1 To intMaxFormat
If strInput Like DateFormat(intFrmtCtr)
Then
'if we find a match, we want
to know how long the date is
'Remember not to include the wildcard characters
intDateLength = Len(DateFormat(intFrmtCtr)) - 8
'Sometimes there's a space
before the date, sometimes after,
sometimes none
'Let's level the playing field and just get rid of all of
them!
This will not
'affect our document
strInput = Replace(strInput, " ", "")
'Now we'll loop through each
character in the string
'looking for a date of the specified length
For intPosition = 1 To Len(strInput)
If IsDate(Mid(strInput,
intPosition, intDateLength)) Then
GetDate = DateValue(Mid(strInput, intPosition,
intDateLength))
Exit Function
End If
Next intPosition
End If