Updated June 9, 2023
VBA Enum
Enum is abbreviated as enumeration. It is a process in which we mention something one by one. Similarly, we have enumerations in every programming language, whether JAVA, C, or Excel VBA. In VBA, we have some inbuilt enumerations which start with VB, like VBnewLine is used to insert a new line. It is an enumeration. In Excel VBA, we can also create our custom enumerations.
In VBA, Enum is a variable, just like an integer or string and others. We can make a list of elements in VBA and use the variables. Enums are made to make things easier to use or easy to find. For example, for a motorbike company like Bajaj, enumeration will be a list of bikes they produce, such as Pulsar, Avenger, dominar, etc. We need to remember that an enum is like a numeric variable that can be used as a representation of one interpretation. There is also another way to use status messages by using a constant. Still, they can be tiresome because they are not grouped and come along with all the other enumerated variables of VBA. But by using VBA ENUM, we can group our enumerations in one group and use them whenever we like.
Syntax of Enum in Excel VBA
The syntax to use ENUM in Excel VBA is as follows:
Enum EnumName EnumValue [= LongValue] EnumValue [= LongValue] [[_EnumValue]] [= LongValue] End Enum
An important thing to remember is that the values specified in ENUM should have specific values. Still, if we do not provide any value to it, VBA automatically gives the first enum value as 0, the next one as 1, and so on.
So as I said above about a motorbike company manufacturing bikes, the enumeration for it will be as follows.
ENUM Bikes Pulsar Avenger = 4 Dominar End ENUM
Now in the above sample enum, I have assigned values to only Avenger, so by default, the pulsar is zero, and the dominar is 5 as the previous list value was 4. This is how values are assigned to ENUM values.
First, let us understand why enumerations are essential in VBA; then, we will move to examples of how to make custom enumerations in VBA.
For a demonstration in VBA code, try to refer to cell A1, and as soon as we use the dot operator, we can see that many other enumerations pop up, which may or may not be required for us. Have a look at the screenshot below.
We can see that in the above screenshot, there are many enumerations starting from A. Those are inbuilt VBA enumerations.
Now let us learn how to make custom enumerations in VBA with a few examples of ourselves.
How to Make Custom Enumerations in Excel VBA
We will learn how to make custom Enumerations (Enum) with a few examples in Excel VBA.
Excel VBA Enum – Example #1
Let us make a very simple enumeration first. In this example, we will define the greetings of the day as some numbers and display them as personalized messages.
Follow the below steps to use VBA Enum:
Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.
Step 2: Insert a new module from the insert tab in the VB Editor.
Step 3: Declare the Enum function with an enum name.
Code:
Enum Greetings End Enum
Step 4: Declare the enum values as follows,
Code:
Enum Greetings Morning = 1 Afternoon = 2 Evening = 3 Night = 4 End Enum
Step 5: Now declare a sub-function below the enum function to use the enum.
Code:
Enum Greetings Morning = 1 Afternoon = 2 Evening = 3 Night = 4 End Enum Sub UseEnum() End Sub
Step 6: Display a personalized message using the msgbox function.
Code:
Enum Greetings Morning = 1 Afternoon = 2 Evening = 3 Night = 4 End Enum Sub UseEnum() MsgBox "Greeting Number" End Sub
Step 7: To display the greeting number, we use the enum as follows,
Code:
Enum Greetings Morning = 1 Afternoon = 2 Evening = 3 Night = 4 End Enum Sub UseEnum() MsgBox "Greeting Number" & Greetings. End Sub
Step 8: Choose any of the greetings values as follows.
Code:
Enum Greetings Morning = 1 Afternoon = 2 Evening = 3 Night = 4 End Enum Sub UseEnum() MsgBox "Greeting Number" & Greetings.Afternoon End Sub
Step 9: Once done, compile and run the complete code by clicking on the play button located just below the menu bar. When we run the code, we see the following result.
It was very easy to create and use an enumeration; this is clear from the above example.
Excel VBA Enum – Example #2
In this example, let us use enumeration to store the data in an Excel sheet. We will use the example of motorbikes used in the introduction section.
We have some pre-written calculations in sheet 1 as follows.
We will fill the cells of a count of motorbikes using enumerations and which will calculate the total cost of these bikes.
For this, Follow the below steps to use VBA Enum:
Step 1: Go to the developer’s tab to click on visual basic in order to open the VB Editor.
Step 2: Insert a new module from the insert tab in the VB Editor.
Step 3: Declare the enum function as motorbikes.
Code:
Enum Motorbikes End Enum
Step 4: Declare the enum list as follows,
Code:
Enum Motorbikes Pulsar = 20 Avenger = 15 Dominar = 10 Platina = 25 End Enum
Step 5: Now declare a sub-function to use these enumerations.
Code:
Enum Motorbikes Pulsar = 20 Avenger = 15 Dominar = 10 Platina = 25 End Enum Sub TotalCalculation() End Sub
Step 6: To use sheet 1 properties, we must first activate the sheet using the following code.
Code:
Enum Motorbikes Pulsar = 20 Avenger = 15 Dominar = 10 Platina = 25 End Enum Sub TotalCalculation() Worksheets("Sheet1").Activate End Sub
Step 7: Now assign the values of the cell by using enumeration as follows,
Code:
Enum Motorbikes Pulsar = 20 Avenger = 15 Dominar = 10 Platina = 25 End Enum Sub TotalCalculation() Worksheets("Sheet1").Activate Range("B2").Value = Motorbikes.Pulsar Range("B3").Value = Motorbikes.Avenger Range("B4").Value = Motorbikes.Dominar Range("B5").Value = Motorbikes.Platina End Sub
Step 8: Run the above code by the run button or press F5 and see the following result in sheet 1.
We can see in the above example that we have added values to the cells using VBA enumeration.
The above examples make it very clear to us what an ENUM in VBA is. ENUM is a numeric variable in VBA which is used to make data and referring to specific values easily. In enum, we group the messages so that they can be used effectively.
Things to Remember
- ENUM is a numeric variable that can be custom-built but is also inbuilt in VBA.
- If we do not assign any values to ENUM values, VBA automatically assigns values to them.
- To use the VBA enumerations in our code, we refer to them by enum name.
Recommended Articles
This has been a guide to VBA Enum. Here we discussed how to make custom enumerations in Excel VBA, practical examples, and downloadable Excel templates. You can also go through our other suggested articles –
