I tried starting a forum, but you know the story about a tree falling in the forest when nobody is around.

So I am again trying something new. As soon as an article reaches 90 days old, WordPress will close the comment form. I may also decide to change that to 30 or 60 days. If you read an article and see "Comments are closed" at the bottom, and you want to follow up specifically about that article, please do one of the following:

1. Check the existing comments. Someone may have already brought up and addressed the issue.
2. Read the FAQ. Your question may be already answered there.
3. Write a response on your own blog or website. Send a trackback and it will appear at the bottom of the article in the Trackbacks section. If you don't have one, write the article and I'll publish it for you right here!
4. Post your question on one of the many active forums such as MrExcel, VBA Express, MSDN, Chandoo, ExcelGuru, Stack Overflow, and so on. Send me an email and if I have an account on the forum, I'll try to stop by and assist.
5. Send me a message on Twitter.
6. Send me an email. If you choose to go this route, please read the help section of the FAQ first.

I strongly encourage the first four options as your best chance of getting targeted help.

And I cannot emphasize this enough: if you choose to email me, please be specific. I get too many emails that are full blown work descriptions without any actual questions in them.

Sorry, TLDR!

If someone lets me know about an issue with an article (wrong code, bad advice, additional details etc) I will of course amend the article. But not through comments.

Please understand, I am not trying to discourage the interaction that is important for many blogs. There are still plenty of ways I can be reached. I really hope this doesn't come across as whining or condescending. I am not trying to hide from my critics. But as the amount of traffic grows, so is the amount of off-topic conversation. Comments are not being turned off completely, just on older posts. As usual, this is an experiment and if it looks like it's not working, I will revert to the previous state.

Thanks for your time. We now return you to your regularly scheduled helpings of VBA.

Related Articles:

• January 18, 2010 -- Exporting Comments using VBA

A small experiment in comments is Copyright © JP Software Technologies. All Rights Reserved.

It smartly uses a loop to start from the newest version and walks its way down to the lowest version, trying to set an object reference each time. But the VBA version of the same code just hardcodes each version. Why not loop in VBA as well?

Unfortunately a version-loop is required, because we cannot simply use CreateObject("DAO.DBEngine") like we can with other objects (Outlook.Application, Excel.Application and so on) to get whatever version exists on the local computer.

Who cares about the DBEngine Object anyway?

Before we start showing code, let's talk about what we can use this object for. It has a few important methods which we can leverage when working with Microsoft Access databases.

• CompactDatabase Method

The DAO.DBEngine.CompactDatabase Method may be used to compact and make copies of existing (closed) databases.

• CreateDatabase Method

This method lets you create and save new databases, and returns a Database object for you to work with.

• OpenDatabase Method

This method opens databases and returns a Database object.

• SetOption Method

The SetOption Method lets you store temporary values for various database options, such as whether you want to display the status bar or compact on close. This is great for application-specific settings that you don't want to change permanently, but only for a particular database. See SetOption for VBA syntax.

• BeginTrans, CommitTrans, Rollback Methods

These methods, when used with a given DBEngine.Workspace object, can be used to group changes to a database.

Use the BeginTrans method before updating the first record, and then, if any subsequent update fails, you can use the Rollback method to undo all of the updates. Use the CommitTrans method after you successfully update the last record.

Get Reference to DBEngine

This code is the approximate VBA equivalent of the VB.NET code from the above link.

Public Function GetDBEngine() As Object
  Dim versions() As String
  Dim i As Long

  ' put versions from newest to oldest into array
  versions = StringToArray("120,36,35")

  ' loop through array and try to set obj reference
  For i = LBound(versions) To UBound(versions)
    On Error Resume Next
    Set GetDBEngine = CreateObject("DAO.DBEngine." & versions(i))

    If Not GetDBEngine Is Nothing Then ' found it!
      Exit For
    End If
  Next i

End Function
Function StringToArray(entries As String, _
      Optional delimiter As String = ",") As Variant
  StringToArray = Split(entries, delimiter)
End Function

In your calling procedure, after calling GetDBEngine you need to compare it with Nothing to make sure it exists.

Sample Usage:

Sub GetDAO()

  Dim dbeng As Object
  Set dbeng = GetDBEngine

  If dbeng Is Nothing Then
    MsgBox "DAO is not available on this computer."
  End If

  ' or

  If Not dbeng Is Nothing Then
    ' your code here
  Else
    MsgBox "DAO is not available on this computer."
  End If

End Sub

Related Articles:

• April 25, 2011 -- Check if Office is installed
• July 30, 2010 -- An Introduction to YQL for VBA
• June 8, 2010 -- An exploration of IE browser methods, part II

Reference latest version of DBEngine is Copyright © JP Software Technologies. All Rights Reserved.

Manual / On Demand Method

This code may be run as needed to remove flags on existing emails in the default Inbox.

