Can you reference an enum value as a string

[nitt1995]

I have set up an enumeration in a module (Module1) like the example below:
Enum TestEnum
A= 2
B= 0
C= 0
D= 2
E= 2
F= 2
G= 0
H= 0
End Enum

I want to be able to reference the integer value of one of these entries (ie TestEnum.B) from another module (Module 2).

One way I have thought of this is to set up a function in Module 1 like so:
Public Function getEnumValue (enumString As String) As Integer
getEnumValue = ......... (reference enum via string here)
End Function
I can't figure out how to reference the enum by a string name.

The other way that I have thought of this was to create a collection in Module 1 with a getCollection function that I can call from Module2, but right now I am trying to learn more about enums so that is where my focus is. As for the collection idea, I thing I need to package the information as objects, which I do not know about in VBA.

[mvidas]

Hi nitt,

It seems you may have the wrong idea of what Enums are for. The only time I've used them is if I want to make it easier to code something. For example, put the following in one module:Enum TestEnum
ChoiceA = -1
ChoiceB = 0
ChoiceC = 1
ChoiceD = 2
ChoiceE = 3
ChoiceF = 4
ChoiceG = 5
ChoiceH = 6
End EnumAnd in another module put this:Sub EnumExample()
Dim MyTestEnum As TestEnum
MyTestEnum
End SubAfter the MyTestEnum, put an equals sign and you'll see the different values that enum can have. Similar to how when you use the .Find method in excel vba, if you've ever used the SearchDireciton argument, you'll see the different possibilities come up after you enter the :=
In all honesty, I really can't figure out what you're trying to figure out. Can you explain a little further?
Matt

[Bob Phillips]

Hi nitt,

It seems you may have the wrong idea of what Enums are for. The only time I've used them is if I want to make it easier to code something. For example, put the following in one module:Enum TestEnum
ChoiceA = -1
ChoiceB = 0
ChoiceC = 1
ChoiceD = 2
ChoiceE = 3
ChoiceF = 4
ChoiceG = 5
ChoiceH = 6
End EnumAnd in another module put this:Sub EnumExample()
Dim MyTestEnum As TestEnum
MyTestEnum
End SubAfter the MyTestEnum, put an equals sign and you'll see the different values that enum can have. Similar to how when you use the .Find method in excel vba, if you've ever used the SearchDireciton argument, you'll see the different possibilities come up after you enter the :=
In all honesty, I really can't figure out what you're trying to figure out. Can you explain a little further?
Matt

You can also use them as an argument type, and use that list in your code , such as



Enum myColours
xlCIBlack = 1
xlCIBlue = 5
xlCICoral = 22
xlCIDarkBlue = 11
xlCIDarkPurple = 21
xlCIDarkRed = 9
xlCIDarkYellow = 12
xlCIBrightGreen = 4
xlCIGray25 = 15
xlCIGray50 = 16
xlCIGreen = 10
xlCIIvory = 19
xlCIPeriwinkle = 17
xlCILightTurquoise = 20
xlCIOceanBlue = 23
xlCIPink = 7
xlCIPlum = 54
xlCIRed = 3
xlCITeal = 14
xlCITurquoise = 8
xlCIViolet = 13
xlCIWhite = 2
xlCIYellow = 6
End Enum


Function ColourName(ci As myColours)
Select Case ci
Case xlCIBlack: ColourName = "Black"
Case xlCIWhite: ColourName = "White"
Case xlCIRed: ColourName = "Red "
Case xlCIBrightGreen: ColourName = "Green "
Case xlCIBlue: ColourName = "Blue"
Case xlCIYellow: ColourName = "Yellow"
Case xlCIPink: ColourName = "Pink"
Case xlCITurquoise: ColourName = "Turquoise"
Case xlCIDarkRed: ColourName = "DarkRed"
Case xlCIGreen: ColourName = "Green"
Case xlCIDarkBlue: ColourName = "DarkBlue"
Case xlCIDarkYellow: ColourName = "DarkYellow"
Case xlCIViolet: ColourName = "Violet"
Case xlCITeal: ColourName = "Teal"
Case xlCIGray25: ColourName = "Gray (25%)"
Case xlCIGray50: ColourName = "Gray (50%)"
Case xlCIPeriwinkle: ColourName = "Periwinkle"
Case xlCIPlum: ColourName = "Plum "
Case xlCIIvory: ColourName = "Ivory"
Case xlCILightTurquoise: ColourName = "LightTurquoise "
Case xlCIDarkPurple: ColourName = "DarkPurple "
Case xlCICoral: ColourName = "Coral"
Case xlCIOceanBlue: ColourName = "OceanBlue"
End Select
End Function

Sub TestColourName()
MsgBox ColourName(ActiveCell.Interior.ColorIndex)
End Sub

[nitt1995]

You can also use them as an argument type, and use that list in your code , such as



