Displaying Enums as Strings (VBA)

[orekin]

Hi There

How do I display an enum as it's string value ? For example, say I have decalred an enum as follows:

Enum gE_DivEstType
Gold = 1
Other = 2
End Enum

And I have a variable that is of type gE_DivEstType

What I want to do is display "Gold" instead of 1 and "Other" instead of 2

In C# I could just use .ToString() - however I cannot find the equivalent in VBA.

TIA
Orekin

 

[XLGibbs]

Select Case maybe?


Select Case gE_DivEstType
case 1
strGE = "Gold"
Case 2
strGE = "other"

Enum = strGE

I don;t have experience with Enum so I might be way off.

 

[orekin]

Re Select Case

Thanks Gibbs.

The select case will solve the problem, but it takes a while to implement. For example, if I have a long select case statement

Enum gE_RateBasis
    DiscountFactor = 0
    AnnualComp = 1
    SemiAnnualComp = 2
    QuarterlyComp = 3
    MonthlyComp = 4
    DailyComp = 5
    ContinuousComp = 6
    SimpleInterest = 7
    DiscountRateBasis = 8
End Enum

Then getting a string would need to be:

Public Function DisplayAsString_gE_RateBasis(enumVal As gE_RateBasis)
    Select Case enumVal
        Case gE_RateBasis.AnnualComp
            DisplayAsString_gE_RateBasis = "AnnualComp"
        Case gE_RateBasis.ContinuousComp
            DisplayAsString_gE_RateBasis = "ContinuousComp"
        Case gE_RateBasis.DailyComp
            DisplayAsString_gE_RateBasis = "DailyComp"
        Case gE_RateBasis.DiscountFactor
            DisplayAsString_gE_RateBasis = "DiscountFactor"
        Case gE_RateBasis.DiscountRateBasis
            DisplayAsString_gE_RateBasis = "DiscountRateBasis"
        Case gE_RateBasis.MonthlyComp
            DisplayAsString_gE_RateBasis = "MonthlyComp"
        Case gE_RateBasis.QuarterlyComp
            DisplayAsString_gE_RateBasis = "QuarterlyComp"
        Case gE_RateBasis.SemiAnnualComp
            DisplayAsString_gE_RateBasis = "SemiAnnualComp"
        Case gE_RateBasis.SimpleInterest
            DisplayAsString_gE_RateBasis = "SimpleInterest"
        Case Else
            Call MsgBox("Unknown value for gE_RateBasis " & enumVal, vbCritical, THISAPPNAME)
            DisplayAsString_gE_RateBasis = "Unknown!"
    End Select
End Function

And I would manually need to keep the function is sync if I changed the enum.

I guess if there is no quicker way I will go down the select case route.

Cheers
Orekin

 

[XLGibbs]

I am sure there is a better way...

do a search on Google of required words "Mr.Excel" and the words "assign variable" "Array" and "List" and that may get you somewhere...the searches should lead you to some posts here...i don;'t have any links or examples handy..

 

[orekin]

Easy there Tiger!

I would not call this a dumb question by any means. If it was a dumb question you would have been able to answer it

 

[XLGibbs]

ROFLMAO...I wasn;t even thinking of my buddy Bart!

And if it was dumb question, I probably could have answered it with any number of poorly typed responses, riddled with syntax errors.

 

[StudRoid]

How about just making a collection of the string values with the key being the long integer value?

 

[skinnedKnuckles]

@orekin is correct that VBA has no clean way to do this. Some have imported the .NET framework methods (enum.ToString, GetType) into VBA as a workaround. An anonymous blogger worked out some VBA code to return an element from a string array based on the enum as @StudRoid suggested (see link here and below). This code ran for me after replacing 2 lines in the ToBinary() method as shown below.

    'Dim dic As Scripting.Dictionary
    'Set dic = New Scripting.Dictionary
    Dim dic                   'Create a variable
    Set dic = CreateObject("Scripting.Dictionary")

VBA - Enumeration to strings

 

[Scott Huish]

What you're describing that you want sounds like it could just be called from an array stored in a function:

Function gE_DivEstType(x As Long)
On Error Resume Next
Dim i
i = Array("Gold", "Other")
gE_DivEstType = i(x - 1)
End Function

Sub test()
MsgBox gE_DivEstType(1)
End Sub