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.
ActiveDocument.Styles("Normal").ParagraphFormat.LineSpacingRule = "wdLineSpaceMultiple"
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.
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.ScriptCont
rol 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.
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:
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:
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.
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(x86)=C:\Program Files (x86)
For Each vProgFile In Array("ProgramFiles", "ProgramFiles(x86)", "ProgramW6432")
strProgFiles = Environ(vProgFile)
If Len(strProgFiles) <> 0 Then
'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
If boolOLBFound Then
MsgBox "Unsupported Office version"
Set GetOLBenums = Nothing
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
Set GetOLBenums = oDic
You would initialize a dictionary object with some code like this:
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)
Stepping through the For Each loop a few times, produces the following in an Excel environment:
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.
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.
= C:\Program Files
= C:\Program Files (x86)
= 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.
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
Here is a helpful MSDN article on the Typelib Information library, albeit a little out of date.
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.
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!