VBA Enum

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 –

1. VBA Range | Excel Tutorials
2. Grouping Columns in Excel
3. VBA Active Cell
4. VLOOKUP Tutorial in Excel