Iterating Office Enumeration Constants

Introduction

In his recent article, http:A_13877.html , DrTribos faced the problem of assigning MS Word property values with a string variable equal to the name of one of the enum constants.
Example:

ActiveDocument.Styles("Normal").ParagraphFormat.LineSpacingRule = "wdLineSpaceMultiple"

Select all Open in new window

His solution involved a lot of copy/paste operations and some nifty code that created a function with a big Select Case statement for performing the look-up (translation) of the character string into the numeric value of the instrinsic enumeration item.

This is a partial solution with some limitations that DrTribos details in the article.

This article describes a more complete, more automated, and faster performing solution to the problem.
 

Problem Definition

You may face this same problem under the following circumstances

• You read values in from a text file that uses the names of the properties/constants
• You inherit legacy code with strings
• You initially developed the code in a VB6 or VBA environment and are moving to VBScript
• You initially used early binding for development and need to use late binding for production

 

What does not work

As the original author stated, the obvious statements do not work. In addition to those described in the prior article, I can also state that using a MSScriptControl.ScriptControl object will not get around the nature of these application enumerated constants. Thinking I might get lucky, I even tried using the CallByName() function, with equally poor success.
 

The Best Solution

There are actually two solutions to this problem. I will show you the first one and save the second one for a separate article. Rather than create a large Select Case statement, this solution will populate a dictionary object.
 
The Magic Sauce that makes this possible is a Microsoft DLL, tlbinf32.dll, which contains the TypeLib Information Library object.
http://msdn.microsoft.com/en-us/magazine/bb985086.aspx
 
The first difficult part will be to find a reliable download source. After some searching on the Microsoft site, I settled for this download site:
http://www.dll-files.com/dllindex/dll-files.shtml?tlbinf32
 
Before I put this in my system, I found some hashes to compare against my version of the dll. I used Microsoft's FCIV utility to display both the MD5 and SHA1 hashes for the tlbinf32.dll and they matched with values at this anti-malware site:
http://www.herdprotect.com/tlbinf32.dll-e00c50f66358a5c71c97b92adb1225fa7ef722b2.aspx
 
You will need to elevate your priviledges to administrator, copy the dll to the system32 directory, and run the regsvr32 utility to register the object.
 
The code is written to run in Office projects (Access, Excel, Graph, Office, Outlook, Powerpoint, Word) from 2003 to 2013.  This function will use the TypeLibe Information object to extract all the constants, and their associated values, from the .OLB file for the currently running application. The name/value pairs are added to a dictionary object, which is returned to the invoking routine.
Note: The Graph application is only available in Office 2003.
 

Public Function GetOLBenums() As Object
    Dim oTLI As New TLI.TypeLibInfo
    Dim sText As String
    Dim strProgFiles As String
    'Set oTLI = CreateObject("TLI.TLIApplication")
    Dim oConstantsGroup, oEnum
    Dim oDic As Object
    Dim DicAppLibs As Object
    Dim strOfficePath As String
    Dim vItem As Variant
    Dim vProgFile As Variant
    Dim oFS As Object
    Dim boolOLBFound As Boolean
    
    Set oFS = CreateObject("scripting.filesystemobject")
    Set DicAppLibs = CreateObject("scripting.dictionary")
    Set oDic = CreateObject("scripting.dictionary")
    oDic.CompareMode = vbTextCompare        '=1
    
    DicAppLibs("Microsoft Access") = "Msacc.olb"
    DicAppLibs("Microsoft Excel") = "EXCEL.EXE"
    DicAppLibs("Microsoft Graph") = "Graph.exe"
    DicAppLibs("Microsoft Office") = "MSO.dll"
    DicAppLibs("Microsoft Outlook") = "MSOutl.olb"
    DicAppLibs("Microsoft PowerPoint") = "MSPpt.olb"
    DicAppLibs("Microsoft Word") = "MSWord.olb"
    
    'Different %ProgramFiles% environment variables in different Windows versions
    'ProgramFiles=C:\Program Files
    'ProgramFiles(x86)=C:\Program Files (x86)
    'ProgramW6432=C:\Program Files
    For Each vProgFile In Array("ProgramFiles", "ProgramFiles(x86)", "ProgramW6432")
        strProgFiles = Environ(vProgFile)
        If Len(strProgFiles) <> 0 Then
            Exit For
        End If
    Next
    
    'Different Office OLB paths
    'Office 2003 C:\Program Files\Microsoft Office\Office11
    'Office 2007 C:\Program Files\Microsoft Office\Office12
    'Office 2010 C:\Program Files\Microsoft Office\Office14
    'Office 2013 C:\Program Files\Microsoft Office\Office15
    'Office 2013 C:\Program Files\Microsoft Office15\root\Office15
    'Note: last one after Office365 installation
    boolOLBFound = False
    For Each vProgFile In Array("Microsoft Office\Office%ver%", "Microsoft Office%ver%\root\Office%ver%")
        strOfficePath = Replace(vProgFile, "%ver%", Int(Application.Version))
        strOfficePath = strProgFiles & "\" & strOfficePath & "\" & DicAppLibs(Application.Name)
        If oFS.fileexists(strOfficePath) Then
            boolOLBFound = True
            Exit For
        End If
    Next
    If boolOLBFound Then
    Else
        MsgBox "Unsupported Office version"
        Set GetOLBenums = Nothing
        Exit Function
    End If
        
    On Error Resume Next
    'Get information from the current application library (currently executing version)
    oTLI.ContainingFile = strOfficePath
    For Each oConstantsGroup In oTLI.Constants
        For Each oEnum In oConstantsGroup.Members
            oDic(oEnum.Name) = oEnum.Value
        Next
    Next
    Set GetOLBenums = oDic
