Excel VBA - Set values of Enumerated elements

I quite like ex-man's solution in certain circumstances, for which reason I've upvoted it. The solution more often posited goes along the following lines:

Enum myEnum
  myName1 = 1
  myName2 = 2
  myName3 = 3
End Enum

Function getEnumName(eValue As myEnum)
  Select Case eValue
  Case 1
    getEnumName = "myName1"
  Case 2
    getEnumName = "myName2"
  Case 3
    getEnumName = "myName3"
  End Select
End Function

Debug.Print getEnumName(2) prints "myName2"


As written in the comments, this is not possible. There is possible workaround though that I used in the past. Have:

Private Enum colType
  ID = 1
  SSN = 2
  lName = 3
  fName = 4
End Enum

And then create a separate String property of function such as:

Public Property Get colType_String(colType) as String
  Dim v as Variant
  v= Array("A","B", ...)
  colType_String = vba.cstr(v(colType))
End Property

This is not the most universal solution, but it is easy to implement and it does the job... If you have this in the class module already you can even use property on private colType variable and there is no need to have colType input into the property.


I have been searching for a very long time for the answer to this question. I do not want to have to relist the contents of an Enum in either a Case statement or an array. I couldn't find the answer, but I have managed to do after finding the code somewhere to change Module content. An alteration of that has produced the following working code, to be placed in Module1:

    Option Explicit

    Enum MensNames
        Fred
        Trev = 5
        Steve
        Bill = 27
        Colin
        Andy
    End Enum

    Sub EnumStringTest()
        MsgBox EnumString(Steve) & " = " & Steve
    End Sub

    Function EnumString(EnumElement As MensNames) As String
        Dim iLineNo As Integer
        Dim iElementNo As Integer

        iElementNo = 0
        EnumString = vbNullString
        With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
            ' Find the Enum Start
            For iLineNo = 1 To .CountOfLines
                If InStr(.Lines(iLineNo, 1), "Enum MensNames") > 0 Then
                    Exit For
                End If
            Next iLineNo

            ' Find the required Element
            iLineNo = iLineNo + 1
            Do While InStr(.Lines(iLineNo, 1), "End Enum") = 0 And .Lines(iLineNo, 1) <> ""
                If InStr(2, .Lines(iLineNo, 1), "=") > 0 Then
                    iElementNo = CLng(Mid(.Lines(iLineNo, 1), InStr(2, .Lines(iLineNo, 1), "=") + 1))
                End If
                If iElementNo = EnumElement Then
                    EnumString = Left(Trim(.Lines(iLineNo, 1)), IIf(InStr(1, Trim(.Lines(iLineNo, 1)), " ") = 0, 1000, InStr(1, Trim(.Lines(iLineNo, 1)), " ") - 1))
                    Exit Do
                End If
                iElementNo = iElementNo + 1
                iLineNo = iLineNo + 1
            Loop
        End With
    End Function

Tags:

Enums

Excel

Vba