VBA ENUM

What is VBA Enumerations (Enum)?

A VBA Enum, short for “enumeration,” is a specialized variable type in Excel VBA. It allows you to create a custom set of related constants or items, each with its distinct value. In essence, enumerations provide a structured way to define a list of items or options, much like specifying several elements sequentially.

Let us look at an example. In this example, we have an Enum called “DaysOfWeek” that represents the days of the week. Each day of the week, from Sunday to Saturday, is associated with a distinct constant within the Enum. This enumeration aids in providing clear and descriptive labels for the different days.

VBA Enum - Definition Example - 1

When the code is run, a message box will appear displaying:

“Today is Tuesday.”

VBA Enum - Definition Example - 2

This outcome results from assigning Tuesday to the variable today and using the Select Case statement to match this value with the corresponding day name “Tuesday.”

Note: If the Select Case statement is not utilized, the output of the code will display the internal index value assigned to the today variable. In this case, it will show a numeric value representing the position of the Tuesday constant within the “DaysOfWeek” Enum. It’s essential to use the Select Case statement to match the index with the corresponding day name to ensure that the output reflects the actual day.

Key Takeaways
  • VBA Enum String can represent string values in addition to numeric constants, providing descriptive names for strings used in your code.
  • VBA Enums are not only used for constants but can also serve as parameters or return types in procedures.
  • Using VBA Enums in your code makes it easier for others to understand and collaborate on your projects.
  • The VBA EnumChildWindows function is used in Windows API calls to enumerate child windows of a given parent window, often used for GUI automation.
  • VBA EnumWindows is another Windows API function that enumerates top-level windows, allowing for tasks like listing open windows or automating interactions.

Syntax

The syntax for declaring an Enum is straightforward:

Enum EnumName
  Constant1
  Constant2
  ' …
End Enum

Each constant within the Enum is assigned an incremental integer value, starting from 0 by default.


Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at some examples on how to implement the VBA ENUM function.

Example #1

Suppose we have a dataset of the clothing sizes mentioned in Column A and we want the Type of clothing size in Column B, we can achieve this by using VBA Enum the following way:

VBA Enum in Excel - Example 1
  • Step 1: Open the Visual Basic for Applications (VBA) editor in Excel. Press Alt + F11 to open the editor.
Example 1 - Step 1
  • Step 2: In the VBA editor, insert a new module by right-clicking on any existing module or the workbook name on the left pane > Insert > Module.
Example 1 - Step 2
  • Step 3: Here, we define an enumeration named ClothingSize with four named constants: Small, Medium, Large, and XLarge. Each constant is associated with a specific numerical value (30, 40, 45, and 50, respectively).
VBA Enum - Example 1 - Step 3
  • Step 4: In this step, we declare a variable “ws” of the Worksheet data type and use the Set statement to assign it to the worksheet named “Sheet1” within the workbook containing the macro.
VBA Enum - Example 1 - Step 4
  • Step 5: Here, we calculate the last row with data in column A of the specified worksheet. It does this by starting from the last row of the worksheet and moving upwards until it finds the last non-empty cell in column A.
Example 1 - Step 5
  • Step 6: In this step, we create a loop. This loop iterates through each row starting from the second row (to skip the header) up to the last row found in the previous step.
Example 1 - Step 6
  • Step 7: Here, we check if the value in the first column (column A) of the current row is numeric. This ensures that only rows containing numeric values are processed.
Example 1 - Step 7
  • Step 8: We declare a variable sizeNumber to hold the numeric value from the first column of the current row. This value will be used to determine the corresponding size.
Example 1 - Step 8
  • Step 9: In this Select Case block, we determine the corresponding size name based on the sizeNumber. Depending on whether the sizeNumber matches one of the constants in the ClothingSize enumeration, we assign the appropriate size name (“Small,” “Medium,” “Large,” “XLarge”) to the sizeName variable. If the sizeNumber doesn’t match any constants, we assign “Unknown” to sizeName.
VBA Enum - Example 1 - Step 9
  • Step 10: Here, we assign the determined sizeName to the second column (column B) of the current row.
Example 1 - Step 10
  • Step 11: After processing all rows, we use this part to format the entire second column (column B) as text so that the Enum-based size names are displayed as text in the cells.
Example 1 - Step 11
  • Step 12: Save the module and exit the VBE. Now go to Sheet1, where the data is available and press Alt + F8 to open the macro window.
VBA Enum - Example 1 - Step 12
  • Step 13: Select the “AssignClothingSizes” macro and click “Run.” After executing the macro, column B automatically populates with the clothing size type.
VBA Enum in Excel - Example 1 - Step 13

Here is the full code:

Enum ClothingSize
Small = 30
Medium = 40
Large = 45
XLarge = 50
End Enum

