ENUM is a short form for enumerations, almost in every programming language we have enumerations some are pre-defined and some are user-defined enumerations, like in VBA Vbnewline is an enumeration and we can make our own enumerations using ENUM statement.
VBA Enumerations (Enum)
We usually declare variables and assign data types to them. The common data types we use are “Integer, Long, Single, Double, Variant, and String”. But we have one more data type i.e. VBA “Enum”. You must be thinking what is this and looks a strange thing, but to clear all your doubts we are presenting this article on “VBA Enumerations”
What is VBA Enum?
“Enum” means Enumerations. Enum is a type of variable just like our string or integer or any other data type but here we create a list element using Excel VBA Enum statement. Enumeration means “the action of mentioning a number of things one by one”.
In Excel VBA Enum is a type that contains an enumeration of constants. Enumerations create a list of items and make them in a group. For example type of mobiles: “Redmi, Samsung, Apple, Vivo, Oppo”.
Using enumerations we can group together all them under a single value. Enum can be used as variables in VBA and it is a numeric variable data type of LONG.
Formula of VBA Enum
If you are not understanding anything don’t worry you will slowly get the hang of it. Now take a look at the formula of VBA Enum.
Enum GroupName Member1 = [Long] Member2 = [Long] Member3 = [Long] Member4 = [Long] Member5 = [Long] End Enum
As I told in the beginning Enum can be used as a variable and it is the numeric variable data type of Long.
Examples of VBA Enum
Before I start the Enum examples let me show you “Constant” example in VBA. Constant is also a word used to declare the variable in VBA.
Look at the below codes.
Option Explicit Const Samsung = 15000 Const VIVO = 18000 Const Redmi = 8500 Const Oppo = 18500 Sub Enum_Example1() End Sub
I have declared the variables at the top of the module by using the Const word.
Const Samsung = 15000
Const VIVO = 18000
Const Redmi = 8500
Const Oppo = 18500
Now I know all these variables are a group member of Mobile. If I want to use these variables lets say “Vivo” in the module.
Sub Enum_Example1() V End Sub
As I start the character “v” I can see many other things of VBA mixed up with them were the things which start with the letter “v”.
This is where the picture of VBA “Enumerations” comes into the picture.
For better understanding let’s try to change the background color of the cell.
Sub Enum_Example1() AcriveCell.Interior.Color = RGB End Sub
As you can see in the above code we can see all the RGB colors available in VBA. These are all constants with wonderful names with it.
Actually, all these RGB colors are part of the family enumeration called “xlRGBColor”.
Sub Enum_Example1() AcriveCell.Interior.Color = xlrg End Sub
By using these VBA enumerations actually, I can access to all the group member of this enumeration.
Sub Enum_Example1() AcriveCell.Interior.Color = XlRgbColor. End Sub
As we can see in the above image we see only color combinations nothing else. This is what the simple overview of the “VBA Enum”.
Ok, now we will go back to our original example of Mobile group members. Like how we have seen group members of RGB color similarly we can declare the variables by using VBA Enum statement.
Enum Mobiles Samsung = 15000 VIVO = 18000 Redmi = 8500 Oppo = 18500 End Enum Sub Enum_Example1() End Sub
Now I have declared all the mobile brands under the “Mobiles” group by using “Enum” statements.
By using the group name “Mobiles” now I can access all these brands in the module.
Enum Mobiles Samsung = 15000 VIVO = 18000 Redmi = 8500 Oppo = 18500 End Enum Sub Enum_Example1() Mob End Sub
Select the group and put a dot to see all the members of the group.
Look we can see only the group members of the group “Mobiles” nothing else. This is how we can use VBA Enumerations to group a list of items under one roof.
Using VBA Enumeration Variables to Store the Data
Let’s see a simple example of using declared Enum variables. Declare Enum group name as “Department” and add department’s names as the group member.
Enum Mobiles Finance = 150000 HR = 218000 Sales = 458500 Marketing = 718500 End Enum Sub Enum_Example1() End Sub
I have declared each department salary numbers in front of them.
Now we will store the values of these numbers to excel sheet. Before applying the code create a table like below.
Now go back to the visual basic editor and refer the cell B2 by using RANGE object.
Sub Enum_Example1() Range("B2").Value = End Sub
In A2 cell we have the Finance department and so in B2 cell, we will store the salary of this department. So first access the group name “Department”.
Sub Enum_Example1() Range("B2").Value = Dep End Sub
Now in this group, we can see only declared department names.
Sub Enum_Example1() Range("B2").Value = Department. End Sub
Select the department name “Finance”.
Sub Enum_Example1() Range("B2").Value = Department.Finance End Sub
Similarly, for all the other cells select the respective department names.
Sub Enum_Example1() Range("B2").Value = Department.Finance Range("B3").Value = Department.HR Range("B4").Value = Department.Marketing Range("B5").Value = Department.Sales End Sub
Run this VBA code we will get the assigned salary amount for these departments.
This is how we can use VBA Enum.
You can download this VBA Enum Excel here. VBA Enum Excel Template
This has been a guide to what is VBA Enum (Enumerations). Here we discuss how we can use VBA Enum along with examples & download excel template. Below are some useful excel articles related to VBA –