End Function

Select all Open in new window

You would initialize a dictionary object with some code like this: 

Sub testOLBenums()
    Dim AppEnums As Object
    Dim vItem As Variant
    Set AppEnums = GetOLBenums
    'For demonstration purposes only
    'Comment or delete the following For Each loop in production
    For Each vItem In AppEnums
        Debug.Print vItem, AppEnums(vItem)
    Next
End Sub

Select all Open in new window

Stepping through the For Each loop a few times, produces the following in an Excel environment: 

xlAll       -4104
xlAutomatic -4105
xlBoth      1
xlCenter    -4108
xlChecker   9
xlCircle    8
xlCorner    2

Select all Open in new window

Once you have populated a dictionary object, you can quickly get the associated numeric value for your string value. The extra benefit to using the dictionary object is that you can use its .Exists() method to check on the existence of the string value key before you try and retrieve the numeric value. If the string (key) value does not exist, you might return some default value, some 'not found' value, raise an error, or take some other action.
 

Code Notes:

Rather than hard-code the path names, it is better to use the %ProgramFiles% environment variable.  There are different %ProgramFiles% environment variables in different Windows versions, so the code iterates through these variations.
ProgramFiles = C:\Program Files
ProgramFiles(x86) = C:\Program Files (x86)
ProgramW6432 = C:\Program Files

Also, there are different paths down to the Office directory tree to the location of the OLB files.  The last one in the list appeared after an Office365 installation.

Examples:

Office 2003 C:\Program Files\Microsoft Office\Office11
Office 2007 C:\Program Files\Microsoft Office\Office12
Office 2010 C:\Program Files\Microsoft Office\Office14
Office 2013 C:\Program Files\Microsoft Office\Office15
Office 2013 C:\Program Files\Microsoft Office15\root\Office15

Further reading:
Here is a helpful MSDN article on the Typelib Information library, albeit a little out of date.
http://msdn.microsoft.com/en-us/magazine/bb985086.aspx
 

Performance

My systems programming background always leads me to include performance data in my articles, when applicable.  In this case, populating the dictionary object (Set AppEnums = GetOLBenums) only takes 0.484 seconds for 1544 Excel enum entries (name/value pairs).
 

Distributing the enums for your Office apps

Since this solution entails the installation, or at least registry, of a dll, it might be easier to enumerate these name/value pairs and persist them to a server, where your Office application code could read the data from a file, populating the dictionary object.
 

What's next?

Although this code does work and gives a portable solution, it isn't perfect.  The reader will need to find and register a dll on any system where the code will run.  Subsequent article(s) will show a VBA programmatic approach to getting this data that does not require any DLL or third-party software installation.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.
 
If you found this article helpful, please click the Yes button near the:
 
      Was this article helpful?
 
label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=