Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / VBE

VBA Enumeration Database

Rate me:
Please Sign up or sign in to vote.
4.83/5 (8 votes)
8 Jan 2014CPOL18 min read 43.5K   1.5K   13   4
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.

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.

Image 1

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.

VB.NET
Public Sub Conversation()

    'Create a database instance
    Dim EBase As CEnumBase
    Set EBase = New CEnumBase

    'Enable messaging and parser reporting
    EBase.DebugOn = True
    EBase.ReportOn = True

    'Add every enumeration in the active project
    EBase.AddAll
    Debug.Print "EnumCount : " & CStr(EBase.ECount)

    'Save the database to a file
    EBase.ToFile
    Debug.Print "Datafile : " & EBase.Last(LastType_Path)

    'Shell the loaded data to the system assigned text editor
    Call EBase.Data(True, True)

    'Define an enumeration
    Dim TheEnum As String
    TheEnum = "MEnumBase.MinMaxEnum"

    'MemberName by value
    Debug.Print "MemberName : " & EBase.Parse(TheEnum, MinMax_Both)

    'MemberList
    Debug.Print "MemberList : " & EBase.Members(TheEnum)

    'Cleanup
    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
    Extract       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.

  1. VBA does not support the evaluation of a named constant contained in a string variable
  2. When an Office project is protected or locked for viewing, its source code is inaccessible
  3. 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.

VB.NET
'Accessible Object
Public EBase As CEnumBase

'Create a database instance
Public Sub CreateBase()
    Set EBase = New CEnumBase
End Sub

'Destroy the database instance
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.

VB.NET
Public Enum NameOne
    One = 1              'Will derive by coercion
    Two = 2              'Will derive by coercion
    [_First] = One       'Will only derive by lookup
    Three = (One + Two)  'Will only derive by lookup
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.

VB.NET
'Lookup
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.

VB.NET
Private Enum NameOne
    One = 1              'Will derive by coercion
    Two = 2              'Will derive by coercion
    [_First] = One       'Lookup will fail member is private
    Three = (One + Two)  'Lookup will fail member is private
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...

  1. Declarations, members, and terminating blocks must be separated and on individual lines
  2. Escaped names and constants cannot contain an equal sign (=) or the apostrophe (') character
  3. 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.

Required Formatting

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.

VB.NET
Public Enum NameOne       'Declaration
   One = 1                'Standard member
   [Two] = 2              'Escaped member
   Three                  'Compiler assigned member
   [_First] = One         'Hidden member
   [_Last] = [Three]      'Hidden member
End Enum                  'Terminating block

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.

VB.NET
Public Enum [Name'One]    'Escaped apostrophe will fail
   [One=Hi] = 1           'Escaped equal sign will fail
   [Two'There] = 2        'Escaped apostrophe will fail
   Three = [Two'There]    'Escaped apostrophe will fail
End 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.

    Image 2

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
    02Image 3 )Component name begin
    03Image 4 )Enumeration name begin
    05Image 5 )Hex value begin
    06Image 6 )Member name begin
    19Image 7 )Comment begin
    05Image 8 )Member block divider
    04Image 9 )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    
    39Image 10 )Standard VBA Comment        19Image 11 )
    61( = )Standard VBA Value        06Image 12 )

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
    20Image 13 )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...

  1. Verify that the target Office application supports the VBE interface
  2. Move your Office project file to its own folder
  3. Import both CEnumBase and MEnumBase into the Office project
  4. 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
    • Initial Release

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<code>
    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...

  1. Create a new instance of any Office application to be tested
  2. Open the VBA IDE window of the new instance
  3. 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Australia Australia
Creating object models is a wonderfully dynamic process. As components begin to take shape, a model can sometimes reveal capabilities which might not have been imagined when first started.

Comments and Discussions

 
GeneralThis is FANTASTIC!! Thank you. Pin
Member 1126605826-Nov-14 5:02
Member 1126605826-Nov-14 5:02 
GeneralRe: This is FANTASTIC!! Thank you. Pin
Mark Regal26-Nov-14 12:30
Mark Regal26-Nov-14 12:30 
GeneralMy vote of 5 Pin
Dr Bob15-Jan-14 6:25
Dr Bob15-Jan-14 6:25 
GeneralRe: My vote of 5 Pin
Mark Regal15-Jan-14 14:53
Mark Regal15-Jan-14 14:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.