Introduction
Enumerations are an important resource for developers and are included in many development languages. The VBA language supports enumerations but only provides access to member values.
It is the intention of this article to add a measure of detail to all user defined enumerations through the creation of an enumeration database.
Overview
This project creates a database of user defined enumerations in the active VBA project. Individual enumerations, entire components, or all user defined enumeration can be extracted and added to the database.
The enumeration database is developed through real-time parsing of a project's source code. Once developed, the database can either be saved and loaded from a datafile, or real-time parsing can continue until a project is protected.
A developed database has the ability to return member names and comments by member value. Member lists can be formatted for dropdowns and the database can return the Min-Max values of an enumeration.
The enumeration data is stored in a non-binary, ANSI format, and is easy to reuse. Also, a generic data viewer has been included to allow database elements to be viewed in a text editor.
Requirements
With the exception of gaining access to the source code in your project and some file I/O, the enumeration database is plain vanilla VBA. The only real requirement being that any targeted Office application must support the VBE interface.
Officially, the enumeration database supports MS Office version 2003 or later as that is the earliest version which I can test. If you discover that it can be used with other versions of Office, please let me know and I will consider updating the requirement.
While it is not a requirement, it could prove useful to download the MProject module from my article entitled "Deconstruction of a VBA Code Module". The module creates a backup of your work and can offer some clarity to a codebase.
By Example
With respect to the amount of documentation in this article, and at the end of the day, this database is very easy to use. In keeping with that premise, a short example of a database conversation can sometimes speak a thousand words.
Please note that this is simply an example. In practice, the conversation would be at its best when the functionality is separated and designed with a much broader scope in mind.
Also note that the conversation could potentially shell two instances of the system assigned text editor. One instance for viewing the parser Lookup report and one instance for viewing the currently loaded data.
Public Sub Conversation()
Dim EBase As CEnumBase
Set EBase = New CEnumBase
EBase.DebugOn = True
EBase.ReportOn = True
EBase.AddAll
Debug.Print "EnumCount : " & CStr(EBase.ECount)
EBase.ToFile
Debug.Print "Datafile : " & EBase.Last(LastType_Path)
Call EBase.Data(True, True)
Dim TheEnum As String
TheEnum = "MEnumBase.MinMaxEnum"
Debug.Print "MemberName : " & EBase.Parse(TheEnum, MinMax_Both)
Debug.Print "MemberList : " & EBase.Members(TheEnum)
Set EBase = Nothing
End Sub
Procedures
Public
| | Add | | Add individual components or a targeted enumeration |
| | AddAll | | Add all enumerations in the active project |
| | CCount | | Count of loaded components |
| | Components | | List of loaded components |
| | Data | | The enumeration database |
| | DataLen | | Length of the enumeration database |
| | DebugOn | | Enable or disable Debug.Print messages |
| | ECount | | Count of loaded enumerations |
| | Enumerations | | List of loaded enumerations |
| | FromFile | | Load the database from a file |
| | Last | | Last error, message, and path |
| | LookupOn | | Enable or disable the parser lookup functionality |
| | MCount | | Count of enumeration members |
| | Members | | List of enumeration members |
| | MinMax | | Minimum and maximum values of an enumeration |
| | Parse | | Return a member name or comment using a member value |
| | Project | | Current project properties |
| | Remove | | Remove an enumeration or a component from the database |
| | ReportOn | | Enable or disable parser reporting |
| | ResetData | | Complete reset of the database setting all values to zero |
| | ToFile | | Save the database to a file |
| | Version | | Current database version |
| | View | | Generic data viewer |
Private
| | AddObject | | Base functionality for the Add and AddAll procedures |
| | CreatePath | | Creates a folder path when it does not already exist |
| | | The source code parser |
| | Lookup | | Lookup for member values which are in a constant format |
| | MBlock | | Member block seek |
| | Message | | Generic message trap |
| | ReadWrite | | Generic file read and write procedure |
| | ResetMsg | | Reset last error, message, and path |
| | SortLong | | Quick sort of a one dimensional long array |
| | SortText | | Quick sort of a one dimensional string array |
| | SortTwo | | Quick sort of a two dimensional variant array |
| | SplitPath | | VBA object path splitter |
Workarounds
When working with this codebase and VBA in general, three conditions exist which require proper visibility. The conditions are applicable to all VBA projects, however and in the case of this project, the conditions are amplified.
- VBA does not support the evaluation of a named constant contained in a string variable
- When an Office project is protected or locked for viewing, its source code is inaccessible
- Private enumerations are inaccessible outside of their scope
Both condition one and two have been provided with workaround functionality. Condition one is directly related to the creation of the parser Lookup procedure and condition two is directly related to saving and retrieving an enumeration database from a datafile.
Condition three is by design of the VBA object model and does not have a developable workaround. Although the condition is by design, it still remains significant. Please read the Private Enumerations section for more information.
Creating an Instance
The enumeration database is comprised of two components, both of which are required. CEnumBase is the database and a VBA class object. MEnumBase is a standard module which contains eight support enumerations for the database.
Like other VBA class objects, an instance of the CEnumBase class must be created before it can be used. By the same measure, it should be disposed of when done.
In the following example, the CEnumBase class is declared as a public object. Two procedures are then used, one to create an instance and one to dispose of it.
Public EBase As CEnumBase
Public Sub CreateBase()
Set EBase = New CEnumBase
End Sub
Public Sub DestroyBase()
Set EBase = Nothing
End Sub
Please note that both the CreateBase and DestroyBase procedures are not part of the database. They have been included simply to enhance the concept that the database can be a publically available object. The best practice of where or when a database object should be made available will be up to the developer.
Data Development
The enumeration database is developed by adding user defined enumerations to a database instance.
Currently, two procedures are used when developing a database. The Add procedure is available for adding individual components and targeted enumerations. The AddAll procedure simply adds every user defined enumeration in the active project.
Many enumerations can be parsed and included as is, although, there could be exceptions. To understand the exceptions is to first understand how member values are derived by the parser.
Deriving Member Values
The source code parser is used to disseminate the enumeration data in a code module. During the parsing process, all enumeration member values are determined. The parser will first attempt to derive a member value by source code coercion.
In this codebase, source code coercion is simply having the ability to transform the source code text into numeric value. However, there can be cases when a member value will be in the form of a named constant or an expression, which the parser cannot convert.
Public Enum NameOne
One = 1 Two = 2 [_First] = One Three = (One + Two) End Enum
When the parser cannot convert a member value, it sends the current MemberPath to the Lookup procedure. The Lookup procedure being a simple way to transform a MemberPath string into an actual value.
Select Case MemberPath
Case "MEnums.NameOne.[_First]"
Lookup = CStr(MEnums.NameOne.[_First])
Case "MEnums.NameOne.Three"
Lookup = CStr(MEnums.NameOne.Three)
End Select
The Lookup procedure uses a Select-Case statement which is checked for a matching MemberPath. If a match is found then the associated value is returned to the parser.
The key concept being that when a value cannot be derived by source code coercion, and a value is not returned by a call to the Lookup procedure, the member will be omitted from a database instance. By the same measure, an enumeration must have at least one valid member to be included in a database.
Private Enumerations
The ability to keep objects and data private is an important resource when developing software. However, and in the case of this codebase, a condition can be presented to which there is no developable workaround.
The potential is to have a private enumeration with a member value in a constant format. Source code coercion cannot be used to resolve the value and a lookup mapping will fail as the enumeration member is inaccessible due to its private scoping.
Private Enum NameOne
One = 1 Two = 2 [_First] = One Three = (One + Two) End Enum
The condition is by design of the VBA object model. In cases such as these, functionality changes should be redirected towards an application design perspective, as that is where any changes would need to occur.
Object Paths
Currently, three variations of an object path are used by the procedures in the database. Whenever one of the paths is requested, a string representation of the object path is required, and not an object instance.
The paths have been designed to allow some of the procedures to reuse functionality, and for a uniform approach when using them.
| | ComponentPath | | ComponentName or ComponentName.EnumerationName |
| | EnumerationPath | | EnumerationName or ComponentName.EnumerationName |
| | MemberPath | | ComponentName.EnumerationName.MemberName |
When asked to supply the Full Path to save or load the database from a datafile, please use one of the following two formats...
| | Standard | | Drive:\Folder\FileName.Extension |
| | UNC Path | | \\Server\Folder\FileName.Extension |
While the backslash character is used in the example, the actual folder separator will be system dependent. If needed, the database Project property can be used to return the folder separator for the current system.
Optimistic Parser
The enumeration database uses an optimistic approach to extract data. That is to say, the parser is optimistic that data will be in a predefined format. In this case, the data is comprised of user defined enumerations in a VBA project.
Typically, an optimistic parser can be somewhat inflexible. Data is required to be in a predefined format which implies added maintenance. However, being optimistic can also imply efficiency as the assumptions required to parse a dataset are reduced.
With speed being key, this source code parser requires offloading some of its workload to the developer. The developer workload comes in the form of a consistent enumeration format and In most cases, zero enumeration reformatting will be required.
As such, only a few simple ingredients are required for this optimistic parser to be successful. Follow some basic rules, maintain consistency in enumeration formatting, and then benefit from the effort with some advanced abilities.
Parser Rules
The following three rules must be maintained for all user defined enumeration blocks...
- Declarations, members, and terminating blocks must be separated and on individual lines
- Escaped names and constants cannot contain an equal sign (=) or the apostrophe (') character
- The non-printable characters (2,3,4,5,6,19,20) cannot be used within an enumeration block
If these rules are not maintained then the parser could return a corrupted database. Also note that this parser does not know how to split apart inline enumerations or how to deal with line continuations.
The NameOne enumeration is in the format which is required by the parser. The declaration, terminating block, and each of the members are on their own lines. Hidden members, escaped names, and comments are all valid.
Public Enum NameOne One = 1 [Two] = 2 Three [_First] = One [_Last] = [Three] End Enum
Please note that the example has a declared Public scope. While the parser does not require enumerations to explicitly declare a scope, it is recommended to do so.
Also note that only member in-line comments are retained in the database. All other comments and whitespace lines are stripped during data development.
The following is an example of what NOT to do and will either fail to develop, return a corrupted database, or lead to enumeration members being omitted.
Public Enum [Name [One=Hi] = 1 [Two Three = [TwoEnd Enum
The Database
The enumeration database consists of four primary variables, although the bulk of the actual enumeration data is stored in one non-binary, ANSI formatted string variable. The text is separated by delimiters which can be used to discover record perimeters.
A single enumeration record consists of a parent component name, an enumeration name, and at least one member name along with its value.
When comments are requested to be retained and if a comment is present, a member block will also include a character 19 delimiter followed by the comment. Please note that all values stored in the database are in a hexadecimal format without the standard hex prefix (&H).
Delimiters
The following delimiters are used to create a complete enumeration record. These delimiters cannot be used in any enumeration block which is to be added to a database instance.
| | Chr | Sym | Description |
| | 02 | (  ) | Component name begin |
| | 03 | (  ) | Enumeration name begin |
| | 05 | (  ) | Hex value begin |
| | 06 | (  ) | Member name begin |
| | 19 | (  ) | Comment begin |
| | 05 | ( ) | Member block divider |
| | 04 | (  ) | Enumeration end |
The following standard VBA delimiters are used for initial source code parsing. Once the source code is parsed, the delimiters are then mapped to character 19 and character 06 delimiters in a database instance.
| | Chr | Sym | Description | ToChr | Sym |
| | 39 | (  ) | Standard VBA Comment | 19 | ( ) |
| | 61 | ( = ) | Standard VBA Value | 06 | ( ) |
A character 20 delimiter is used to separate each field in the enumeration datafile and cannot be used in any enumeration block which is to be added to a database instance.
| | Chr | Sym | Description |
| | 20 | (  ) | Field Separator |
Notations
| | a. | | Enumerations are physically in the order in which they were added |
| | b. | | Enumerations are placed end to end |
| | c. | | Enumerations can only be added once per database instance |
| | d. | | Enumerations with zero members are omitted |
| | e. | | Members with unresolved values are individually omitted |
| | f. | | Complete components can be added when they do not already exist in a database |
| | g. | | ComponentName is repeated once for each enumeration |
| | h. | | HexValue is a hexadecimal value stored without the (&H) prefix |
| | i. | | The HexValue-MemberName-Comment segment is repeated for each member |
| | j. | | A Comment segment is only included when a member comment is present and requested |
| | k. | | Only member in-line comments can be retained |
| | l. | | Compiler assigned member values can be used to force a lookup |
| | m. | | Search and replace is case insensitive |
| | n. | | Character sorting is case insensitive |
| | o. | | First character in the database is character 02 |
| | p. | | Last character in the database is character 04 |
Switches
Currently, the enumeration database employs three switches which control both messaging and the parser Lookup functionality. The switches are as follows...
| | DebugOn |
| | Enable or disable Immediate window messaging |
| | Default = False |
| | |
| | This switch allows for database messaging and errors to be displayed in the Immediate window. Whether enabled or not, messaging is always available via the Last property of a database instance. |
| | |
| | LookupOn |
| | Perform lookups while parsing source code |
| | Default = True |
| | |
| | When enabled, the parser will use the Lookup procedure to resolve member values as required. When disabled, the Lookup procedure is not used. Please note that a parser report will not be generated unless both the LookupOn and ReportOn switches are enabled. |
| | |
| | ReportOn |
| | Produces a report of unresolved member values while parsing source code |
| | Default = False |
| | |
| | When the switch is enabled, and only during parsing, enumeration members with unresolvable values are collected in a report. The report contains the exact MemberPath required to create a lookup mapping and can be displayed in the Immediate window or sent to the data viewer. |
Datafile
Whenever an Office project is protected, real-time source code parsing is unavailable. In consequence, the enumeration database can be saved and loaded from a datafile on demand.
The default datafile location is a subfolder at the current location of your Office project file. As such, it should be considered mandatory to move your Office project to its own folder. The default values are as follows...
| | Location | | Folder created at the Office project file location |
| | Folder name | | EBase |
| | Datafile name | | OfficeProjectFilename_ebase.dat |
While the defaults have been provided for consistency and ease of use, any valid folder and file name can be specified. Also note that when required, the class will automatically create the target path when saving a datafile.
Buffer Profile
When profiling this parser and its buffer, it was noted that the buffer threshold was never achieved and additional space allocations were not required. In fact, a negative padding could potentially be employed.
The base model for the buffer is the size of the declarations section of a module, minus the position of the first enumeration found. In most cases, the base model is clearly overstating the initial allocation requirement.
Rather then add a padding switch to the database and modeling the buffer down, the buffer was first examined from an initial allocation speed and overall performance perspective. The determinants are as follows...
- A standard allocation call using the Space$ function is relatively fast. The average time spent allocating up to 300kB was less then 1 millisecond.
- The average speed of parsing a dataload with a final and developed size of 50kb, using 2310 lines of enumerations was 17 milliseconds.
- The largest single bottleneck being the call to the VBE CodeModule object with a local allocation for the requested data. On average, the call takes 2 milliseconds.
The net result is that while the initial buffer allocation is overstated, any performance gains from a succinct buffer would be negligible. The conclusion is weighted by the single-threaded apartment which will be subjected to other performance degradations which are not directly related to a dataload in progress.
Interestingly, the fastest overall load time was always achieved using a datafile. A 50kB, pre-parsed and pre-developed datafile averaged 8 milliseconds to load. Loading a database on demand is attractive from a perspective of consistency in timing, albeit, file system dependent.
Parameter Glossary
| | AllComponents | | All user defined VBA components |
| | CommentDelimiter | | Delimiter used to separate name and comment |
| | ComponentName | | Name of a user defined VBA component |
| | ComponentPath | | ComponentName or ComponentName.EnumerationName |
| | EnumerationPath | | EnumerationName or ComponentName.EnumerationName |
| | ExitOnFirst | | Exit after the first value is found |
| | FormatValue | | Return a numeric value formatted as a Long or Hexadecimal |
| | FullPath | | A path which includes drive, folder, file name and extension |
| | IncludeComments | | Include member inline comments when adding enumerations |
| | IncludeHiddenMembers | | Include hidden members when adding enumerations |
| | LastType | | Class message type (error, message, path) |
| | ListSeparator | | Character which separates list items |
| | MakePath | | Create a path when it does not exist |
| | MemberPath | | ComponentName.EnumerationName.MemberName |
| | PropertyValue | | Project property value type |
| | RemoveCharacters | | Characters which are to be removed from member names |
| | ResetMessages | | Reset the last property containers |
| | ReturnColumns | | Define member columns to return |
| | ReturnField | | Return a member name, comment, or both |
| | ReturnQualified | | Return a component qualified path |
| | ReturnValue | | A single value or multiple values |
| | Sort | | Return a sorted list of records |
| | SortColumn | | Column to sort a list by |
| | SplitRecords | | Split the database records |
| | ToViewer | | Send data to the built-in data viewer |
| | ValueDelimiter | | Character which separates name and value |
Setup
The enumeration database consists of the CEnumBase class and the MEnumBase standard module.
CEnumBase is the database and MEnumBase contains eight support enumerations for the database. Both components are required as the database will not function without its support enumerations.
To include the enumeration database in your office application, please use the following steps...
- Verify that the target Office application supports the VBE interface
- Move your Office project file to its own folder
- Import both CEnumBase and MEnumBase into the Office project
- Compile and save the project.
Please note that it should be considered mandatory to move the Office project file to its own folder. Please see the Datafile section for details.
Version History
| Version | Release Date |
| 1.0 | 2014.01.02 |
- Release Notes - Version 1.0
Distributions
The complete database is contained in the EnumBase.zip distribution. No other files are needed or required to install and use the database. Please note that while offline documentation is being provided, this article will always contain the latest information and distributions.
| | EnumBase.zip | | *required |
| | CEnumBase | | Database class |
| | MEnumBase | | Eight support enumerations |
| |
| | Examples.zip |
| | MCopybook | | Offline documentation |
| | MTest | | Test environment and examples |
Office VBE Support
In order to use the codebase in this project, any targeted Office application must support the VBE interface. The easy way to determine if an Office application supports the interface is to use the following steps...
- Create a new instance of any Office application to be tested
- Open the VBA IDE window of the new instance
- Navigate to the Immediate window and type Application.VBE
The IntelliSense popup window should appear and if VBE is included in the list, then there is a good chance that this project will work in that application and version. If VBE does not appear in the list, then this project will not work in the tested version of the application.
Troubleshooting Tips
If experiencing an issue, two avenues are available to return messages generated by the class. When in a development environment, enable the DebugOn switch. Messaging will then be directed towards the Immediate window.
Messaging is also available via the database Last property. While the Last property has been primarily designed as a code based messaging solution, it still is available at all times. Please note that the property is cleared and reused by subsequent calls to database procedures.
Some other tips which might prove useful...
- Make sure the project compiles without errors
- Verify read-write access is available to the project folder
If required, confirm "Trust access to the VBA project object model" is enabled. In Office 2003, it appears as a Macro Security setting. In Office 2007 and later, it is part of the Trust Center settings. For more information, see Microsoft Knowledge Base article KB282830.
Project References
I often wonder what the development world would have been like without Microsoft's code, and in this case, the VBA team. So giving credit where credit is due, save one, all of links and references in this article belong to Microsoft, along with my thanks.
Final Thoughts
Parsing enumerations can be a significant advancement in any object model. One of the many advantages being a method to reduce duplication. However, it also opens doorways to trace messaging, pre-connectivity logging, and so much more.
In closing, both the documentation and codebase in this article are quite lengthy. If you happen upon something you would like to note, please, try to be constructive as I am available and will respond to all questions.
