Recognizing Constants And Variables In Vba Excel #05

As I said in the previous tutorial, after discussing Data Types in VBA, we will continue this Basic VBA tutorial series by discussing the terms Constants and Variables in VBA and how to use them in an Excel VBA code.

If you are familiar with using excel formulas I am sure you will quickly understand the meaning of these two terms ( Constant and Variable ). Because, perhaps unconsciously you have actually used Constants or Variables as part of a certain excel formula element.

Wow, don’t tell me you still don’t understand the elements that make up excel formulas. If this is true, before continuing to read the explanation about VBA Constants and Variables.

Besides that, we will also invite you to understand more about the term Scope or the scope of VBA and the term VBA lifetime.

Read slowly and don’t forget to prepare coffee and snacks first.

Excel VBA Constants

What is the meaning of Constants in VBA Excel?

In the previous discussion, I said that basically Excel VBA sometimes stores data as an object and sometimes stores data as a variable. Data storage in the form of variables that are fixed and does not change are commonly referred to as constants.

So Constants in Excel VBA is a repository for a certain value where this value is fixed and cannot change. This means that when used Constants are read only where the Value in the constant is set when declaring the constant.

If you write an excel formula, for example:

=A1*2

Then the number 2 in the formula is a constant, different from the value of A1, which we can change according to the value we input in cell A1.

Can we change the number 2 with another number? Yes, it’s true, but we can’t change the meaning here, it’s not like that. So after we set the value 2 in the formula, the value 2 is used in the excel formula. Meanwhile, without changing the formula we can change the value of A1. I’m sure you’re smart enough to get the message I want to convey.

In VBA Excel, to declare a constant follows a certain syntax or way of writing. Here’s a further explanation.

How to Declare Constants

There are 2 types of constants in Excel VBA:

1. Intrinsic Constants , are constants that have been provided by VBA ( Visual Basic For Application ). Characteristics begin with the word vb or xl at the beginning of the constant, for example: vbYesNo and xlDialogOpen . Regarding the default constant ( Intrinsic ) we will discuss on another page when it is needed.
2. Symbolic Constants are constants whose values ​​are determined by the user (you) using the keyword “Const” . This type of VBA constant is intended in the discussion on this page.

Constants are created or declared with the Const keyword with the minimum scope level being at the Module level scope . We will discuss this scope in more detail later below.

The constant declaration syntax is as follows:

scope CONST Constant_Name AS DataType = Constant_Value

Information:

• You can fill the Scope element with Public or Private according to the scope you need. If you don’t include this Element then VBA will consider Constants declared as Public scope .
• CONST is a keyword that indicates that you are declaring constants in the VBA module or procedure you are creating
• Constant_Name is the name you want to use for the constant in question to distinguish one constant from another.
• DataType is the type or data type that you want to use in the constant as explained in the previous tutorial.
• Constant_value is the value you want to assign to the constant, of course it must match the data type you specify.

Constant Declaration Example

Here’s a simple example of writing a constant declaration:

Public Const Author As String = "Raiyan"

Private Const BlogName As String = "InfactDaily"

For now I think there is no need to extend the discussion about Constants in VBA. In the next section I will invite you to get to know and learn more about VBA Variables.

You can find a discussion of VBA Data Types here: Understanding Data Types in Excel VBA

Excel VBA Variables

Variables in Excel VBA is a part that I think is very important for you to master. Because…. why? it’s really important. Ha ha ha ha. Please define yourself the importance of learning the meaning of and how to use this VBA Variable after you finish reading and practicing this basic VBA tutorial.

What is a VBA Variable?

A variable is the name of a memory location used to store or represent a specific value that can be changed during the execution of VBA code.

Basically Variables are the same as constants that have a specific name and data type, the difference is that when the code runs the constant value cannot be changed while the variable value can be changed to manipulate data.

So if constants only able to read or retrieved value ( Read ), Variable besides being able to take its value can be written or changed the value stored therein ( Read and Write ).

From the above understanding, we can simplify that the variable has the following 3 characteristics:

1. Has a specific name.
2. Is a location where values ​​or data are stored in computer memory.
3. Used by a program.

From the three main characteristics of the variables above, I think you can make reference to what you need to understand after reading the VBA Variables guide in this section so that you are able to declare variables correctly when writing Excel VBA Macro codes, namely:

1. How do you determine how values ​​or data are stored. This discussion is closely related to how you determine the VBA datatype you are using and how to define or declare a variable.
2. How do you name a VBA variable.
3. How do you determine where a Variable can be used or accessed. This discussion is closely related to the Scope and Lifetime of Variables .

Before discussing Constants and Variables in Excel VBA, I first chose to discuss VBA Data Types in the previous Basic VBA tutorial article. So this time I don’t have to do it again.

Benefits of Declaring VBA Variables

Actually you can just compile VBA code without making Variable declarations. It’s just that as we discussed in the previous series, by explicitly declaring variables , the VBA macro code that we create will run faster and more efficiently in memory usage.

Some other reasons or benefits why you should do variable declarations are as follows:

1. Allows you to use the AutoComplete feature to avoid typing errors in variable names when calling or intending to use certain variables.
2. Make VBA perform additional error or error checks. Suppose you declare a variable as an integer because you intend to fill the value of the variable with a number, but you fill it with text accidentally. If this is the case then VBA will give error information that you entered an incorrect value in the variable.
3. Declaring a variable will increase the Readability or readability of a code. This makes the Debug process easier and reduces the risk of certain errors or errors.

This is the reason why you should enable the “Option Explicit” option as explained in the first series of this VBA tutorial. The Option Explicit statement that you use at the start of a module will force you to always define or declare a Variable.

Another thing that I find quite useful is that by using this Option Explicit statement , the Visual Basic Editor automatically corrects the spelling of the VBA variable names you create.

How to Declare VBA Variables

Now, how to do variable declaration? The syntax for declaring a VBA Variable is as follows:

Keyword Variable_Name As Data_Type

• “Keyword” is one of the following statements: Dim, Public, Private or Static .
• “Variable_Name” is the name you want to use as the variable identifier.
• “Data_Type” is a reference to the data type of the variable. Although this Element is optional or may not be used, I recommend you to always use it, unless you are really confused about what Data Type you should use in the variables you declare.

An example of a VBA Variable declaration is as follows:

Dim serial number As Long

Dim code_number As String

The code above intends to declare 2 variables with the names sequence_number and code_number . We specify the serial_number variable using the Long data type, while the code_number we specify as a String or text.

To make writing lines of code more concise, you can also use 1 Dim keyword to declare several variables at once by separating them using commas (,). Examples are as follows:

Dim sequence_number As Long, code_number As String

In the past ( even now ) I often summarized the writing of variable declaration code such as the following:

Dim A_number, B_number As Integer

With the intention of declaring variables A_Number and B_Number as Integer values.

You need to know that when writing a variable declaration statement like this, VBA only defines variable_number B as an Integer, while for variable_number A because the data type is not stated, it will be considered as Variant as the default data type in VBA.

So, writing the proper declaration code should be as follows:

Dim_number A As Integer,_number B As Integer

 

VBA Variable Naming Rules

Basically you are free to name a variable. But it would be better if the name also reflects the value or content of the variable. For example, use a data type abbreviation and then connect it with a name that describes the value of the variable.

Here are some abbreviations that are commonly used as prefixes or the prefix of Variable names:

Example Variable Name:

1. To name a serial number variable of type Integer use the name: intNoUrut, int_No_Order or int_NoUrut.
2. For Variables that store people’s names, for example, use the names str_Name or s_Name where str or s stands for String data type.

In addition there are some special rules that you must obey when specifying the name of a Variable:

