Outlook VBA how to improve speed when looking thru a folders list

Question

I'm currently writing some code so that when I send an email based upon certain criteria it will save it in a specific folders. I know that rules do something like this, but believe me, it will take forever and wouldn't be practical to set that many rules. So here's what I'm trying to do. When an email is sent, code looks into the title of the email to find a project number. Then it pops up a userform where the user can make multiple filing choice based on it's need. It first save the project number using the VBA.SaveSetting method It then returns object "objX" which is in fact the email object that is being send. So in the code below I'm looping thru a list of public folders. The first part of the folders name is the project number. The project number I'm looking for is saved in "proj_folder" local variable.
So I'm looping thru all the folders to find the folder name that is beginning with "proj_folder" variable. It's working fine except for the fact that it can get pretty slow if there's a lot of folders to loop thru. Looking at the code below, would someone be kind enough to share a way to improve my looping speed. Right now it can take around 2 seconds to loop thru let's say 30 folders. Sometime it can get up to more than 200 folders.

Sub MoveProject(objX)

Dim objNS As Outlook.NameSpace
Dim projectParentFolder As Outlook.MAPIFolder
Dim objFolder As Outlook.MAPIFolder
Dim proj_folder As String
Dim intX As Long

 'recall of the name of the folder saved from a previouly filed userform
proj_folder = VBA.GetSetting("mail filing", "num_projet", "num_proj", vbNullString)

sub_folder_1 = "Quebec"
sub_folder_2 = Left(proj_folder, 3)

Set objNS = Application.GetNamespace("MAPI")

Set projectParentFolder = objNS.Folders("Public Folder - [email protected]").Folders("All Public Folders").Folders(sub_folder1).Folders(sub_folder2)
'=============THIS IS THE PART WHERE I WOULD LIKE TO IMPROVE THE SPEED==============================
For intX = 1 To projectParentFolder.Folders.Count                   'searching for folder name beginning
    If Left(projectParentFolder.Folders.item(intX).Name, Len(proj_folder)) = proj_folder Then
        Set objFolder = projectParentFolder.Folders.item(intX)
        Exit For
    End If
Next
objX.Move objFolder                         'moving mail to objFolder

Set objX = Nothing
Set objFolder = Nothing
Set projectParentFolder = Nothing
Set objNS = Nothing

End Sub

Answer 1

You have too many dots in the loop for one. Here's a general VBA speed post of mine.

When setting properties or calling methods, each is a function call in the CPU. That means stack setup overhead. Function calls are slower than inline code. Use loops rather than functions in VBA for the same reason.

For a start don't specify all those properties over and over again. Unless you change them they don't change.

With Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchByte = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = False
    .MatchFuzzy = False

    For loop to go through each word pair
        .Text = SrcText
        .Replacement.Text = DestText
        .Find.Execute Replace:=wdReplaceAll
    Next

End With

Minimise Dots

So if you are interested in performance minimise dots (each dot is a lookup), especially in loops.

There are two ways. One is to set objects to the lowest object if you are going to access more than once.

eg (slower)

set xlapp = CreateObject("Excel.Application")
msgbox xlapp.worksheets(0).name 

(faster because you omitt a dot every time you use the object)

set xlapp = CreateObject("Excel.Application")
set wsheet = xlapp.worksheets(0)
msgbox wsheet.name

The second way is with. You can only have one with active at a time.

This skips 100 lookups.

with wsheet
For x = 1 to 100
 msgbox .name
Next
end with

String Concatination

And don't join strings one character at a time. See this from a VBScript programmer. It requires 50,000 bytes and many allocation and deallocation to make a 100 character string.

http://blogs.msdn.com/b/ericlippert/archive/2003/10/20/53248.aspx

Reading Properties

Don't reread properties that don't change especially if out of process or late bound. Put them into a variable. Reading variables is quick compared to an object(s) lookup (which is also a function call or at least two if late bound) and then a function call.

Variables

Constants and Literals are pretty much the same once compiled.

Const x = 5
msgbox x

is the same as

msgbox 5

Literals are inserted direct in code. String and object variables have managers, incuring overhead. Avoid creating variables for no reason. This is an example of a pointless and slow variable.

x = "This is a string"
msgbox x

compared to

const x = "This is a string"
msgbox x

or

msgbox "This is a string"

Object Types

Two concepts here - in or out of process and early or late binding.

