Home	Case Studies	MS Office Tips and Tricks	Sample Code	Helpful Videos	Custom Development	Custom Training	Join Us	Contact Us
Search Our Site:

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.

A Word From Our Sponsor

CPE Link is a new provider of web-based continuing professional education (CPE) for accounting professionals.

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