Open In App

VBA Constants in Excel

Last Updated: 24 Jul, 2022

G

Gautam goel
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

There can be situations when you want to declare a number in your program that you never want to be changed. In other situations, like, as if you have used a person’s name many times in your program and you want to change that name, this assigned task could be hectic if you change the name of that person at every single position you have used in your program, but, if you know constants then this task could be completed within seconds with the help of constants. In this article, you will learn how to use constants in excel VBA.

Constants in VBA

Constant is a convenient label for which value doesn’t change. Many times you have come across the constants in VBA unknowingly. A major difference between a variable and a constant is that you cannot reassign the value of a constant. For example, if you want to change the color of the cell to read, then you might use VbRed in your VBA macro.

Using constants in your code

  1. Constants help in writing defensive code so that one cannot change the value of that constant in the future. Consider a situation where you have created a module declaring all the constants and password-protected it. The scope of the constants is workbook level. Another person working in some other module cannot access the constants, thus not changing its value.
  2. Creating constants makes code more readable. For example, every person might not know what is 9.8 until mentioned its value of acceleration due to gravity.
  3. Constants save your time. Consider a situation you have used someone’s name in the code, and now you want to change it. Changing the name at every position is time taking. You can use constants by which changing the name of that person at the time of constant declaration will change the name in the entire code.

Constants can be of two types

  1. Built-In Constants
  2. User-Defined Constants

Built-In Constants

VBA provides 100+ in-built constants to VBA users. There are boolean, color, and date-type-based constants in VBA. For example, vbRed, vbBoolean, and vbDate. For example, given the cell value of C2 as “Arushi practices dsa on geeks for geeks”. Your task is to change the color of the cell to black and set the font as green using in-built VBA constants.

C2-cell-value-filled

Step 1: Open your VBA editor. The name of the procedure is geeks(). Set the color of cell C2 to black. Here, we have used the in-built constant vbBlack to color the cell. The function used to achieve this is Range(cell).Interior.Color.

Using-in-built-constant-vbBlack

Step 2: Set the font of the text inside C2 to green by using the VBgreen in-built constant. The function used to achieve this is Range(cell).Font.Color.

Using-vbGreen-in-built-constant

Step 3: Run your macro. The text color is changed to green and the background color to black.

Running-macro

Internal Working of Constants

From the above example, it might seem that the constant vbGreen changes your text color green, but that’s the half-truth. In reality, the majority of constants are numeric values. The numeric value of vbGreen is 65280. Different Methods to know the numeric value of the constant:

Method 1: Use F8

Step 1: Open your VBA editor. Under a sub procedure. Press Fn + F8, on your keyboard. Now, a yellow line may highlight the subprocedure i.e., geeks().

Highlighting-in-yellow

Step 2: Now, if your hover your cursor on the constant, it will show the numeric value of that constant. For example, the numeric value of vbGreen is 65280.

Numeric-value-of-vbGreen

Method 2: Use print in VBA

You can print the numeric value of the constant in the immediate window itself.

Step 1: Use Debug.Print(constant) in the code. Click on the run.

Printing-numeric-value

Step 2: The value of the constant will be printed in the immediate window.

Value-printed-in-immediate-window

Getting a List of built-in Constants

VBA provides a detailed list of in-built constants. You can access the name and numeric value by referring to the list.

Step 1: Open your VBA code editor. Click on the View Tab.

Clicking-view

Step 2: Click on the Object Browser or press F2.

Clicking-object-browser

Step 3: A section named Members of ‘<globals>’ is opened. Here we have the list of all the constants in VBA.

List-of-constants-in-VBA

Step 4: Click on any of the cells to get the numeric value of that constant.

Obtaining-numeric-value-of-cells

User-Defined Constants

You can get into situations when you want to define your custom constants. For example, the mathematical constants like PI(3.14), h(plank’s constant = 6.626 \times 10^{-34}) etc. Here, you will require the knowledge of user-defined constants.

Converting RGB colors to a numeric value

Before learning how to declare constants in VBA, you need to know how to convert an RGB number to its numeric value. This will be helpful to know which numeric value we have to assign our constant to achieve the specified color. For example, find the numeric value of the custom black color.

