I was part of a small discussion this morning regarding VBA constants which made me create today’s article.

The more you do Late Binding coding, which IMHO you should always do!, the more you need to use constants for the given application you are automating.

Now, obviously, if you are merely coding a simply procedure, you can quickly open the other application and retrieve the constant you need from the VBE Object Browser. That said, if you get into complex automation and require a lot of the constants, wouldn’t it be nice to simply load a module with them all in one shot and not need to waste your time copying back and forth countless constants?! Lucky for you, it is possible and I’m going to show you just how easy it is to compile such a list.

Generate a List of VBA Constants

The key to the process below is you need to know that we can use Type Information Library (tlbinf32.dll) to query an application to retrieve, amongst other things, their constants. Furthermore, depending on the application, this information is stored in different files, so it can be found in the program’s:

• exe (Excel)
• olb (Access, Outlook, PowerPoint, Word)
• dll (Office)

Enough talk!  Below is a procedure that you can call to extract a list of constants for the specified Application which get written to a text file that you can then import into any VBA project.

'---------------------------------------------------------------------------------------
' Procedure : GenerateListConstants
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Extract a listing of all the constants for a given program
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
' Dependencies: Requires a copy of Txt_Append()
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sProgramName  : Name of the program to generate the constant listing of.
'                 Access, Excel, Office, Outlook, PowerPoint, Word
' sOutputFile   : Fully qualified path and filename to export the constants to.  If
'                 ommitted, it will be created on the desktop using the name of the
'                 program.  Access  => Access_VBAConstants.txt
'                           Excel   => Excel_VBAConstants.txt
'                           ...
'
' Usage:
' ~~~~~~
' GenerateListConstants("Access")
' GenerateListConstants("Excel", "C:\ExcelConstants.txt")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-10-06              Initial Public Release
'---------------------------------------------------------------------------------------
Public Sub GenerateListConstants(sProgramName As String, _
                                      Optional sOutputFile As String = "")
    Dim oConstant             As Object    'As TLI.ConstantInfo
    Dim oMember               As Object    'As TLI.MemberInfo
    Dim sProgramBasePath      As String
    Dim sProgramPath          As String
    Dim sFile                 As String
    Dim sOutput               As String
    Const sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\" & _
                    "CurrentVersion\App Paths\MSACCESS.EXE\Path"
                    
    On Error GoTo Error_Handler

    sProgramName = StrConv(sProgramName, vbProperCase)
    If sOutputFile = "" Then
        sOutputFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & _
                      sProgramName & "_VBAConstants.txt"
    End If

    sProgramBasePath = CreateObject("WScript.Shell").RegRead(sRegKey)

    Select Case sProgramName
        Case "Access"
            sProgramPath = sProgramBasePath & "Msacc.olb"
        Case "Excel"
            sProgramPath = sProgramBasePath & "Excel.exe"
        Case "Office"
            sProgramPath = Environ("CommonProgramFiles(x86)") & "\Microsoft Shared\OFFICE" & _
                           Int(Application.Version) & "\MSO.dll"
        Case "Outlook"
            sProgramPath = sProgramBasePath & "MSOutl.olb"
        Case "PowerPoint"
            sProgramPath = sProgramBasePath & "MSPpt.olb"
        Case "Word"
            sProgramPath = sProgramBasePath & "MSWord.olb"
        Case Else    'Can't continue, unknow request
            MsgBox "Unrecognized program '" & sProgramName & "'.", _
                   vbCritical Or vbOKOnly, "Operation aborted"
            GoTo Error_Handler_Exit
    End Select

    sOutput = "'Generated lisiting of " & sProgramName & " VBA constants" & _
              vbNewLine & "'Generated " & Format(Now(), "yyyy-mm-dd hh:nn") & _
              " based on Office " & Application.Version

    With CreateObject("TLI.typelibinfo")
        .ContainingFile = sProgramPath
        For Each oConstant In .Constants
            sOutput = sOutput & vbNewLine & "'" & oConstant.Name
            sOutput = sOutput & vbNewLine & "'" & String(79, "*")

            For Each oMember In oConstant.Members
                sOutput = sOutput & vbNewLine & "Public Const " & oMember.Name & " = " & oMember.Value
            Next oMember
        Next oConstant
    End With

    Call Txt_Append(sOutputFile, sOutput)

Error_Handler_Exit:
    On Error Resume Next
    If Not oMember Is Nothing Then Set oMember = Nothing
    If Not oConstant Is Nothing Then Set oConstant = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GenerateListConstants" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Do note this procedure requires a copy of the VBA – Append Text to a Text File function as it is used to create the actual text file.