Enum Name Value List

This page describes how to get a list of value names from an Enum definition.

Introduction

We have discussed the value of Enum definitions elsewhere on this site, including a discussion of how they help document the code. However, in VBA, there is no way to ensure that a valid value is assigned to a variable declared as an enum type. For example, if we have the following code:

Public Enum MySizeEnum
    Small = 0
    Medium = 1
    Large = 2
End Enum

Dim ShirtSize As MySizeEnum

There is no way to ensure that a valid value is assigned to ShirtSize. (It is worth noting that this has been partially remedied in VBNET with the IsDefined member of the Enum object.) It is perfectly legal, and no run-time or compiler error will be raised, to assign 1234 to the ShirtSize variable. As most code that uses enums relies on the variable having a valid value, a Select Case statement is often used to validate the value. Something like the following is often used:

Select Case ShirtSize
    Case Small, Medium, Large
        ' OK
    Case Else
        ' error code here
End Select

This code can be tedious to write, especially if the enum definition has a large number of names and is not located near the validation code. The code presented on this page creates a comma-separated list of enum value names for a selected enum and puts that list in the Windows clipboard. This is suitable for pasting into a Select Case statement. For example, using the code above, if the cursor is anywhere within the MySizeEnum definition, the code will create the following text and put it in the clipboard:

    Small, Medium, Large

When you run the code, the cursor must be within the enum definition, between the Enum and End Enum statements. If the cursor is not within the enum definition, the behavior is unpredictable.

The Code

The code requires two references. The first is to the Microsoft Visual Basic For Application Extensibility Library 5.3 (VBIDE) and the second reference is to the Microsoft Forms 2.0 Object LibraryMicrosoft Forms 2.0 Object Library (MSFORMS). You can add these references from the References dialog box on the Tools menu in the VBA editor. If you add a UserForm to your project, the reference to the MSForms library is automatically added. The code for the EnumValueNamesToClipboard is shown below.

Sub EnumValueNamesToClipboard()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' EnumValueNamesToClipboard
' This creates a text string of the comma separated value names of an
' Enum data type. Put the cursor anywhere within an Enum definition
' and the code will create a comma separated string of all the
' enum value names. This can be used in a Select Case for validating
' values passed to a function. If the cursor is not within an enum
' definition when the code is executed, the results are unpredictable.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim N As Long
Dim Txt As String
Dim S As String
Dim SL As Long, EL As Long, SC As Long, EC As Long
Dim DataObj As MSForms.DataObject
If Application.VBE.ActiveCodePane Is Nothing Then
    Exit Sub
End If
Application.VBE.ActiveCodePane
    .GetSelection SL, SC, EL, EC
    With .CodeModule
        S = .Lines(SL, 1)
        Do Until InStr(1, S, "enum ", vbTextCompare) > 0
            N = N + 1
            S = .Lines(SL - N, 1)
        Loop
        N = SL - N + 1
        S = .Lines(N, 1)
        Do
            S = .Lines(N, 1)
            If InStr(1, S, "end enum", vbTextCompare) = 0 Then
                Txt = Txt & " " & Trim(S) & ","
            End If
            N = N + 1
        Loop Until InStr(1, S, "end enum", vbTextCompare) > 0
    End With
End With

With New MSForms.DataObject
    .SetText Left(Txt, Len(Txt) - 1)
    .PutInClipboard
End With
End Sub

You can download the file with all the example code on this page.

This page last updated: 10-September-2010.