EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Coding VBA Enum
 

VBA Enum

Madhuri Thakur
Article byMadhuri Thakur

Updated June 9, 2023

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, whether JAVA, C, or Excel VBA. In VBA, we have some inbuilt enumerations which start with VB, like VBnewLine is used to insert a new line. It is an enumeration. In Excel VBA, we can also create our custom enumerations.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

In VBA, Enum is a variable, just like an 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. We need to remember that an enum is like a numeric variable that can be used as a representation of one interpretation. There is also another way to use status messages by using a constant. Still, they can be tiresome because they are not grouped and come along with all the other enumerated variables of VBA. But by 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. Still, if we do not provide any value to it, VBA automatically gives the first enum value as 0, the next one as 1, and so on.

So as I said above about a motorbike company manufacturing bikes, the enumeration for it will be 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, the pulsar is zero, and the 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 essential in VBA; then, we will move to examples of 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 not be required for us. Have a look at the screenshot below.

Enumeration

We can see that in the above screenshot, there are many enumerations starting from A. Those are inbuilt VBA enumerations.

Now let us learn how to make custom enumerations in VBA with a few examples of ourselves.

Note: In order to use Excel VBA ensure to have the developer’s tab enabled from the files tab and options section.

How to Make Custom Enumerations in Excel VBA

We will learn how to make custom Enumerations (Enum) with a few examples in Excel VBA.

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

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 personalized messages.

Follow the below steps to use VBA Enum:

Step 1: In the developer’s tab, click on Visual Basic to open VB Editor.

VBA Enum Example 1-1

Step 2: Insert a new module from the insert tab in the VB Editor.

VBA Enum Example 1-2

Step 3: Declare the Enum function with an enum name.

Code:

Enum Greetings

End Enum

VBA Enum Example 1-1

Step 4: Declare the enum values as follows,

Code:

Enum Greetings

Morning = 1
Afternoon = 2
Evening = 3
Night = 4

End Enum

VBA Enum Example 1-2

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

VBA Enum Example 1-3

Step 6: Display a personalized message using the msgbox function.

Code:

Enum Greetings

Morning = 1
Afternoon = 2
Evening = 3
Night = 4

End Enum

Sub UseEnum()

MsgBox "Greeting Number"

End Sub

VBA Enum Example 1-4

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

VBA Enum Example 1-5

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

VBA Enum Example 1-6

Step 9: Once done, compile and run the complete code by clicking on the play button located just below the menu bar. When we run the code, we see the following result.

Result of Example 1-7

It was very easy to create and use an enumeration; this is clear from the above example.

Excel VBA Enum – Example #2

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 pre-written calculations in sheet 1 as follows.

VBA Enum Example 2-1

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.

VBA Enum Example 2-2

Step 2: Insert a new module from the insert tab in the VB Editor.

VBA Enum Example 2-3

Step 3: Declare the enum function as motorbikes.

Code:

Enum Motorbikes

End Enum

VBA Enum Example 2-4

Step 4: Declare the enum list as follows,

Code:

Enum Motorbikes

Pulsar = 20
Avenger = 15
Dominar = 10
Platina = 25

End Enum

VBA Enum Example 2-5

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

Example 2-6

Step 6: To use sheet 1 properties, we must first activate the sheet using the following code.

Code:

Enum Motorbikes

Pulsar = 20
Avenger = 15
Dominar = 10
Platina = 25

End Enum

Sub TotalCalculation()

Worksheets("Sheet1").Activate

End Sub

Example 2-7

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

Example 2-8

Step 8: Run the above code by the run button or press F5 and see the following result in sheet 1.

Result of Example 2-9

We can see in the above example that we have added values to the cells using VBA enumeration.

The above examples make it 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 that can be custom-built but 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 to 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, practical examples, and downloadable Excel templates. You can also go through our other suggested articles –

  1. VBA Range
  2. VBA Month
  3. Excel VBA Macro
  4. VBA Login

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Enum Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW