In a Class Module there is:
Private Enum colType
ID = "A"
SSN = "B"
lName = "H"
fName = "G"
End Enum
as a private member. Whenever the class initializes I get the Compile Error: Type Mismatch message. If I declare colType as Private Enum coltype As String. That gets highlighted red as an error and I get the message:
Compile Error: Expected end of statement
Is specifying the values of enumerated elements Unallowed in Excel VBA?
My solution of this looks like this:
Private Enum ColType
ID = 1
SSN = 2
lName = 3
fName = 4
End Enum
Private Function GetEnumName(ByVal value As ColType)
GetEnumName = Choose(value, _
"A", _
"B", _
"H", _
"G" _
)
End Function
Using Choose looks more tidy.
Sample usage: ... = GetEnumName(ColType.ID)
I hope this help
Reference: (Microsoft Visual Basic for Application Extensibility 5.3) is required
Public Enum SecurityLevel
IllegalEntry = 0
SecurityLevel1 = 1
SecurityLevel2 = 3
SecurityLevel3
SecurityLevel4 = 10
End Enum
Public Sub Test1()
Cells.Clear
Range("A1").Value = StrEnumVal("SecurityLevel", SecurityLevel.IllegalEntry)
Range("A2").Value = StrEnumVal("SecurityLevel", SecurityLevel.SecurityLevel1)
Range("A3").Value = StrEnumVal("SecurityLevel", SecurityLevel.SecurityLevel2)
Range("A4").Value = StrEnumVal("SecurityLevel", SecurityLevel.SecurityLevel3)
Range("A5").Value = StrEnumVal("SecurityLevel", SecurityLevel.SecurityLevel4)
End Sub
Public Sub AaaTest2()
Cells.Clear
Dim E As Long
For E = SecurityLevel.IllegalEntry To SecurityLevel.SecurityLevel4
Cells(E + 1, 1) = StrEnumVal("SecurityLevel", E)
Next
End Sub
Function StrEnumVal(BEnumName As String, EnumItm As Long) As String
'''''''''''''''''''''''''
' Fahad Mubark ALDOSSARY'
'''''''''''''''''''''''''
Dim vbcomp As VBComponent
Dim modules As Collection
Dim CodeMod As VBIDE.CodeModule
Dim numLines As Long ' end line
Dim MdlNm As String
Dim lineNum As Long
Dim thisLine As String, SpltEnm As String, EnumITems As String, Itm As String
Dim EEnumName As String
Dim Indx As Long
Dim I As Long, s As Long
Dim SpltEI As Variant
Indx = 0
Set modules = New Collection
BEnumName = "Enum " & BEnumName
EEnumName = "End Enum"
For Each vbcomp In ThisWorkbook.VBProject.VBComponents
'if normal or class module
If vbcomp.Type = vbext_ct_StdModule Then
Set CodeMod = vbcomp.CodeModule
With CodeMod
numLines = .CountOfLines
For lineNum = 1 To numLines
thisLine = .Lines(lineNum, 1)
If InStr(1, thisLine, BEnumName, vbTextCompare) > 0 Then
If InStr(thisLine, ":") > 0 Then
' thisLine = Replace(thisLine, BEnumName & ":", "") ' Remove Enum Titel Enum
thisLine = Right(thisLine, Len(thisLine) - InStr(1, thisLine, ":"))
For s = 0 To UBound(Split(thisLine, ":"))
SpltEnm = Split(thisLine, ":")(s)
If InStr(SpltEnm, " = ") > 0 Then
Itm = SpltEnm
Indx = CDbl(Split(SpltEnm, " = ")(1))
Else
Itm = SpltEnm & " = " & Indx
End If
EnumITems = EnumITems & IIf(EnumITems <> "", vbNewLine, "") & Itm '''''
Indx = Indx + 1
Next
If InStr(1, thisLine, EEnumName, vbTextCompare) > 0 Then
EnumITems = Replace(EnumITems, "End Enum", "")
Exit For
End If
Else
'Only Title show if nothing bedside
End If
ElseIf InStr(1, thisLine, EEnumName, vbTextCompare) > 0 Then
If InStr(thisLine, ":") > 0 Then
For s = 0 To UBound(Split(thisLine, ":"))
SpltEnm = Split(thisLine, ":")(s)
If InStr(SpltEnm, " = ") > 0 Then
Itm = SpltEnm
Indx = CDbl(Split(SpltEnm, " = ")(1))
Else
Itm = SpltEnm & " = " & Indx
End If
EnumITems = EnumITems & IIf(EnumITems <> "", vbNewLine, "") & Itm '''''
EnumITems = Replace(EnumITems, "End Enum", "")
Indx = Indx + 1
Next
Else
End If
Exit For
Else
If InStr(thisLine, ":") > 0 Then
For s = 0 To UBound(Split(thisLine, ":"))
SpltEnm = Split(thisLine, ":")(s)
If InStr(SpltEnm, " = ") > 0 Then
Itm = SpltEnm
Indx = CDbl(Split(SpltEnm, " = ")(1))
Else
Itm = SpltEnm & " = " & Indx
End If
EnumITems = EnumITems & IIf(EnumITems <> "", vbNewLine, "") & Itm '''''
Indx = Indx + 1
Next
Else
If InStr(thisLine, " = ") > 0 Then
Itm = thisLine
Indx = Split(thisLine, " = ")(1)
Else
Itm = thisLine & " = " & Indx
End If
EnumITems = EnumITems & IIf(EnumITems <> "", vbNewLine, "") & Itm '''''
End If
Indx = Indx + 1
End If
Next lineNum
If InStr(1, thisLine, EEnumName, vbTextCompare) > 0 Then Exit For
End With 'CodeMod
End If
Next vbcomp
SpltEI = Split(EnumITems, vbNewLine)
For I = LBound(SpltEI) To UBound(SpltEI)
If CDbl(Replace(Split(SpltEI(I), " = ")(1), " ", "")) = EnumItm Then
StrEnumVal = Replace(Split(SpltEI(I), " = ")(0), " ", "")
Exit For
Else
End If
Next
End Function
To active Required Reference copy Below Code then delete it
Sub AddReferenceVBA()
AddRef ThisWorkbook, "{0002E157-0000-0000-C000-000000000046}", "VBIDE", 5, 3
End Sub
Sub AddRef(wbk As Workbook, sGuid As String, sRefName As String, sRefMajor As Long, sRefMinor As Long)
Dim I As Integer
On Error GoTo EH
With wbk.VBProject.References
For I = 1 To .Count
If .Item(I).Name = sRefName Then
Exit For
End If
Next I
If I > .Count Then
.AddFromGuid sGuid, sRefMajor, sRefMinor ' 0,0 should pick the latest version installed on the computer
ThisWorkbook.Save
End If
End With
EX: Exit Sub
EH: MsgBox "Error in 'AddRef'" & vbCrLf & vbCrLf & Err.Description
Resume EX
Resume ' debug code
End Sub
Instead of Enum, define a Type(struct)
Public Type colType
ID As String
SSN As String
lName As String
fName As String
End Type
And then create a object of type colType and set desired values to it.
Public myColType As colType
myColType.ID = "A"
myColType.SSN = "B"
myColType.lname = "H"
myColType.fName = "G"