Step 1: Go to the Home tab. Under the Font section, click on the theme color. Now, click on More colors.

Clicking-theme-color

Step 2: A colors tab is open. Select the custom menu. You can see the value of R, G, and B, i.e., 7, 36, and 2. Click Ok.

Selecting-custom-menu

Step 3: Now, open your VBA editor. In the immediate window, type the ?RGB(red_value, green_value, blue_value). Press Enter. You will obtain the numeric value of that color i.e., 140295.

Obtaining-numeric-value-of-that-color

Declaring Custom Constants in VBA

The custom constants are declared the same way as variables are declared, but instead of using the ‘Dim’ keyword, we use the ‘Const’ keyword in the syntax.

Syntax: Const constant_name as data_type = value_assigned

For example, given the cell value of C2 as “Arushi practices dsa on geeks for geeks”. Your task is to change the color of the cell to black and set the font as green using custom VBA constants.

Changing-color-in-cell

Step 1: Declare the constant black in the sub-procedure scope. As the numeric value of black is 0. So the constant is set to 0.

Constant-set-is-0

Step 2: Set the color of the cell to black by using Range(cell).Interior.Color function, assigned by a constant to black.

Assigning-constant-to-black

Step 3: Repeat steps 1 and 2. Declare the numeric value of green, i.e., 65280, which can be found by rgb to the numeric converter as shown above. Set the font color by Range(cell).Font.Color function, assigned by constant to green.

Declaring-custom-constant

Step 4: The background of cell C2 is set to black and the font color to green.

Cell-color-set-black-and-font-green

Scope of Constants in VBA

The constants are declared according to their scopes. Some constants have only sub-procedure scope; some might have module scope etc.

  • Scope in a procedure

The constant declared in a procedure has its scope within the procedure itself. The constant black declared inside the procedure geek() will not work for any other procedure or module.

Declaring-custom-constant-for-black-color
  • Scope in a module

The constant is declared outside a procedure, and the scope of the constant is valid for the entire module. That constant can be used in any procedure or sub-procedure, or function.

Scope-in-a-module
  • Scope in all modules

The scope of the module can be increased to all the modules by using the keyword public while declaring the constant. Now, the scope of constant is for all the modules.

Scope-in-all-modules


Article Tags :

G

Gautam goel
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

A

Ayush Dey

VBA Subroutine in Excel - How to Call Sub in VBA?

article_img
When a specified action is performed on a worksheet with the help of a collection of code known as a VBA Subroutine. It also helps to read an external file, also it can open other applications from Excel. A large piece of code can be broken into small parts so that we can manage it easily. Let's learn why to use submarines: Converts large piece of codes into small parts so that the computer ignores all kind of complexities that arises because of large codesReusability of code suppose we in a program have to access the database frequently so instead of writing the code again and again we can create a function to access the databaseSubroutines are self-documenting functions which means a coder can easily say what the program does by looking into the name of the function Naming Rules of SubroutinesIt can start with a letter or an underscore but it cannot start with a number or a special character.It cannot contain any space in the name.The name of the subroutine cannot be a keyword like Private, Sub, End, etc. Syntax Private Sub function_name( ByVal arg1 As String, ByVal arg2 As String) End Sub Syntax Explanation Code Action "Private Sub function_name(...)"Private is the keyword whic
Read More

R

Rishabh Chakraborty

How to Use Select Case Statement in Excel VBA?

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. In this article, we are going to discuss how to use Select Case Statement in Excel VBA. Select Case Statement of Excel VBA The select case in VBA is like a detective that investigates an expression by checking it against different scenarios listed as Case Statements, each with its own conditions. When a scenario matches the expression, it's like solving a piece of the puzzle, and the corresponding code linked to that scenario is activated. Importantly, once a match is found, the detective stops investigating and moves on to executing the discovered code. Yet, if none of the scenarios match, it's like the detective hitting a dead end. In this situation, the code associated with the Case Else statement comes into play, offering a default solution or outcome. Syntax of the Select Case Statement of Excel VBAThe VBA Select Case Statement shares similarities with the Switch Case construct found in programming languages such as Java, C#, and PHP. In Excel VBA, this statement helps
Read More

R

Rishabh Chakraborty

