An Enum in Microsoft Access VBA is a named set of numeric constants. Declare it at the top of a standard module, then use its members to make code easier to read, such as assigning InvoiceStatus.Approved instead of remembering that the value 2 means “approved.”
What an Enum Means in Access VBA
Enum is short for enumeration. It defines a group of related names that VBA associates with integer values. A status, mode, or category is a natural fit when the possible choices are known in advance and are used by your code.
For example, code that stores a processing state as 0, 1, or 2 can be difficult to understand when you revisit it. An enum gives those numbers meaningful names. The names are constants: they are set in the declaration and cannot be changed while the code runs. An enum is not a new kind of Access table field, nor does it create a list of choices for a user automatically.
VBA enum values are numeric (Long integer) constants. They are useful for communicating intent and reducing unexplained numbers in code, but the names themselves are not stored in a database field. That difference matters when you decide whether an enum or an Access lookup table is the right tool.
Declaring an Enum
Put an enum declaration in the declarations section at the top of a module, before its procedures. For an enum you want to use throughout an Access project, use a standard module and declare it Public. In Access, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and add a declaration such as this before any Sub or Function:
Public Enum InvoiceStatus
Draft = 0
Submitted = 1
Approved = 2
Rejected = 3
End Enum
The first member is explicitly assigned 0, and each following member is assigned the next integer. Explicit values are optional: if the first member has no value, it defaults to 0, and subsequent members increase by 1. Assigning values explicitly makes the intended mapping clear and helps avoid accidental changes if you later insert a member.
A Private Enum limits the declaration to its module. Use that when the names are implementation details for one module. A public enum in a standard module is the usual choice when forms, reports, and other modules in the same VBA project need to refer to the same set. Keep public declarations in a central module rather than making similar, separately named lists in several places.
Using Enum Values in Procedures
Declare a variable using the enum type name. Then assign and compare the named members. The example below belongs in the same standard module as the public enum declaration:
Public Function StatusLabel(ByVal status As InvoiceStatus) As String
' Return a readable label for a known status.
Select Case status
Case InvoiceStatus.Draft
StatusLabel = "Draft"
Case InvoiceStatus.Submitted
StatusLabel = "Submitted"
Case InvoiceStatus.Approved
StatusLabel = "Approved"
Case InvoiceStatus.Rejected
StatusLabel = "Rejected"
Case Else
StatusLabel = "Unknown status"
End Select
End Function
Public Sub ShowStatusExample()
Dim currentStatus As InvoiceStatus
currentStatus = InvoiceStatus.Approved
Debug.Print StatusLabel(currentStatus)
End Sub
Running ShowStatusExample prints Approved in the Immediate window. In the Visual Basic Editor, press Ctrl+G to display that window. The member is written with its enum type, as in InvoiceStatus.Approved; this qualification helps show which group of constants it belongs to and can make code easier to inspect.
Before running a macro or procedure that changes records, save a copy of the database or test on sample data. VBA changes cannot be undone with Ctrl+Z. The example only prints text, but the same precaution matters when applying enum-based logic to updates or deletes.
Choosing and Assigning Values
Use a value scheme that will remain understandable. Sequential values are convenient for internal states, while explicit values can preserve compatibility when numbers are already saved in records or exchanged with another system. If you add a new status to a scheme whose numeric values are persisted, give careful thought to whether existing values must remain unchanged.
- Sequential values: Suitable when the enum is only an internal code choice and its exact numbers are not part of a saved-data contract.
- Explicit values: Preferable when code, saved records, or an external interface depends on a particular numeric mapping.
- Gaps: You can leave unused numbers, for example to reserve space for future values. Gaps do not create extra members.
- Duplicate values: They can make two names represent the same number, but may make comparisons and debugging confusing. Use them only when that aliasing is intentional.
Enum members must be constant values; they are not generated from a query, table, or user selection at runtime. If the choices need to be edited by an administrator without changing VBA, an enum is usually too rigid. A lookup table is more appropriate for that requirement.
Using Enums with Access Forms and Tables
An enum can make form event code clearer. For instance, a button-click procedure could set a variable to InvoiceStatus.Submitted and pass it to a routine that validates or saves an invoice. The form control still needs its own user-facing text and behavior: declaring an enum does not populate a combo box, add a validation rule to a field, or prevent a user from entering an unwanted value.
To store a status in an Access table, a common design is a Number field with Field Size set to Long Integer, containing the numeric code. VBA code can interpret that number using the enum, provided the stored mapping and the enum declaration agree. A query or table does not automatically know that the number 2 means Approved; it sees a number unless you join to a lookup table or otherwise provide a label.
For a small, fixed set of internal states, an enum paired with a Long Integer field can be practical. For choices that users must see, search, report on, or maintain, create a related lookup table with a key and a descriptive label, then use a combo box or query to display the label. That design makes the choices visible in the database and avoids scattering display text across VBA procedures.
Enum Compared with Constants and Lookup Tables
A group of separate Const declarations can also provide named values, but an enum groups related values under one type name. Declaring a variable As InvoiceStatus communicates that the variable represents one of the invoice statuses, not an unrelated number. This improves readability and can help catch mistakes while editing or compiling code, though it does not replace runtime validation of database contents.
A lookup table solves a different problem. It stores data that the application can read and users may be able to maintain; an enum is part of the compiled VBA project. Use an enum for stable choices that primarily guide code flow. Use a lookup table for editable, displayable, or relational data. You can also use both: store IDs in a lookup table and use a VBA enum for a separate, fixed set of internal workflow states, but avoid maintaining two competing definitions of the same choices.
One related limitation is that a VBA enum name is not an Access SQL table or field type. Do not expect a query to treat InvoiceStatus.Approved as a database-defined value just because it exists in a module. Use a parameter supplied by VBA, a numeric value where appropriate, or a join to a lookup table, depending on the query and data design.
Common Problems and Troubleshooting
If VBA reports that a type is not defined, check the spelling of the enum name and its scope. A Private Enum cannot be used from another module. For a shared type, put a Public Enum in a standard module and make sure the declaration is in the declarations section, not inside a procedure.
- Declaration placed inside a Sub or Function: Move the complete enum declaration to the module level, above the procedures.
- Wrong module visibility: Change a module-only declaration to public only if other modules genuinely need it, and place the public enum in a standard module.
- Member name conflict: Rename ambiguous members with a clear prefix or qualify a member with its enum type, such as
InvoiceStatus.Draft. - Stored number displays without a label: The enum does not convert database values into text. Add display logic or use a lookup table and join.
- New enum member breaks old data assumptions: Review code and saved records that depend on numeric values before changing an established mapping.
After changing declarations, use Debug > Compile in the Visual Basic Editor to find compile-time issues. If a procedure compiles but the displayed status is still wrong, check the actual stored number and verify that every relevant procedure uses the same mapping. Access desktop is available for Windows; there is no native Microsoft Access desktop application for macOS, so these Access-specific VBA steps require Windows.
Frequently Asked Questions
How do I create an enum in Access VBA?
Open a standard module, place a Public Enum Name declaration in its declarations section, list the named members, and finish with End Enum. Give members explicit numeric values when the mapping needs to stay stable.
Does an Access enum create a dropdown list?
No. An enum defines constants for VBA code. To show a selectable list on a form, configure a combo box, commonly with a lookup table or a fixed value list.
Can I save an enum directly in an Access field?
No. The enum type is a VBA code construct, not an Access table data type. Store a numeric value in a suitable field if that fits the design, or use a lookup table when records need meaningful, maintainable labels.
Should an enum be Public or Private?
Use Private when only procedures in that module need it. Use Public in a standard module when multiple parts of the project need the same enum type and members.
Can an enum member have a text value?
No. VBA enum members represent numeric constants, not strings. If you need text labels, map each member to text in code, as in the example, or keep labels in a lookup table.
Why use an enum instead of numbers?
Names such as InvoiceStatus.Approved make the purpose of a value clearer than an unexplained number. Enums are most useful when the set of choices is fixed in code; they do not replace database constraints or a lookup table.
Related
- Excell Battery P/N 2EXL7431-001: What It Means
- Y Balance Test: How to Perform and Calculate Scores
- How to Add Transitions in PowerPoint: Steps and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Get Array Dimensions: Bounds, Rank, and SizeIn Excel VBA, use LBound(array, dimension) and UBound(array, dimension) to get an array’s lower and upper bounds; its…
- Access VBA MsgBox Constants: Buttons, Icons and ResultsAccess VBA uses the built-in MsgBox function to show a message and, optionally, buttons, an icon, and a…
- Excel VBA Database: How It Works and When to Use OneAn Excel VBA database is a structured set of records kept in an Excel workbook, with Visual Basic…
- Excel VBA Binary Strings: Text, Bytes, and BitsIn Excel VBA, a String is text, not a raw sequence of binary bytes. If you mean a…
- Excel VBA Error 400: Meaning, Causes, and How to Fix ItIn Excel VBA, error 400 means “Form already displayed; can’t show modally.” It usually appears when code tries…
- VBA Reset AutoFilter: Clear Filters, Keep the SetupTo reset an Excel AutoFilter in VBA while keeping its dropdown arrows, clear the active filter rather than…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.