Is there a way to get the enums in VBA?

Question

Is there a way to get the enums in VBA? Something like this example for C#, but for VBA?

using System;

class EnumsExampleZ
{
    private enum SiteNames
    {
        SomeSample = 1,
        SomeOtherSample = 2,
        SomeThirdSample = 3
    }

    static void Main()
    {
        Type enumType = typeof(SiteNames);
        string[] enumName = enumType.GetEnumNames();

        for (int i = 0; i < enumName.Length; i++)
        {
            Console.WriteLine(enumName[i]);
        }
    }
}

Lets say we have the following:

Enum FruitType
    Apple = 1
    Orange = 2
    Plum = 3
End Enum

How can we display on the immediate window these:

Apple
Orange
Plum

Answer 1

If the reason you're looking for enum names is because you mean to use them in a user interface, know that even in C# that's bad practice; in .net you could use a [DisplayAttribute] to specify a UI-friendly display string, but even then, that's not localization-friendly.

In excel-vba you can use Excel itself to remove data from your code, by entering it into a table, that can live in a hidden worksheet that can literally act as a resource file:

Then you can have a utility function that gets you the caption, given an enum value:

Public Enum SupportedLanguage
    Lang_EN = 2
    Lang_FR = 3
    Lang_DE = 4
End Enum


Public Function GetFruitTypeName(ByVal value As FruitType, Optional ByVal langId As SupportedLanguage = Lang_EN) As String
    Dim table As ListObject
    Set table = MyHiddenResourceSheet.ListObjects("FruitTypeNames")
    On Error Resume Next
    GetFruitTypeName = Application.WorksheetFunction.Vlookup(value, table.Range, langId, False)
    If Err.Number <> 0 Then GetFruitTypeName = "(unknown)"
    Err.Clear
    On Error GoTo 0
End Function

Or something like it. That way you keep code with code, and data with data. And you can quite easily extend it, too.

Answer 2

There is no built-in function, though it is easy enough to roll your own in a concrete case:

Enum FruitType
    Apple = 1
    Orange = 2
    Plum = 3
End Enum

Function EnumName(i As Long) As String
    EnumName = Array("Apple","Orange","Plum")(i-1)
End Function

If you have several different enums, you could add a parameter which is the string name of the enum and Select Case on it.

Having said all this, it might possible to do something with scripting the VBA editor, though it is unlikely to be worth it (IMHO).