Excel VBA Enum and String
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.
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
What's the problem? How are you using the array in your code?
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
Kevin
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
Kevin
ASKER
@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.
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.
A single check box?
Value = IIf(CheckBox1.Value, 1, 0)
Kevin
Value = IIf(CheckBox1.Value, 1, 0)
Kevin
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.
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
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
Yes. A class. I'm not referring to a UDT.
ASKER
@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?
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?
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Kevin.
Works Perfectly
Works Perfectly