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