[RESOLVED] Enum value according variable

Hello,
I have made an enum to work properly.
Now i've found a problem on choosing the right enum.
can someone point me in the right direction ?
I'am reading an excell sheet, and need to convert the string into a number.
Don't want to use a select case.

Code:

---

 

Private Enum Chocolade
    Melk = 1
    Ganache = 2
    Fondant = 3
    Mokka = 4
End Enum

Private Enum Eclair
    Mini = 1
    Medium = 2
    Maxi = 3
    Groot = 4
End Enum

' some code here. Enums Eclair and Chocolade used.
For iCount = 1 To oXLees.Worksheets.Count
        strSht = oXLees.Worksheets(iCount).Name
        iTxt = InStr(1, strSht, " ")
       
        iVal = [eclair. & Trim(Mid$(strSht, 1, iTxt - 1))] 'error
        oXLsht.Cells(iCount, 1).Value = iVal
        strVal = Trim(Mid$(strSht, iTxt + 1))
        iVal = [Chocolade. & strVal] 'gives an error

        oXLsht.Cells(iCount, 2).Value = iVal
Next
' putting in Immidiate pane gives me error 2015 Or 2029

---

I think I know what you are trying to do, but what is the goal? What will the excel cell contain? A number?

Enumerations cannot be referenced dynamically. You cannot add a variable onto the Eclair. and not get an error.

If Trim(Mid$(strSht, 1, iTxt - 1)) returns 1 for example, the result is equal to 1 which is also equal to Eclair.Mini, which is also equal to Chocolade.Melk, which is also equal to anything else that equates to 1.

Enumerations are used for various reasons. To offer a predefined list to a user in Public properties/subs/functions or as a return result from them. It is easier for a user to know, for example, that VbMsgBoxResult.vbYes means the "Yes" button was clicked than a return of simply 6 that means the same thing (VbMsgBoxResult.vbYes = 6). For Private enumerations, we coders generally use these to make our coding easier to read. For example, using a Select Case example....

Code:

---

Select Case MsgBox("blah, blah, blah", vbYesNoCancel)
    Case VbMsgBoxResult.vbYes
    Case VbMsgBoxResult.vbNo
    Case VbMsgBoxResult.vbCancel
End Select
' above is easier on the eyes than the equivalent:
Select Case MsgBox("blah, blah, blah", vbYesNoCancel)
    Case 6 ' same as vbYes
    Case 7 ' same as vbNo
    Case 2  ' same as vbCancel
End Select

---

Like I said, did not want to use select case.
Because i have to put in two select cases .
If my enumerator expands it is easier to adjust then add into my select cases.

To answer your question, i read in the values of the sheet names.
These are Mini Melk......
I want to read in the sheets with them values, but the sheet names refer to a number in my database, like i placed into my enumerators.
Been a long time, but i did use function names and other dynamicly in the past. Think has to be possible with enumerator too, just don't know how yet.

If you could reference an enumeration by name, dynamically, the code would be pretty extreme. So let's assume you cannot. What else can you use? How about a collection that is populated during form load?

Code:

---

Private XLxRef As Collection

Private Sub Form_Load()

    Set XLxRef = New Collection
    XLxRef.Add 1, "Chocolade.Melk"
    XLxRef.Add 2, "Chocolade.Ganache"
    XLxRef.Add 3, "Chocolade.Fondant"
    XLxRef.Add 4, "Chocolade.Mokka"
   
    XLxRef.Add 1, "Eclair.Mini"
    XLxRef.Add 2, "Eclair.Medium"
    XLxRef.Add 3, "Eclair.Maxi"
    XLxRef.Add 4, "Eclair.Groot"

End Sub


' some code here. Enums Eclair and Chocolade used.
For iCount = 1 To oXLees.Worksheets.Count
        strSht = oXLees.Worksheets(iCount).Name
        iTxt = InStr(1, strSht, " ")
       
        iVal = XLxRef.Item("Eclair." & Trim(Mid$(strSht, 1, iTxt - 1)))
        oXLsht.Cells(iCount, 1).Value = iVal
        strVal = Trim(Mid$(strSht, iTxt + 1))
        iVal = XLxRef.Item("Chocolade." & strVal)

        oXLsht.Cells(iCount, 2).Value = iVal
Next

---

Edited. Another solution. If you have this cross-reference of sheet names to numbers in your database, query the database for the sheet name & return the number.

Thnx, will do with the collection.
No need to read from table.
Prefer this

Code:

---

    For iCount = 1 To oXLees.Worksheets.Count
        strSht = oXLees.Worksheets(iCount).Name
        iTxt = InStr(1, strSht, " ")
        oXLsht.Cells(iCount, 1).Value = XLxRef.Item("Eclair." & Trim(Mid$(strSht, 1, iTxt - 1)))
        oXLsht.Cells(iCount, 2).Value = XLxRef.Item("Chocolade." & Trim(Mid$(strSht, iTxt + 1)))
    Next

---

instead of using two select cases in the loop.