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.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.
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 = oDicEnd Function
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) NextEnd Sub
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.
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.
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!