1. Uses a letter as the first character. Subsequent characters can be letters, numbers, and some punctuation characters such as underscore (_).
2. Variable names cannot exceed 255 characters.
3. VBA variable names cannot contain spaces (), periods (.), mathematical operators (such as +, -, /, ^ or *), comparison operators (such as =, < or >) or certain punctuation characters (such as type -declaration of characters @, #, $, %, & and !).
4. VBA variable names cannot match any keywords or terms already present or used by default by Excel’s VBA system. For example: Sub, Dim, With, End, Next, For and so on.
5. Visual Basic for Applications does not distinguish between uppercase and lowercase letters. For example, “A” equals “a”.
6. The name of each VBA variable used must be unique within the relevant scope. This means that the name of the procedure-level variable must be unique within the relevant procedure, the name of the module-level variable must be unique within its module, and so on.

How to Enter Values ​​In VBA Variables

After declaring a Variable, how do you put a value into it?

If you read this tutorial in order from the first series, I’m sure you already understand that there are basically 2 types of variables. Namely variables with object and non-object datatypes. For each of these types the way to enter the value is different:

Variables with datatypes other than objects, the method is to write the name of the variable followed by an equal sign (=) and the value of the variable.

Variable_Name = Variable_Value_Expression

Example:

Dim sequence_number As Long, code_number As String

serial_number = 123

code_number = "A123"

For variables with datatypes other than objects, the method is to use the keyword “Set” followed by the name of the variable, then followed by an equal sign (=) and the value of the variable.

Set Variable_Name = Variable_Value_Expression

Example:

Dim range_Data As Range

Set range_Data = Sheets("Sheet1").Range("A1")

Well, easy isn’t it? For now, this is enough about how to pass a value into a variable. Other examples will be explained in other sections of the explanation. What you need to note, add the keyword/statement SET if the data in the variable is a VBA object .

Keyword Dim, Static, Public and Private in VBA Variable Declaration

As explained above, the statements or keywords that can be used to declare a variable are as follows, of course, each has a specific purpose:

1. Dim
2. static
3. Public
4. Private

Of the four keywords, the DIM statement is the most commonly used. While 3 next keyword is used to declare variables with special characteristics concerning the scope ( scope ) or the estimated useful life ( lifetime ).

Then what is the meaning of each statement or keyword above?

This part is indeed a bit difficult to explain, so if you feel confused to understand it I think it’s natural. This is more due to my limited ability to explain and not because of your level of intelligence. Soo, please take a deep breath first.

The main difference between VBA variables declared using the Dim and Static Statements is when they are reset or removed from memory. This is about lifetime as I mentioned above.

As a general rule when a variable is declared with the Dim keyword , all procedure-level variables are reset when the relevant procedure ends. While static variables are not like that ( not reset/discarded ).

The Public and Private Statements are more influential on the scope or scope of a variable. Scope here means which parts of a VBA project can use or access these variables. Is it only on certain procedures, all procedures in a module or can also be used by other procedures even though different modules.

I’ve touched on this scope a bit in the previous Basic VBA tutorial series, please read it first if you haven’t read it: Scope Overview .

If you’ve read it, I think this section will be easier to explain by creating an exercise file. So please create a new file then save it with the file name ” Excel Class-VBA Training 3.xlsm ” in the folder we prepared earlier. Don’t forget to select the .xlsm extension that supports Excel’s VBA macro format.

Wait a minute, I didn’t brew coffee first.

Specifying VBA Scoope Variables

In the previous section, it was explained that the VBA variable scope refers to the term about which modules and procedures can use a variable. In other words, Scope determines where that variable can be used in a VBA project.

In the previous series, it was explained that an Excel workbook can have several modules and each module can be filled with several VBA procedures.

In a simple VBA application, you might just use one module with several procedures. However, as time goes on, your work with VBA may start to become more complex and require a lot of VBA modules and procedures.

In more complex cases, you may not want all VBA variables to be accessible to every module and procedure. So certain variables can only be used by certain procedures either in the same module or procedures in other modules.

Precisely defining the scope of VBA variables will allow you to do this sort of thing.

If in the discussion of procedures and modules I mention there are 4 levels of scope, for this discussion only 3 will be exemplified, namely:

1. Procedure-Level or Local VBA Variable
2. Module-Level or Private VBA Variable
3. Project-Level or Public VBA Variable

* I hope I’m not using the term wrong. If it’s not quite right, please correct it by comparing it with similar tutorials on the web or books on VBA .

Scope Procedure-Level or Local VBA

As the name indicates, procedure-level variables or local variables can only be used in procedures where a variable is declared. This applies to procedures of type Sub or Function.

The way to declare variables for the scope level of this procedure is generally right below the declaration or procedure statement. This way you can use the same name for multiple variables in different procedures.

The file “Excel Class-VBA 3.xlsm Practice” was already created, right? Okay, then please enjoy the coffee and snacks.

Next please do the following exercise:

1. In the file that we created earlier, create a module with the name “Module1”.
2. In this Module1 write the following VBA code:

Option Explicit

Sub Exercise_Local_Variable1()

     Dim int_Number1 As Integer

     Dim int_Number2 As Integer

     int_Number1 = 5

     int_Number2 = int_Number1 + 10

     MsgBox "The value of int_Number1 is: " & int_Number1 & Chr(10) & _

            "Value of int_Number2 Variable is: " & int_Number2

End Sub

Sub Exercise_Local_Variable2()

     Dim int_Number1 As Integer

     Dim int_Number2 As Integer

     int_Number1 = 10

     int_Number2 = int_Number1 * 2

     MsgBox "The value of int_Number1 is: " & int_Number1 & Chr(10) & _

            "Value of int_Number2 Variable is: " & int_Number2

End Sub

3. Run each of the above procedures.

* Make sure before and after writing the character & there is a space.

• The code ” MsgBox ” in VBA is used to display a message box with certain contents.
• Code ” & ” is used to combine one value with another value like in excel formula.
• The code ” Chr(10) ” is used to enter or change the line in the message box, otherwise you can use ” vbCrLf “.

In Module1 we create 2 procedures with the names Exercise_Local_Variable1 and Exercise_Local_Variable2 . In each of these procedures we declare 2 variables named int_Number1 and int_Number2 .

When executed the first procedure generates a message box as follows:

While the second procedure displays a different message box as follows:

This indicates that each variable runs locally in the associated procedure and is immediately cleared when the procedure ends.

Next, please add the following procedure to Module1 under these two procedures:

Sub Exercise_Local_Variable3()

     MsgBox "The value of int_Number1 is: " & int_Number1 & Chr(10) & _

            "Value of int_Number2 Variable is: " & int_Number2

End Sub

Next, please run the Exercise_Local_Variable3 procedure . What happened?

The picture above shows that Exercise_Local_Variable3 does not recognize the variables int_Number1 and int_Number2 , because the variables int_Number1 and int_Number1 that you declared earlier are only available for related procedures where these variables are declared.

Next, please delete the ” Option Explicit ” statement at the top of Module1, then run each procedure on the module in turn.

Although the code does not error basically, VBA declare int_Number1 and int_Number2 is implicit . Which means that the data type used for both variables in the Exercise_Local_Variable3 procedure is Variant. In addition, the value of the two variables in this procedure is still empty, because we haven’t entered any value in these two VBA variables.

From here you can take an understanding of Variables in Scope Level Procedures or Local VBA, right? Essentially a variable that can only be used on related procedures and cannot be called for other procedures.

So what if you want VBA to be able to access a Variable from another procedure?

Scope Module-Level or Private VBA

If at the Procedure level Scope a Variable can only be used by related procedures, at the module level scope variable, a Variable is also available for use in other procedures as long as it is still in the same module where the VBA variable is declared.

To better understand it please create a new module with the name “Module2” in the same file. In the module write the following VBA code:

Option Explicit

Dim str_Text As String

Dim int_Number As Integer

Sub Exercise_LevelModule_Variable1()

     str_Text = "Learn Basic Excel VBA"

     int_Number = 1439

     MsgBox "Exercise_LevelModule_Variable1" & Chr(10) & Chr(10) & _

            "The value of the str_Text Variable is: " & str_Text & Chr(10) & _

            "Value of int_Number Variable is: " & int_Number

End Sub

Sub Exercise_LevelModule_Variable2()

     str_Text = "www.infactdaily.com"

     int_Number = 2018

     MsgBox "Exercise_LevelModule_Variable2" & Chr(10) & Chr(10) & _

            "The value of the str_Text Variable is: " & str_Text & Chr(10) & _

            "Value of int_Number Variable is: " & int_Number

End Sub

Sub Exercise_LevelModule_Variable3()

     MsgBox "Exercise_LevelModule_Variable3" & Chr(10) & Chr(10) & _

            "The value of the str_Text Variable is: " & str_Text & Chr(10) & _

            "Value of int_Number Variable is: " & int_Number

End Sub

If so, then please run each of the above procedures in turn in the following order. Previously press the reset button on the VBE menu to ensure all Variables have been reset:

1. Exercise_LevelModule_Variable3 

2. Exercise_LevelModule_Variable1 then Exercise_LevelModule_Variable3 

3. Exercise_LevelModule_Variable2 then Exercise_LevelModule_Variable3 VBA 3 

 

From this exercise you can learn that by declaring a Variable at the beginning of the declaration line or before declaring a procedure, under the ” Option Explicit ” statement , the Variable has a module level scope.

This means that the VBA variable can be recognized by all procedures in the same module and there are no errors as in the previous exercise examples in the Scope Procedure-Level or Local VBA section .

In the example declaration above, to get the module level scope, apart from declaring it with the Dim keyword, you can also declare the Variable with the Private statement . So the code

Dim str_Text As String

Dim int_Number As Integer   ( or )

Private str_Text As String

Private int_Number As Integer

Using the Private keyword in this case will have the same effect as the Dim statement. For this reason, to make the code easier to read I recommend that you use a Private Statement. Because in general we will declare a variable with Dim so that when you use the Private Keyword it will be a differentiator, so that the next time you reread the code the message conveyed by the code will be easier for us to read.

Okay, can you understand the module scope variable?

Now create 1 more Module with the name “Module3” . Then write the following Excel VBA Macro code on the module:

Option Explicit

Sub Exercise_LevelPublic_Variable()

     MsgBox "Exercise_LevelModule_Variable3" & Chr(10) & Chr(10) & _

            "The value of the str_Text Variable is: " & str_Text & Chr(10) & _

            "Value of int_Number Variable is: " & int_Number

End Sub

Then try running this Exercise_LevelPublic_Variable procedure .

Error huh? Soo how can we use a Variable across Modules? We will continue the discussion in the next section.

Scope Project-Level or Public VBA

Up to this point you should be able to use Variables at the procedure and Module level . Now we will continue to create a Variable Level Project where the scope level is wider than the previous 2 scopes.

To declare a VBA variable in the Public scope , the declaration must meet the following requirements:

1. Declare these variables like the module-level declarations written at the beginning of the module outside of all procedures.
2. Use Statement Public instead of Statement Dim .
3. Variable declarations must be made in standard VBA modules.

VBA variables whose declarations meet the above conditions are available for any procedure, even in different modules within the same Excel Workbook.

OK, now let’s practice, shall we?

Please open Module2 then change the Dim or Private keywords if you have changed the str_Text and int_Nangka variable declarations with the Public statement . so in the module2 the declaration of the two variables becomes:

Public str_Text As String

Public int_Number As Integer

Now, try to open Module3 and then run the Exercise_LevelPublic_Variable procedure .

Now no more errors right? It’s just that the value of each variable is still empty, because we haven’t entered the value for each of these variables.

Next, try running the Exercise_LevelModule_Variabel1 or Exercise_LevelModule_Variabel2 procedures on Module2 to assign values ​​to the two variables then run the Exercise_LevelPublic_Variable_Variable procedure on module3 again.

Up here you have understood about how to set the scope or the scope of a variable.

What you need to note is that you should use the narrowest possible scope, if you only need it for the Procedure level then set the variable for the procedure level only. If a variable is also needed at the Module level only, then don’t set it to be wider to the Public/Project scope.

You may ask, after the procedure ends, the value of the variable is still stored and not lost from memory?

This question has something to do with the term Lifetime in VBA . If so, let’s continue the discussion.

Understanding VBA Lifetime Variables

The term ” lifetime ” or sometimes simply ” life ” refers to how long a variable is stored in computer memory.

Basically the scope of a variable, in addition to affecting where the variable can be accessed or used, but also affects the state in which the variable is deleted or not from memory.

In general there are 2 basic rules regarding this:

• Procedure-level VBA variables declared with Dim statements are deleted or reset from memory when the procedure is complete (End Sub).
• Procedure level Static variables, module level variables, and public variables retain their values ​​between procedure calls (Not deleted/reset).

To be clearer Do the following activities:

1. Create a new module with the name ” Module4 ” in the file we created earlier.
2. Create a public procedure with the name ” TestVarabelStatic “.
3. In this procedure, we declare 2 variables, each with the names int_Var1 and int_Var2. Use the Dim keyword for int_Var1 and the static keyword for int_Var2.
4. After declaring the two variables, fill in the value of each value by adding up the variable with the number 5.
5. Next we test the value of each variable by calling it in a message box with the following code:

MsgBox "Variable value of int_Var1 is: " & int_Var1 & Chr(10) & _

"Variable value int_Var2 is: " & int_Var2

6.  End the procedure with a closing statement “End Sub“

If the steps you did are correct then the code you created in Module1 will look like this:

Option Explicit

Public Sub TestVariableStatic()

     Dim int_Var1 As Integer

     Static int_Var2 As Integer

     int_Var1 = int_Var1 + 5

     int_Var2 = int_Var2 + 5

     MsgBox "Variable value of int_Var1 is: " & int_Var1 & Chr(10) & _

            "Variable value int_Var2 is: " & int_Var2

End Sub

When finished please try to run the procedure.

At first run, it appears that the value of the variables int_Var1 and int_Var2 is 5. As shown in the following message box:

Next, please run the procedure again and see the results.

At the second run, it turns out that the value of the variable int_Var1 remains 5 while the value of int_Var2 becomes 10 and continues to change by 5 when it is rerun. Why is that?

This is because the variable int_Var2 we declare as Static , so that at the end of the procedure the value 5 of the variable int_Var2 remains in memory. When you run the procedure a second time, the value is added to the number 5 according to the value assigned to the int_Var2 (int_Var2 = int_Var2 + 5).

Please try running the TestVarabelStatic procedure a few times and take a look at the resulting values.

Then how to reset the Static value earlier? For this example, use the reset menu button on the VBE as described previously.

From the simple case examples above, I’m sure you’ve got an idea about the meaning of lifetime that I mentioned above.

The term ” lifetime ” in VBA refers to how long a variable is stored in computer memory.”

In addition to being used on Variables. Static keywords or statements can also be used at the procedure level, this will make all variables in the procedure have a longer lifetime or are not immediately deleted at the end of the procedure.

In module4 below the end of the procedure, add the following VBA script or code:

Public Static Sub TestProcedureStatic()

     Dim int_Var1 As Integer

     Dim int_Var2 As Integer

     int_Var1 = int_Var1 + 5

     int_Var2 = int_Var2 + 5

     MsgBox "Variable value of int_Var1 is: " & int_Var1 & Chr(10) & _

            "Variable value int_Var2 is: " & int_Var2

End Sub

Then please try running the ” TestProcedureStatic ” procedure several times. What happened? It turned out that even though we are not declared as static, variable int_Var1 and int_Var2 in this procedure is treated like a static variable is not it?

Alright, the discussion about VBA Constants and Variables in this VBA tutorial series I think is quite long and tiring. For that I have done it first.

Hopefully useful and increase your enthusiasm for learning VBA. Don’t forget to share if there are benefits, so that other friends also feel the benefits. See you in the next series. Greetings Excel Class.