If you define a variable (or other declaration) as having the value type of an Enum and then want to get the value (i.e. the Enum member) that has been assigned to it, you will just get the numerical value – there is no simple way to get the name of the Enum member. In other words, if you have this Enum …
… and then run this code …
… then "1" will be sent to the Immediate window (as the default Enum member numbering is 0-based) which isn't ideal unless you can remember the exact order of each Enum member. What if you wanted the actual name of the Enum member assigned to the variable (i.e. "FRUIT_ORANGE" in this case)? You would need something like this:
… and then change the Debug.Print line to …
… then "FRUIT_ORANGE" will be sent to the Immediate window which is much clearer as to its meaning. As typing out the equivalent of GetFruitAsString() is time consuming, then use the 'Add a "GetEnumAsString()" Function' provided by VBE_Extras to add it for you.
The 'Add a "GetEnumAsString()" Function' works not only for Enums defined in your VBA code but also for Enums defined in Type Libraries that, either, the VBA Project has a Project Reference to or that are 'referenced' using special binding.
Structure of the GetEnumAsString() Function
Some Enums are designed so that multiple Enum members can be combined (sometimes called 'composite' Enums). An example is VbFileAttribute which can combine a series of file attributes that can be applied to a file, for example a file can be both vbReadOnly and vbHidden. Such Enums have members with values that are 'bit flags' i.e. they are all powers of 2 (optionally with an Enum member with a value of 0 … in the case of VbFileAttribute, that is vbNormal).
The 'Add a "GetEnumAsString()" Function' recognises the 'composite' nature of the Enum and adds a Function with a different structure so that all Enum members (that are passed in to the 'value' parameter) are returned.
An example of an Enum that is not 'composite' is VbDayOfWeek (its Enum members have sequential values, from 0 to 7) for which 'Add a "GetEnumAsString()" Function' will add:
… and the returned String will be the name of the one Enum member that is assigned to your variable and passed in as the 'value' parameter.
An example of an Enum that is 'composite' is VbFileAttribute (its Enum members all have values that are powers of 2, plus one that has a value of 0) for which 'Add a "GetEnumAsString()" Function' will add:
… and the returned String will be the names of all of the Enum members that are assigned to your variable and passed in as the 'value' parameter.
Note that:
An Enum will be identified as being 'composite' if it includes 3 or more Enum members with non-0 values that are all powers of 2 and no Enum members with values that are not powers of 2 (or are not 0).
Some Enums are 'partially composite', for example VbVarType has values that are mostly sequential (e.g. vbEmpty which is 0, vbLong which is 3, and vbByte which is 17) but then has a 'composite' Enum member (which has a value that is a power of 2), being vbArray which is 8192. VBE_Extras will identify this (and other similar Enums that contain any value that is not a power of 2) as being 'not composite' and will add a Function with the structure of the first of the above two examples.
For Enums defined in VBA code, when working out whether the Enum is 'composite' or not, the value assigned to the Enum member will not (if it is an expression) be evaluated … only Enums for which all members are assigned an explicit numeric value will be considered as being possibly 'composite'
Using the Enum members name or value
When adding a Function, whether the Enum members names or values are used (in the Case or If statement, as appropriate for whither the Enum is 'composite' or not) depends on whether the source of the Enum is:
A Type Library:
If the VBA Project has a Project Reference to the relevant Type Library, you will be given the choice of using the Enum member names or the Enum member values (the latter being better if you may subsequently revert to using late binding for the relevant Type Library)
If the VBA Project does not have a Project Reference to the Type Library (e.g. if you are using special binding), the Enum member values will always be used
Your VBA Project:
The Enum member names are always used
When the Enum member names are used, whether they are prefixed with the Enum name † is defined by the 'Auto Text uses qualified names' setting (in the 'Other' tab of the Settings). For example, this is a line from the Function that uses the Enum member names prefixed with the Enum name:
This is the same line but using the Enum member name alone:
And this is the same line but using the Enum member value:
Other info
The name of the Function that is added to the code is determined by the name of the Enum ... it is always GetXxxAsString() where Xxx is the name of the Enum. If an existing Function with that name already exists then it will be updated - so if the Enum is one that is defined in your VBA code and if you update it (e.g. adding, removing, re-ordering or otherwise changing Enum members) then you can just do 'Add a "GetEnumAsString()" Function' again to update the existing Function to match the updated Enum.
Click this link to learn more about, and optionally install, VBE_Extras.
† some Enums in some Type Libraries use an 'Alias' for their name ... Enum members cannot be prefixed with such 'aliased' Enum names (they are not recognised by the VBE and will result in compile-time errors) and so the name of the Library itself is used in the GetEnumAsString() Function. Examples of 'aliased' Enums (these from Microsoft Scripting Runtime) are DriveTypeConst, FileAttribute, SpecialFolderConst and StandardStreamTypes (but other Enums in the same Type Library are not 'aliased': CompareMethod, IOMode and Tristate). For anyone reading this that is interested (!), you can see the actual name 'in plain view':
In your VBA Project, add a Project Reference to the Microsoft Scripting Runtime, then
Switch on 'show hidden members', and
Within a procedure or property, type Scripting (i.e. the 'program name' for the Microsoft Scripting Runtime) then '.' and then, in the VBE's Intellisense popup, scroll down to see the names of the members between 'IOMode' and 'Normal' ...
The names such as "__MIDL___MIDL_itf_scrrun_0000_0000_0001" are the actual Enum names … and these names are legal code when surrounded with square brackets which the VBE will add for you …
To show that these are the actual names (and are legal code), if you select one in the Intellisense popup to add it to your VBA code and then, after it, type another '.' you will see the Enum member names listed