VBA ENUM

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 of them under a single value. Enum can be used as variables in VBA, and it is a numeric variable data type of LONG.

VBA ENUM

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA ENUM (wallstreetmojo.com)

The formula of VBA Enum

If you do not understand 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 the “Constant” example in VBA. Constant is also a word used to declare the variable in VBA.

Look at the below codes.

Code:

Option ExplicitOption ExplicitVBA option explicitly makes a user mandatory to declare all the variables before using them; any undefined variable will throw an error while coding execution. We can enable it for all codes from options to require variable declaration.read more

   Const Samsung = 15000
   Const VIVO = 18000
   Const Redmi = 8500
   Const Oppo = 18500

Sub Enum_Example1()

End Sub
VBA Enum Example 1

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, let us say “Vivo” in the module.

Code:

Sub Enum_Example1()

 V

End Sub
VBA Enum Example 1-1

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.

Code:

Sub Enum_Example1()

  AcriveCell.Interior.Color = RGB

End Sub
Enumerations Example 1-2

As you can see in the above code, we can see all the RGB colors available in VBAThe RGB Colors Available In VBAThe RGB function is used to obtain the numerical value of a colour value. This function contains three named range components: red, blue, and green. Other colours are considered components of these three different colours in VBA.read more. These are all constants with wonderful names with it.

Actually, all these RGB colors are part of the family enumeration called “xlRGBColor.”

Code:

Sub Enum_Example1()

  AcriveCell.Interior.Color = xlrg

End Sub
Enumerations Example 1-3

By using these VBA enumerations actually, I can access to all the group member of this enumeration.

Code:

Sub Enum_Example1()

  AcriveCell.Interior.Color = XlRgbColor.

End Sub
Enumerations Example 1-4

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 the VBA Enum statement.

Code:

Enum Mobiles
   Samsung = 15000
   VIVO = 18000
   Redmi = 8500
   Oppo = 18500
End Enum

Sub Enum_Example1()

End Sub
Enumerations Example 1-5

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.

Code:

Enum Mobiles
   Samsung = 15000
   VIVO = 18000
   Redmi = 8500
   Oppo = 18500
End Enum

Sub Enum_Example1()

  Mob

End Sub
Enumerations Example 1-6

Select the group and put a dot to see all the members of the group.

Enumerations Example 1-7

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.

Code:

Enum Mobiles
   Finance = 150000
   HR = 218000
   Sales = 458500
   Marketing = 718500
End Enum

Sub Enum_Example1()

End Sub
Using Enum Variables 1

I have declared each department’s 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.

Using Enum Variables 1-1

Now go back to the basic visual editor and refer the cell B2 by using the RANGE object.

Code:

Sub Enum_Example1()

  Range("B2").Value =

End Sub
Using Enum Variables 1-2

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.”

Code:

Sub Enum_Example1()

  Range("B2").Value = Dep

End Sub
Using Enum Variables 1-3

Now in this group, we can see only declared department names.

Code:

Sub Enum_Example1()

  Range("B2").Value = Department.

End Sub
Using Enum Variables 1-4

Select the department named “Finance.”

Code:

Sub Enum_Example1()

  Range("B2").Value = Department.Finance

End Sub
Using Enum Variables 1-5

Similarly, for all the other cells, select the respective department names.

Code:

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
Using Enum Variables 1-6

Run this VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more we will get the assigned salary amount for these departments.

Using Enum Variables 1-7

This is how we can use VBA Enum.

You can download this VBA Enum Excel here. VBA Enum Excel Template

Recommended Articles

This has been a guide to what is VBA Enum (Enumerations). Here we discuss how we can use VBA Enum along with examples & download an excel template. Below are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Comments

  1. Shady Mohsen says

    Thanks for this tutorial. Very useful and helful

    • Dheeraj Vaidya says

      Thanks for your kind words!