Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Iterating Office Enumeration Constants

aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Published:

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"

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

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

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

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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
4
3,419 Views
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.