Array in an Enumeration

Question

I am wondering if it is possible to have a dynamic enumeration.

Explanation: I have a function to add errors to an error log in ms access. The categories in my Enum variable are all in a table. If I use the function and enum as depicted below, it works just fine. The problem is however, that there is a possibility that the categories will change with time.

Enum category
    Category1 = 1
    Category2 = 2
    etc...    = n
End Enum

Public Function AddError(Current_order_ID As Long, Optional Error_Category As category)
'Add the error to the log
End Function

I have noticed that using DLookups and recordsets and actually everything in general will not work. While compiling, it throws an 'Invalid inside Enum'. Consulting MSDN gives me the following information: You tried to specify a string or some other invalid type as the value of an Enum member. The constant expression used to specify an Enum member must evaluate to type Long or another Enum type.

This tells me that an enum can hold only long types, hence the numbers, but also other Enum types.

What I am looking for: Is there some workaround, or method, to loop through a recordset or query and pass the values on to an array, and dynamically assign these values to an enumeration. Or perhaps it can be done by changing the source code/vba text itself?

Note: I know that there are a lot of other ways to add my errors to a log and I am able to do so, but for know I am just wondering if this is even possible to do.

I am looking forward to your reactions.

Could you put the actual line of code which is producing the error? The code fragment you have given is OK. It would help to see the actual problem statement. — Rob Sedgwick
– Rob Sedgwick, Commented Aug 22, 2014 at 11:27
The code above is not producing any errors, as stated, it works just fine. I am having trouble explaining correctly I guess, but what I am looking for, is a way to change the categories in the Enum, according to values in a table, if that is at all possible. — Jens
– Jens, Commented Aug 22, 2014 at 11:33
@JenZzz I only understand the first part of your post. So you are passing an Enum for the error type. What do you mean by categories may change? the number assigned to the enum? — user2140173
– user2140173, Commented Aug 22, 2014 at 11:58

ashareef · Accepted Answer · 2014-08-22 13:40:10Z

3

The Recordset loops through a table containing the Enum types. Basically this is editing a modules VBA code.

Public Function CreateEnum()
    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("MYENUMS", dbOpenSnapshot)

    Dim m As Module
    Dim s As String
    Set m = Modules("myEnumsModule")

    s = "Option Compare Database"
    s = s & vbNewLine & "Option Explicit"
    s = s & vbNewLine
    s = s & vbNewLine & "Public Enum MyEnums"
    With rs
        Do Until .EOF
            s = s & vbNewLine & vbTab & .Fields("MYENUM") & " = " & rs.Fields("MYENUM_ID")
            .MoveNext
        Loop
    End With
    s = s & vbNewLine & "End Enum"

    Call m.DeleteLines(1, m.CountOfLines)
    Call m.AddFromString(s)
End Function

edited Aug 22, 2014 at 13:40

answered Aug 22, 2014 at 12:44

ashareef

1,84613 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jens Over a year ago

Why thank you, I was unaware of the possibility of changing the source code of a module in vba. Even though z's answer was helpfull as well, this one provides me with the answer I was looking for! Thanks

z̫͋ · Accepted Answer · 2014-08-22 12:27:28Z

2

While an Enum type cannot be changed at runtime, a variable of that type can actually store any Long value.

Enum category
    Category1 = 1
    Category2 = 2
End Enum


Sub error(err As category)
    Debug.Print err
End Sub

Sub test()
    Dim category3 As Long: category3 = 3
    Call error(category3) ' prints "3"
End Sub

But no, you cannot declare or update an Enum at runtime, nor convert an array to an Enum.

edited Aug 22, 2014 at 12:27

answered Aug 22, 2014 at 12:22

z̫͋

1,57110 silver badges15 bronze badges

1 Comment

Jens Over a year ago

Thank you for the clear answer. Even though this is not possible, your small example has given me some inspiration!

Collectives™ on Stack Overflow

Array in an Enumeration

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related