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.

Are You Ready For Office 365?

Office 365 is

• Email and calendaring powered by Microsoft Exchange
• Office Web Apps - the office productivity apps you already know, tailored for the web
• Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing website
• Instant Messaging and Online Meetings powered by Microsoft Lync
• All with a financially-backed 99.9% uptime guarantee

Sign up for a free trial:

Name
Company
Email Address

Your information will not be shared with any other party. You must provide a valid email in order to recieve your login information and starting information kit.

Watch this video  to see how Office 365 can help your business.

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