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 be it JAVA, C or excel VBA. In VBA we have some inbuilt enumerations which starts with VB like VBnewLine is used to insert a new line. It is an enumeration. In Excel VBA we can also create our own custom enumerations.
In VBA, Enum is actually a variable just like 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. The one thing which we need to keep in mind is that enum is like a numeric variable which can be used as a representation of one interpretation. There is also another way to use status messages by using constant but they can be tiresome because they are not grouped and come along with all the other enumerated variables of VBA. But with 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 but if we do not provide any value to it VBA automatically gives the first enum value as 0 and the next one as 1 and so on.
So as above I said about a motorbike company manufacturing bikes the enumeration for it will be such 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 pulsar is zero and 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 important in VBA then we will move to examples on 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 be not required for us. Have a look at the screenshot below.
We can see that in the above screenshot that there are many enumerations starting from A. Those are inbuilt VBA enumerations.
Now let us learn how to make custom enumerations in VBA by 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 a personalized message.
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 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 which is located just below to the menu bar. When we run the code we see the following result.
It was very easy to create an enumeration and use them this is clear from the above example.
Excel VBA Enum – Example #2
Now 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 sort of pre-written calculation 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: We know in order to use sheet 1 properties we need to activate the sheet first by 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.
Now by the above examples, it is 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 which can be custom built but it 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 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 along with practical examples and downloadable excel template. You can also go through our other suggested articles –