Excel VBA Concatenation Operators

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Concatenation means to join two or more data into a single data. There are various ways we can perform concatenation in Excel using built-in functions, operators, etc. Some helpful links to get more insights about concatenate and using VBA in Excel : Record Macros in ExcelCONCATENATE in ExcelHow to Create a Macro in Excel? In this article, we are going to see about concatenate operators and how to use VBA to concatenate strings as well as numbers. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs at the top of the Excel window.Now select Customize the Ribbon from the pop-down menu.In the Excel Options Box, check the box Developer to enable it and click on OK. Now, the Developer Tab is visible. Now, we need to open the Visual Basic Editor. There are two ways : Go to Developer and directly click on the
Read More

R

Rishabh Chakraborty

Excel VBA Comparison Operators

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. Some helpful links to get more insights about Macros, VBA in Excel : 1. Record Macros in Excel. 2. How to Create a Macro in Excel? In this article, we are going to discuss various comparison operators in Excel VBA. Implementation : In the Microsoft Excel tabs, select the Developer Tab. Initially, the Developer Tab may not be available. The Developer Tab can be enabled easily by a two-step process : Right-click on any of the existing tabs in the top of the Excel window.Now select Customize the Ribbon from the pop-down menu. The Excel Options Box, check the box Developer to enable it and click on OK. Now, the Developer Tab is visible. Now click on the Visual Basic option in the Developer tab and make a new module to write the program.Developer -> Visual Basic -> Tools -> Macros Now create a Macro and give any suitable name. This will open the Editor window where can write the code. Comparison Operators in Excel:S.No.OperatorsDefinition 1<> Not equal operator is
Read More

A

Akshit

How to Use for Each Loop in Excel VBA?

A For Each loop is used to execute a statement or a set of statements for each element in an array or collection. Syntax: For Each element In group [ statements ] [ Exit For ] [ statements ] Next [ element ] The For...Each...Next statement syntax has the following three parts: PartDescriptionelement Required (Must be mentioned). Variable is used to iterate through the elements of the collection or array. For collections, the element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, the element can only be a Variant variable.groupRequired(Must be mentioned). Name of an object collection or array (except an array of user-defined types).statementOptional (May or may not be mentioned). One or more statements are executed on each item in the group. There are 4 basic steps to writing a For Each Next Loop in VBA: Declare a variable.Write the For Each Line with the variable and collection references.Add line(s) of code to repeat for each item in the collection.Write the Next line to terminate the loop. The For…Each block is entered if there is at least one element in the group. Upon entering the loop,
Read More

A

Akshit

How to Use Do While Loop in Excel VBA?

article_img
A Do…While loop is used when we want to repeat certain set of statements as long as the condition is true. The condition may be checked at the starting or at the end of the loop Flowchart: Uses of Do-While loop: The Do While loop is used in two ways: Do…while loop which checks the condition at the STARTING of the loop.Do…while loop which checks the condition at the END of the loop. Syntax 1: Do While condition [statements] [Exit Do] [statements] Loop Syntax 2: Do While [statements] [Exit Do] [statements] Loop conditionImplementing a Do While loop: Follow the below steps to implement a Do-While loop: Step 1: Define a Macro Private Sub Demo_Loop() End Sub Step 2: Define variables j=2 i=1 Step 3: Write Do While Loop. You can write condition at the beginning or at the end Do While i < 5 Step 4: Write statements to be executed in loop msgbox "Table of 2 is : " & (j*i) i=i+1 Step 5: End loop. Now let's take a look at some of the examples. Example 1: Do…while loop which checks the condition at the STARTING of the loop. The below example uses Do…while loop to check the condition at the starting of the loop. The statements inside the loop are executed, only if the condition is True
Read More

S

Shlok Dayma

How to Use For Next Loop in Excel VBA?

