VBA Variable Types
VBA stands for Visual Basics for Application.
VBA stands for Visual Basics for Application. Excel VBA is a programming language developed by Microsoft for Office applications such as MS Excel, MS Word, etc.
Visual Basic for Application is a human-readable and editable programming language used to write a set of instructions, known as a program, that can automatically execute simple and complex tasks in Excel.
When these sets of instructions or statements are grouped, they form a macro that is executable infinitely many times to perform a task.
The VBA programming language helps users to access functions beyond what is available in Excel and other Microsoft Office applications. VBA is also used to fulfill the specific needs of the users, such as creating user-defined functions, automating computer processes, etc.
VBA is used to analyze large amounts of data simply. It also helps in creating and maintaining complicated financial models.
For example, suppose we have a financial model for Nike Inc. It is possible to auto-populate most of the numbers on the template with just a click of a button by using VBA macros. The only thing left is to go through the assumptions and fine-tune the model.
Key Takeaways
- VBA is a human-readable programming language developed by Microsoft that helps us to write a series of instructions to automatically execute simple and complex Excel tasks.
- Variables are the fundamental building blocks of a program. These are specific values the user gives that are stored in the computer’s memory and can be used for calculations or other operations later in the program.
- We use the keyword Dim in VBA to declare variables. Variables are declared in VBA just after naming the macro.
- VBA variables can be of many data types like integer, string, boolean, single, etc. Each data type has a different storage capacity and should be used in the code depending on its storage.
- To avoid any potential issues in VBA, it's prudent to mention the specific data type we intend to use for a variable instead of relying on automatic assumptions. This guideline can enhance code safety while ensuring that unexpected errors are kept at bay.
Enabling the Developer Tab in Excel
Learning the VBA programming language can help us access functions beyond those directly available in the various office applications. We can also create our user-defined functions in VBA for customizing our tasks.
Most people who write programs in VBA use macros. A macro serves as a series of statements that the user can run multiple times. This shows the flexible nature and potential usefulness of macros for various tasks.
Before creating, writing, or recording a macro, we must enable the developer tab in Excel, which is hidden on the Excel ribbon by default. The developer tab helps us to open the VBA interface, where we can write our VBA code. It also provides the option to record our macro.
To enable the developer tab on the top of the Excel sheet:
1. Right-click anywhere on the Excel ribbon and click on the Customize the Ribbon option.
2. Go to Customize Ribbon and select the Developer checkbox and click ok.
Doing this enables the Developer tab on the top, as shown below:
In order to write a macro:
1. We must access the VBA interface. This can be done using a keyboard shortcut (ALT+F11) on Windows or navigating to the Developer tab and clicking on Visual Basic. This will bring up a window with options, as shown below:
2. Next, we simply click Insert > Module to create an empty module where we can start writing our code.
VBA Variables
Variables are one of the most crucial building blocks of a program written in any programming language. In general, variables are changeable values.
In computing terms, variables are the specific values (integers, characters, decimals, strings, texts, etc.) stored in the computer’s memory or storage system that we can use in our code for different calculations and can execute them.
Variables are used in all computer programming languages and are the backbone of a program. VBA is no exception to this, and VBA also uses variables.
To identify variables, it is a common practice to give names to variables. Each of the Excel VBA variables must be assigned a name.
There are some rules which must be kept in mind while naming variables. These rules are common to all programming languages. The rules for naming variables are as follows:
- The variable name should contain at most 255 characters.
- There should be no spacing between characters in the name of the variable.
- The variable name can contain upper case characters, lower case characters, and digits, but it should not begin with a digit.
- It should not contain any special characters except underscore (_)
- Using CamelCase and PascalCase makes our variables easily readable.
NOTE
Values retrieved from the memory by the computer are subsequently displayed in the output.
Some examples of valid variable names:
- my_Name
- NewVar1
- RollNo
Some examples of invalid variable names:
- my.Name (invalid as special characters other than underscore are not allowed)
- 1_NewVar (invalid as a variable name cannot start with a digit)
- Roll No (invalid as a variable name cannot contain spaces)
In VBA, we need to declare the variables before using them by assigning them names and data types. Typically, we declare our variables immediately after naming our macro.
The declaration of variables is a very crucial step in the creation of our VBA code. Declaring variables allocates the required amount of space in the memory for the declared variables.
The declaration of variables is made in VBA by using the Dim keyword. The dim keyword is used in a VBA code to store data temporarily. It enables the compiler to identify the variable type while also allotting memory for the variable. Dim keyword also helps us to create a more reliable and effective VBA code.
Generally, the variables are declared just after naming the macro. Sometimes it may be difficult to predict beforehand how many variables will be used in the code. Hence, the user may add or modify variables after writing the macro.
For declaring the variables, the Dim keyword is followed by the variable name, which is subsequently followed by the statement “as [datatype]”.
For example, to create a variable named marketPrice, you can write “Dim marketPrice as double”, where double is the data type of the variable, about which we will learn later.
It is not always necessary to specify the data type of the variable. You may only declare the variable name, and VBA can deduce the data type when the variable is used later in the code. However, it is a good practice to declare the data type we intend to use.
Each declaration will require its line, so grouping variables of the same data type together helps make our code more efficient, readable, and easy to understand.
Let’s take an example of declaring variables using Dim:
The above example declares variables CompanyName, EmployeeName, and EmployeeId as strings, variable MarketPrice as double and variable isProfit as a boolean.
The above code only declares certain variables and does not execute anything. We will learn about different variable data types in the next topic.
VBA Variable Types
Although there are several VBA data types, VBA macros' most commonly used data types in VBA macros are listed below:
1. Integer
This data type stores only integer values with no fractions. It can store positive integers, negative integers as well as zero.
For example, numbers like -24, -45,-7,0,45,109, etc., can be stored in an integer variable, but fractional numbers like 24.7 and 34.2 cannot be stored in an integer variable. An integer variable requires 2 bytes of storage in the computer’s memory.
2. Single
This data type stores any numeric value, which may be a fraction or decimal. It can also store integers. A single data type is generally used when decimal precision is required. A single data type requires 4 bytes of storage.
3. Double
It is very similar to the single data type, except that double has a higher data storage capacity. The double data type can store both integer and decimal numbers. It is used when we require higher levels of accuracy in the data. Double data type requires 8 bytes of storage.
4. Date
This data type stores the date and time information in a variable. It is required when it is necessary to make some calculations involving dates. This data type requires 8 bytes of storage.
5. String
This data type stores characters and texts. Although it can hold numbers, they are stored in the memory as texts and not as numbers, so we cannot carry out any arithmetic operations on numbers stored as strings.
NOTE
A variable of string data type must be enclosed within double quotes.
“A”, “orange”, “Rohan”, “John”, “The weather is good”, etc., are examples of strings. If an integer is enclosed within double quotes, then it is considered a string and not an integer like “2”, “546”, “34.4” are strings.
The storage required for a string variable is equal to the length of string for a string of fixed length. On the other hand, the storage required for a text of variable length is the length of the string + 10 bytes.
6. Boolean
The boolean data type stores binary results (0/1 or False/True). It only takes two values, 0 and 1, where 0 refers to false, and 1 refers to true. It requires the storage of 2 bytes.
Storing a Value in a Variable: After declaring a variable in the macro, we can easily assign/store values in it by using the syntax:
Variable_Name=Variable_Value
Examples of Variable Declaration in VBA macros using Dim
The step of declaring variables is perhaps one of the most important ones when generating VBA code because it instructs the compiler how much memory to allow for a program.
Below are some instances of declaring variables and storing values in variables.
Let us take a look at example 1:
The above example first declares three variables: myInt as an integer, myDec as a double data type, and myStr as a string data type. It then assigns values to the above-declared variables, as shown. It stores the value of 10 in myInt, 3.14 in myDec, and “Hello!” in myStr.
For another example, let’s take a look at example 2:
In this example, we declare three string variables: EmployeeName, CompanyName, and EmployeeId. EmployeeName holds the name of the employee who works for the company named CompanyName, while EmployeeId records the employee's individual Id.
We also declare a variable MarketPrice of double data type, which stores the market value of the company at present. We declare another variable, isProfit of type boolean, which stores whether or not the company is earning profit.
After declaring the variables, we can store values in them as shown above.
Let’s take another example, example 3:
In the above example, we have declared a variable named Num of type integer and stored a value of 567 in it. After that, by using the MsgBox function in VBA, we show the value of the variable Num in the message box.
The message box is a kind of dialog box in VBA Excel through which you can inform the users of your program or give some alert message.
To run your code in VBA, click Run > Run Sub/UserForm as shown below:
On running/executing the code given in the example3, we would get the following output:
Hence, in this way, we can declare variables of any variable type discussed above and use them in our VBA program to produce the desired output/result.
or Want to Sign up with your social account?