exefiles are connected to out of process. All calls are marshalled over RPC (a networking protocol). Dllfiles are in process and function calls are made direct with a jump.

Early binding is set x = objecttype. Functions are looked up when you write the program. On execution the program is hard coded to jump to address stored in the vtable for that function.

Late binding is set x = createobject("objecttype"). Each function call goes like this. "Hi object do you have a print command". "Yes", it replies, "command number 3". "Hi object can you please do command number 3". "Sure, here's the result".

From Visual Basic Concepts (part of Help)

You can make your Visual Basic applications run faster by optimizing the way Visual Basic resolves object references. The speed with which Visual Basic handles object references can be affected by:

Whether or not the ActiveX component has been implemented as an in-process server or an out-of-process server.

Whether an object reference is early-bound or late-bound. In general, if a component has been implemented as part of an executable file (.exe file), it is an out-of-process server and runs in its own process. If it has been implemented as a dynamic-link library, it is an in-process server and runs in the same process as the client application.

Applications that use in-process servers usually run faster than those that use out-of-process servers because the application doesn't have to cross process boundaries to use an object's properties, methods, and events. For more information about in-process and out-of-process servers, see "In-Process and Out-of-Process Servers."

Object references are early-bound if they use object variables declared as variables of a specific class. Object references are late-bound if they use object variables declared as variables of the generic Object class. Object references that use early-bound variables usually run faster than those that use late-bound variables.

Excel Specific

See this link from a Microsoft person. This is excel specific rather than VBA. Autocalc and other calc options/screenupdating etc.

http://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/

.

Edit

I don't have Outlook installed so something like this.

Removed dots, changed to an enumerated For Each, and moved the len function outside the loop so it's not called over and over again.

Set projectParentFolder = objNS.Folders("Public Folder - [email protected]").Folders("All Public Folders").Folders(sub_folder1).Folders(sub_folder2)

prog_folder_len = Len(Prog_Folder)

For Each Fldr in ProjectParentFolder.Folders
    If Left(Fldr.Name, prog_folder_len) = proj_folder Then
        Set objFolder = Fldr
        Exit For
    End If
Next

A lot of collections can be accessed by name.

Does something like set objfolder = Fldr(Prog_Folder) or set objfolder = Fldr.item(Prog_Folder) not work?

Collections are implemented by the object. So one cannot know without installing the object the capabilities of a collection.

Also unlike For x = n to n, For each is also object implemented and might be faster than For x = n to n.

Answer 2

For the messages, you can use MAPIFolder.Items.Find/FindNext/Restrict or MAPIFolder.GetTable to find an item using a custom condition. Unfortunately there is nothing like that for the MAPIFolder.Folders collection in the Outlook Object Model - it was assumed that the number of subfolders is always small.

The best you can do with Outlook Object Model is pass the full name of the child folder to Folders.Item() - if there is an exact match (case insensitive), MAPIFolder.Folders.Item() will be able to return it without looping through all subfolders.

If you need a substring (or any other) match, you can either switch to Extended MAPI (C++ or Delphi only, not an option in VBA) and use MAPIFolder.Folders.RawTable to retrieve the IMAPITable MAPI interface that you can use to search for a subfolder. Or you can use Redemption (I am its author) and its MAPITable object. Your code would look something like the following:

set Table = CreateObject("Redemption.MAPITable")
Table.Item = projectParentFolder.Folders
Set Recordset = Table.ExecSQL("SELECT EntryID from Folder where Name like '" & proj_folder & "%' ")
If not Recordset.EOF Then
  strEntryID = Recordset.Fields(0).Value
  set objFolder = Application.Session.GetFolderFromID(strEntryID)
end If

Redemption version of the folder object (RDOFolder) also exposes Folders.Find/FindNext and Folders.Restrict methods (similar to the methods exposed by the Items collection in Outlook) that allow to specify an arbitrary search clause:

set Session = CreateObject("Redemption.RDOSession")
Session.MAPIOBJECT = Application.Session.MAPIOBJECT
set Folder = Session.GetFolderFromID(Application.ActiveExplorer.CurrentFolder.EntryID)
set subFolder = Folder.Folders.Find("Name LIKE 'MAPI%'")
if subFolder Is Nothing Then
  MsgBox "No such subfolder"
else
  MsgBox "Found subfolder named '" & subFolder.Name & "'"
end if