article_img
If you are familiar with the programming you must have an idea of what a loop is, In computer programming, a loop is a sequence of statements that are repeated until a specific condition is satisfied. In Excel VBA the "For Next" loop is used to go through a block of code a specific number of times. Syntax: For counter = start to end [step count] [code block to execute] statement 1 statement 2 statement 3 . . statement n Next [counter] Here we can use the counter or any other variable to run them as many times as we need. Example: When you are displaying numbers from 1 to 10 you may want to set the value of a variable to 1 and display it 10 times, increasing its value by 1 on each loop iteration. The same logic is used in VBA. We specify how many times we have to run the loop, and then specify what code should our loop execute each time the loop runs. A loop has 3 parts the first one is an initialization, the second is the condition under which the loop runs, and the last is increment or decrement. The flow of the control in for loop:The For step is executed first. This step allows you to initialize any loop control variables and increment the step counter variable.Then the second
Read More

A

Asha Senthilkumar

How to Get Length of Array in Excel VBA?

article_img
We use UBound and LBound functions to get the length of an Array in Excel VBA. In this article, we will discuss them in detail. Syntax: UBound() function UBound (arrayname, [ dimension ]) Parameters: arrayname: required. Array variable namedimension: optional Returns: Return upper limit of an array dimension. Syntax: LBound() Function LBound (arrayname, [ dimension ]) Parameters: arrayname : required. Array variable namedimension : optional Returns: Return lower limit of an array dimension Sample Data: VBA Code to get the length of Array (one-dimensional array): Declare Variables: Declaring a customer array with the size of 10. Sub oneDimArrayLength() ' Array variable Declaration Dim customer (1 To 10) As String Assign values to array elements customer(1) = "ANTON" customer(2) = "BERGS" customer(3) = "BOLID" customer(4) = "KOENE" customer(5) = "FRANS" Use UBound function to get the size of an array and Message box to display the result 'Message box to popup length of 1D array MsgBox "Array has " & UBound(customer) & " element(s)." End Sub To Run VBA Code Press Alt+F8 to popup macro window. Select " oneDimArrayLength" and Click Run button. Output VBA Code to get the length
Read More

A

Asha Senthilkumar

How to Convert Multiple PowerPoint Files Into Pdf with Excel VBA?

article_img
Often clients need PPT files as PDFs. It helps to view on any device. Use below VBA Macro to convert PowerPoint files from a folder and save them as PDF in the same folder. Implementation: Follow the below steps to convert multiple PowerPoint files into PDFs using Excel VBA: Step 1: Open Excel. Step 2: Type text “Folder Path” in cell “B5” (Image 1). Step 3: Enter your folder full path in cell “C5” (Image 1). Here we have a folder “D:\Excel\29.ppt2pdf\ppt” with two PPT files (Image 2). Image 1Image 2 Step 4: Write below VBA code in your VBE module Sub ppt2pdf_Macro() Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Dim onlyFileName As String, folderPath As String, pptFiles As String, removeFileExt As Long Application.ScreenUpdating = FalseInitialize variablesfolderPath = Range("C5").Text & "\" pptFiles = Dir(folderPath & "*.pp*")Check and exit macro if no ppt files are in the folderIf pptFiles = "" Then MsgBox "No files found" Exit Sub End If Do While pptFiles <> ""Assign PowerPoint application to variable Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue On Error Resume NextAssign PowerPoint presentation to
Read More

G

Gautam goel

How to Add a Comment in a VBA in Excel?

article_img
VBA Macro is for developers. Macro is a piece of code written in VBA. VBA is Microsoft's programming language and it stands for Visual Basic for Applications. Let's see how to set up our VBA Macro and how to add comments in a VBA in Excel. VBA Macro Excel is a very advanced tool that contains thousands of functionalities, but VBA Macro comes into existence when we have to do a repeated task. The complex repeated tasks can be automated with the help of VBA Macro. Initial Set-Up Go to Developer Tab. We will see that by default developer tab is not present in the menu bar. Follow the steps: Step 1: Right-click on any tab in the menu bar. Click on Customize the Ribbon. A dialogue box appears. Step 2: In the Mains Tab, check the box Developer and click on Ok. Step 3: Now the Developer Tab is visible. Step 4: Go to Developer Tab and click on Visual Basic. Step 5: Now, the VBA tab is opened. Click on Tools in the menu bar and then click on Macros. A dialogue box is open. Step 6: Write the macro name and click on create. A Macro is created. VBA Comments Comments are the lines in the code that are ignored while executing the code. These are represented as green text in the code.
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox