VBA ENUM

Publication Date :

17 May, 2019

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

VBA Enumerations (Enum)

We usually declare variables and assign data types to them. We use common data types: Integer, Long, Single, Double, Variant, and String. But, we have one more data type, VBA “Enum.” You must think about what this is and looks like 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 variable type just like our string, integer, or any other data type, but here we create a list element using the Excel VBA Enum statement. Enumeration means “the action of mentioning several 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, types of mobiles: Redmi, Samsung, Apple, Vivo, Oppo.

Using enumerations, we can group all of them under a single value. For example, one can use the Enum as a variable in VBA, a numeric variable data type of Long.

The formula of VBA Enum

If you do not understand anything, do not worry. You will slowly get the hang of it. Now, take a look at the formula of VBA Enum.

Enum GroupName

     Member1 = 
     Member2 = 
     Member3 = 
     Member4 = 
     Member5 = 

End Enum

As we said in the beginning, one can use Enum as a variable, the numeric variable data type of Long.

Examples of VBA Enum

Before we start the Enum examples, let us 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 Explicit

Const Samsung = 15000

Const VIVO = 18000

Const Redmi = 8500

Const Oppo = 18500

Sub Enum_Example1()

End Sub

We 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, we know all these variables are group members of mobile. So, if we want to use these variables, let us say “Vivo” in the module.

Code:

Sub Enum_Example1()

 V

End Sub

As we start with the character “V,” we can see many other things of VBA mixed up with them, starting with the letter “V.”

It is where the picture of VBA "Enumerations" comes into the picture.

For better understanding, let us try to change the cell's background color.

Code:

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

All these RGB colors are part of the family enumeration called "xlRGBColor."

Code:

Sub Enum_Example1()

  AcriveCell.Interior.Color = xlrg

End Sub

Using these VBA enumerations, we can access this enumeration's group members.

Code:

Sub Enum_Example1()

  AcriveCell.Interior.Color = XlRgbColor.

End Sub

As we can see in the above image, we see only color combinations, nothing else. It is a simple overview of the "VBA Enum."

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

Code:

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

Sub Enum_Example1()

End Sub

We have now declared all the mobile brands under the "Mobiles" group using "Enum" statements.

Using the group name "Mobiles," we can now 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

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

We can see only the group members' "Mobiles" and nothing else. That 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 us see a simple example of using declared Enum variables. First, declare the Enum group name as "Department" and add the 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

We have declared each department's salary numbers in front of them.

Now, we will store the values of these numbers in an Excel sheet. Before applying the code, create a table like the one below.

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

Code:

Sub Enum_Example1()

  Range("B2").Value =

End Sub

In the A2 cell, we have the "Finance" department, so in the B2 cell, we will store the department's salary. So first, access the group name "Department."

Code:

Sub Enum_Example1()

  Range("B2").Value = Dep

End Sub

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

Code:

Sub Enum_Example1()

  Range("B2").Value = Department.

End Sub

Select the department named “Finance.”

Code:

Sub Enum_Example1()

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

End Sub

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