Sub RemoveFlags()
  Dim itms As Outlook.Items
  Dim msg As Outlook.mailItem
  Dim i As Long

  ' get default Inbox folder items
  Set itms = GetItems(GetNS(GetOutlookApp), olFolderInbox)

  For i = 1 To itms.count
    If TypeName(itms.Item(i)) = "MailItem" Then

      Set msg = itms.Item(i)
      ' remove flags
      With msg
        .FlagStatus = olNoFlag
        .FlagIcon = olNoFlagIcon
        .Save
      End With

    End If
  Next i
End Sub

This code should be placed in a standard module in Outlook's VBA editor (see Where do I put my Outlook VBA code for placement assistance).

Event Handler

Suppose you wanted to remove flags on all incoming emails automatically. Kudos to you for being brave. I started with the event handler found at Stock Event Code and produced this code:

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
  Set Items = GetItems(GetNS(GetOutlookApp), olFolderInbox)
End Sub

Private Sub Items_ItemAdd(ByVal item As Object)
  On Error GoTo ErrorHandler

  Dim msg As Outlook.mailItem

  If TypeName(item) = "MailItem" Then
    Set msg = item
    With msg
      .FlagStatus = olNoFlag
      .FlagIcon = olNoFlagIcon
      .Save
    End With
  End If
ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

This code should be placed in the ThisOutlookSession module in Outlook's VBA editor (see Where do I put my Outlook VBA code for placement assistance). Restart Outlook and then ask someone to send you an email with a follow up flag.

Ancillary Functions

Note that both of the above sections of code need these procedures. Paste them into a standard module.

Function GetItems(olNS As Outlook.NameSpace, _
                     folder As OlDefaultFolders) As Outlook.Items
  Set GetItems = olNS.GetDefaultFolder(folder).Items
End Function
Function GetNS(ByRef app As Outlook.Application) As Outlook.NameSpace
  Set GetNS = app.GetNamespace("MAPI")
End Function
Function GetOutlookApp() As Outlook.Application
  Set GetOutlookApp = Outlook.Application
End Function

Related Articles:

• January 9, 2012 -- Annoying Website Name Generator in VBA and PHP
• December 26, 2011 -- Validating Userform Data
• July 29, 2011 -- New Outlook Blog

Remove Flags from Inbox Emails is Copyright © JP Software Technologies. All Rights Reserved.

Function GetSystem32Folder() As String
 GetSystem32Folder = Environ("SystemRoot") & "\system32\"
End Function

Why is this important? I really don't know. But it was a few minutes of fun to find out how to do it. The system32 folder does contain a lot of DLLs which you can leverage in VBA.

Related Articles:

• July 8, 2009 -- VBA Tips, Tricks and Best Practices, Part One of Four

Determine local system32 folder is Copyright © JP Software Technologies. All Rights Reserved.

I am going to assume that Len is faster. To make things simpler I limited my tests to variable-length strings only.

The Test Procedure

As in previous posts, I wrote a simple non-scientific loop to casually test the speed of both functions.

Sub TestLenEmpty()

  Dim i As Long
  Dim str1 As String
  Dim starttimeLen As Single
  Dim endtimeLen As Single
  Dim starttimeEmpty As Single
  Dim endtimeEmpty As Single
  Dim msg As String
  Dim hasValue As Boolean

  Const numberOfLoops As Long = 100000000

  str1 = "Hello World"

  ' use Len
  starttimeLen = Timer
  For i = 1 To numberOfLoops
    hasValue = (Len(str1) > 0)
  Next i
  endtimeLen = Timer

  ' use Empty
  starttimeEmpty = Timer
  For i = 1 To numberOfLoops
    hasValue = (str1 = Empty)
  Next i
  endtimeEmpty = Timer

  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using Len: " & _
    Format(endtimeLen - starttimeLen, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using Empty: " & _
    Format(endtimeEmpty - starttimeEmpty, "#.###") & " seconds" & vbCrLf

  MsgBox msg
End Sub

According to Excel's Visual Basic Help, Empty is a Variant subtype, so I expect some implicit type conversion here when comparing string values to Empty. What I didn't expect was the dramatic difference in speed.

Results

At 100,000 loop iterations, the difference is minimal:

At 1,000,000 iterations, both loops are still very quick, but starting to separate:

At 10,000,000 iterations it takes a full second longer for the second loop:

Finally, at 100 million iterations, the difference is striking: a full eleven seconds longer!

Conclusion

There is simply no substitute for Len (no pun intended). It is the fastest method for checking string length (and therefore, whether strings are empty), and it scales well. I never really used Empty anyway, and I don't plan on starting now.

Related Articles:

• January 6, 2012 -- Is That String Really Empty?
• January 4, 2012 -- Len or LenB, which is faster?
• December 29, 2011 -- Len vs blank strings vs vbNullString

Len vs Empty, which is faster is Copyright © JP Software Technologies. All Rights Reserved.