Sub AssignClothingSizes()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘ Replace “Sheet1” with your sheet name
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim i As Long
For i = 2 To lastRow ‘ Starting from the second row to skip the header
If IsNumeric(ws.Cells(i, 1).Value) Then
Dim sizeNumber As Long
sizeNumber = CLng(ws.Cells(i, 1).Value)
Dim sizeName As String
Select Case sizeNumber
Case ClothingSize.Small: sizeName = “Small”
Case ClothingSize.Medium: sizeName = “Medium”
Case ClothingSize.Large: sizeName = “Large”
Case ClothingSize.XLarge: sizeName = “XLarge”
Case Else: sizeName = “Unknown”
End Select
ws.Cells(i, 2).Value = sizeName
End If
Next i
ws.Columns(2).NumberFormat = “@” ‘ Set the cell format as text
End Sub

Example #2

Let us look at this example where we will use VBA Enum to classify the grades based on the scores for the following data.

Excel VBA Enum - Example 2
  • Step 1: In the new module, we define an enumeration named “Grade” with four named constants: Fail, C, B, and A.
 VBA Enum - Example 2 - Step 1
  • Step 2: In this step, we declare a variable ws of the Worksheet data type and use the Set statement to assign it to the worksheet named “Sheet2” within the workbook containing the macro.
VBA Enum - Example 2 - Step 2
  • Step 3: We calculate the last row with data in column A of the specified worksheet. It does this by starting from the last row of the worksheet and moving upwards until it finds the last non-empty cell in column A.
Example 2 - Step 3
  • Step 4: Next, we create a loop. This loop iterates through each row, from the second row (to skip the header) to the last row found in the previous step.
Example 2 - Step 4
  • Step 5: Here, we check if the value in the first column (column A) of the current row is numeric. This ensures that only rows containing numeric values are processed.
 Example 2 - Step 5
  • Step 6: Next, we declare a variable “studentMarks” to hold the numeric value from the first column of the current row. This value will be used to determine the corresponding grade.
Example 2 - Step 6
  • Step 7: In this Select Case block, we determine the corresponding grade based on the “studentMarks.” Depending on the value of “studentMarks”, we assign the appropriate grade (“Fail,” “C,” “B,” “A”) to the grade variable.
VBA Enum - Example 2 - Step 7
  • Step 8: Here, we assign the determined grade to the current row’s second column (column B).
Example 2 - Step 8
  • Step 9: After processing all rows, we use this part to format the entire second column (column B) as text so that the grade names are displayed as text in the cells.
Example 2 - Step 9
  • Step 10: Save the module and exit the VBE. Now go to Sheet2, where the data is available, and press Alt + F8 to open the macro window.
 VBA Enum - Example 2 - Step 10
  • Step 11: Select the “AssignGrades” VBA macro and click on Run. Once you execute the code, you will see that the B column, i.e., the Grades column, is populated with the Grades based on the scores mentioned in the A column.
Excel VBA Enum - Example 2 - Step 11

Here is the full code:

Enum grade
Fail
C
B
A
End Enum

Sub AssignGrades()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet2”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim i As Long
For i = 2 To lastRow ‘ Starting from the second row to skip the header
If IsNumeric(ws.Cells(i, 1).Value) Then
Dim studentMarks As Long
studentMarks = CLng(ws.Cells(i, 1).Value)
Dim grade As String
Select Case studentMarks
Case Is < 35: grade = “Fail” Case 35 To 49: grade = “C” Case 50 To 69: grade = “B” Case Is >= 70: grade = “A”
End Select
ws.Cells(i, 2).Value = grade
End If
Next i
ws.Columns(2).NumberFormat = “@” ‘ Set the cell format as text
End Sub

Using VBA Enumeration Variables to Store the Data

After defining a VBA Enum, you can declare variables of that VBA Enum type to store data. For instance:

Dim today As Days

today = Tuesday

In the above example, the variable “today” is of the type “Days,” and it can store any day of the week.

Important Things to Note

  • VBA Enums replace ambiguous and hard-to-remember numbers with descriptive names, enhancing code readability and maintainability.
  • By using meaningful names for constants, VBA Enums enhance code clarity and make it easier to understand the purpose of each value.
  • While the default integer values start at 0, you can explicitly assign custom values to each constant.
  • VBA Enum Beispiel translates to “example” in German. VBA Enums can be used to create examples or scenarios with named constants, enhancing code illustration.
  • VBA Enum allows you to create customized sets of related constants, making your code more readable and self-explanatory.

Frequently Asked Questions (FAQs)

1. What is the difference between TYPE and ENUM in VBA?

Type and VBA Enum are used to create custom data structures in VBA, but they have distinct purposes. Type groups various data types to form a composite structure, while Enum defines a set of related constants or options for simplified handling.

2. Why is ENUM in VBA not working?

If a VBA Enum is not functioning correctly, potential reasons include syntax errors, improper scope, typos, naming conflicts, or other compile errors within the code.

3. What is the purpose and application of enumerations?

Enumerations (VBA Enums) provide a structured approach to define and manage related constants. They enhance code readability, simplify maintenance, ensure consistent usage, logically organize code, and offer clear and descriptive values. VBA Enums are especially useful when managing distinct choices that are logically connected, thereby improving code quality and comprehension.

Download Template

This article must be helpful to understand the VBA Enum, with its syntax and examples. You can download the template here to use it instantly.

This has been a guide to VBA Enum. We learn how to use Enumeration in Excel VBA with its syntax, using variables to store data & examples. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *