?
Solved

Excel VBA Enum and String

Posted on 2010-01-04
13
Medium Priority
?
3,097 Views
Last Modified: 2012-05-08
In my macro I use a Function to declare an array from both a UserForm and a Procedure.

The UserForm provides a string that matches the Enum i have created.
But thats the problem.

Can a String be changed to an Enum?

Attached is Snippets of the Code.
NOTE: That is not the complete code.

'Enum
Enum Location
  Belmont = 0
  Kilkenny = 1
End Enum

'Called from Procedure
LocalArray = ArrayDeclaration(Cont1.Caption)

'Called from UserForm
arrBelmont = ArrayDeclaration(Belmont)
arrKilkenny = ArrayDeclaration(Kilkenny)

'Function
Function ArrayDeclaration(Branch As Location)
Select Case Branch
Case Belmont
ArrayDeclaration = Array("100 - Customer Support", _
                        "101 - Customer Support 101", _
                        "102 - Resellers", _
                        "103 - Company Group Accts", _
                        "104 - Staff Acct", _
                        "121 - Rep 121", _
                        "122 - Rep 122", _
                        "123 - Rep 123", _
                        "124 - Rep 124", _
                        "125 - ASA National Accts", _
                        "126 - Print", _
                        "127 - New Business", _
                        "128 - Rep 128", _
                        "132 - Rep 132", _
                        "141 - Furniture", _
                        "151 - Rep 151")
Case Kilkenny
ArrayDeclaration = Array("201 - Customer Support SA", _
                        "221 - David Surname", _
                        "222 - Tracey Surname", _
                        "224 - Myrtle Surname", _
                        "225 - ASA Accounts SA", _
                        "226 - Brenton Surname")
End Select
End Function

Open in new window

0
Comment
Question by:bromy2004
  • 6
  • 4
  • 3
13 Comments
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26177986
What's the problem?  How are you using the array in your code?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26177990
What you can do is pull the selected value index from the combo box versus the text value. It's a trick way to easily convert the text value to an integer enumerated value assuming your enumerated values are consecutive.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26178003
The combobox property is ListIndex. From help: "The ListIndex property contains an index of the selected row in a list. Values of ListIndex range from -1 to one less than the total number of rows in a list (that is, ListCount - 1). When no rows are selected, ListIndex returns -1. When the user selects a row in a ListBox or ComboBox, the system sets the ListIndex value. The ListIndex value of the first row in a list is 0, the value of the second row is 1, and so on."

Kevin
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:bromy2004
ID: 26178011
@Tom
The array is used extensively throughout all the procedures to split each Sales Rep into their State office.
Ive used this format (with the Function) to allow for other States at a later date.
then all i would need to do is
1. Add another line to the Enum
2. Add another Group to the Select Case in the Function
3. Add another CheckBox to the Userform

@zorvek
It is a Check Box, not a List Box
Although i do use a ListBox with the Array successfully.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26178019
A single check box?

   Value = IIf(CheckBox1.Value, 1, 0)

Kevin
0
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26178041
Have you considered creating a custom datatype?  Information could be edited without effecting the code that references it.  You could easily include a method that exposes your strings as an array.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26178050
Tom,

This is Excel and VBA which is a variation of VB6. No such beast here.

Are you referring to a class?

The closest thing we have to a "custom data type" is an enumeration as the asker has already implemented.

Kevin
0
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26178053
Yes.  A class.  I'm not referring to a UDT.
0
 
LVL 10

Author Comment

by:bromy2004
ID: 26178055
@Kevin
Would this be an easy option when there are three CheckBoxes?
The Macro Loops through all checkboxes in the UserForm Frame, Then if it is ticked continues the Macro.
The only this separating them apart is the Caption or Name.
Or would i need to have another Select Case?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26178068
Tom,

Me thinks a class would be a bit overkill to handle the conversion of three check boxes to integers - but I've done things myself like that in the past ;-)

Bromy,

I need a bit more information. How do you get from three check boxes to a (0, 1) enumerated value?

Kevin
0
 
LVL 10

Author Comment

by:bromy2004
ID: 26178101
The Enum will eventually become larger.

Enum Location
  Belmont = 0
  Kilkenny = 1
  Brisbane = 2
  Melbourne = 3
  Darwin = 4
End Enum

the User will be Checking the boxes that they want Reports on.
and The Check Boxes are an outer loop

Attached is a Copy of the Workbook to help explain.
EE---Example---Budgets.xls
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 26178138
If you are going to keep adding check boxes then you could use a simple converter function:

Public Function LocationEnum(ByVal Location As String) As Long
    Select Case Location
        Case "BELMONT": LocationEnum = 0
        Case "KILKENNY": LocationEnum = 1
    End Select
End Function

This is sort of along the lines that Tom was suggesting but without creating an actual class.

Use it like so:

   EnumValue = LocationEnum(CheckBox1.Caption)

Kevin
0
 
LVL 10

Author Comment

by:bromy2004
ID: 26178202
Thanks Kevin.
Works Perfectly
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question