Enum myColours
xlCIBlack = 1
xlCIBlue = 5
xlCICoral = 22
xlCIDarkBlue = 11
xlCIDarkPurple = 21
xlCIDarkRed = 9
xlCIDarkYellow = 12
xlCIBrightGreen = 4
xlCIGray25 = 15
xlCIGray50 = 16
xlCIGreen = 10
xlCIIvory = 19
xlCIPeriwinkle = 17
xlCILightTurquoise = 20
xlCIOceanBlue = 23
xlCIPink = 7
xlCIPlum = 54
xlCIRed = 3
xlCITeal = 14
xlCITurquoise = 8
xlCIViolet = 13
xlCIWhite = 2
xlCIYellow = 6
End Enum


Function ColourName(ci As myColours)
Select Case ci
Case xlCIBlack: ColourName = "Black"
Case xlCIWhite: ColourName = "White"
Case xlCIRed: ColourName = "Red "
Case xlCIBrightGreen: ColourName = "Green "
Case xlCIBlue: ColourName = "Blue"
Case xlCIYellow: ColourName = "Yellow"
Case xlCIPink: ColourName = "Pink"
Case xlCITurquoise: ColourName = "Turquoise"
Case xlCIDarkRed: ColourName = "DarkRed"
Case xlCIGreen: ColourName = "Green"
Case xlCIDarkBlue: ColourName = "DarkBlue"
Case xlCIDarkYellow: ColourName = "DarkYellow"
Case xlCIViolet: ColourName = "Violet"
Case xlCITeal: ColourName = "Teal"
Case xlCIGray25: ColourName = "Gray (25%)"
Case xlCIGray50: ColourName = "Gray (50%)"
Case xlCIPeriwinkle: ColourName = "Periwinkle"
Case xlCIPlum: ColourName = "Plum "
Case xlCIIvory: ColourName = "Ivory"
Case xlCILightTurquoise: ColourName = "LightTurquoise "
Case xlCIDarkPurple: ColourName = "DarkPurple "
Case xlCICoral: ColourName = "Coral"
Case xlCIOceanBlue: ColourName = "OceanBlue"
End Select
End Function

Sub TestColourName()
MsgBox ColourName(ActiveCell.Interior.ColorIndex)
End Sub


This is getting close to what I am after. Now, lets say that I want to pass an enumeration as an argument, and I want to be able to call this function from within VBA.

Dim ci as myColours
set ci = xlCIBlack
Application.Run(ColourName, ci)

I need to pass the myColours enumeration into the Application.Run method. I need a way to cycle through each of the enumeration options in this code so I can return the values from various enumeration entities.

Therefore, using the myColours Enumeration example, I would want to pass xlCITeal like : Application.Run(ColourName, xlCITeal ) to return "Teal" as a string. The next time, I might pass Application.Run(ColourName, xlCIBrightGreen) to return "green" as a string, etc....

[Bob Phillips]

This is getting close to what I am after. Now, lets say that I want to pass an enumeration as an argument, and I want to be able to call this function from within VBA.

Dim ci as myColours
set ci = xlCIBlack
Application.Run(ColourName, ci)
I need to pass the myColours enumeration into the Application.Run method. I need a way to cycle through each of the enumeration options in this code so I can return the values from various enumeration entities.

Therefore, using the myColours Enumeration example, I would want to pass xlCITeal like : Application.Run(ColourName, xlCITeal ) to return "Teal" as a string. The next time, I might pass Application.Run(ColourName, xlCIBrightGreen) to return "green" as a string, etc....

That is simple enough, but you have your declarations wrong



Dim ci As Long
ci = xlCITeal
MsgBox Application.Run("ColourName", ci)


MsgBox Application.Run("ColourName", xlCITeal)

[nitt1995]

That is simple enough, but you have your declarations wrong



Dim ci As Long
ci = xlCITeal
MsgBox Application.Run("ColourName", ci)



Oops, my mistake. I was trying to simplify my code on the fly to make an easy example case for the board, and I made this goof

However, this still leaves one point unanswered. Let's say I want to set the value of ci "on the fly" based upon a string that is named the name of the enumeration option (i.e. xlCITeal). How do I convert the string to an enumeration value of the same name?

[Bob Phillips]

Oops, my mistake. I was trying to simplify my code on the fly to make an easy example case for the board, and I made this goof

However, this still leaves one point unanswered. Let's say I want to set the value of ci "on the fly" based upon a string that is named the name of the enumeration option (i.e. xlCITeal). How do I convert the string to an enumeration value of the same name?

You can't. Just as you cannot create a variable name on the fly, you cannot create an enumeration item. This is where I would use arrays.

[nitt1995]

You can't. Just as you cannot create a variable name on the fly, you cannot create an enumeration item. This is where I would use arrays.

I don't want to create an enumeration item, I just want to reference that item. Perhaps I am under the faulty belief that when an enumeration is defined, it exists like an object and thus can be referred to by a string name (like an object)

Thanks for your help