Getting The Text Equivalent of An Enumeration

I am trying to create some simple tables in a worksheet that give the numeric equivalents of common enumerations like:

enter image description here

This example is for Border enumerated variables. This material can be found by wandering around in MSDN, but frequently I need to work when I am not online and this type of "help" is not available.

I am currently filling my little table with two separate loops:

Sub trythisB()
    Dim i As Long
    i = 1
    For Each a In Array(xlInsideHorizontal, xlInsideVertical, xlEdgeLeft, xlEdgeRight, xlEdgeBottom, xlEdgeTop)
            Cells(i, 2) = a
            i = i + 1
    Next a
End Sub

Sub trythisA()
    Dim i As Long
    i = 1
    For Each a In Array("xlInsideHorizontal", "xlInsideVertical", "xlEdgeLeft", "xlEdgeRight", "xlEdgeBottom", "xlEdgeTop")
            Cells(i, 1) = a
            i = i + 1
    Next a
End Sub

I would really like to avoid keeping two separate arrays; one for the text strings and the other for the enumerations.

Is there any way to get an enumeration from a text string or, alternatively, convert an enumeration into a text string??

Any suggestions will be appreciated.


  • I don't believe that you need to generate a list like this programmatically in order to create it. Microsoft provides a definition of all of them in MSDN:


    You can download it from there and paste it into your spreadsheet. You could even make it dynamic with a web query so you can stay up to date with any changes. Here is a Macro that does just that:

    Sub GetEnumerationDefinitions()
        ActiveWorkbook.Queries.Add Name:="Enumerations", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://msdn.microsoft.com/en-us/vba/excel-vba/articles/constants-enumeration-excel""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Name"", type text}, {""Value"", Int64.Type}, {""Description"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Enumerations"";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Enumerations]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_0"
            .Refresh BackgroundQuery:=False
        End With
    End Sub