Variables and Data Types in VBA Excel

VBA (Visual Basic for Applications) variables are used to store data such as numbers, text, or dates, acting as named containers in your code. Variables allow you to store, manipulate, and reuse data across different parts of a program, making your code more efficient and maintainable. VBA supports various data types, including integers, strings, and floating-point numbers.

Defining Variables In VBA

In VBA, variables can be declared in two ways:

• Implicit Declaration: We can assign a value to a variable without declaring it, and VBA automatically creates it as a Variant type. Variant variables are flexible but consume more memory and can lead to errors if not carefully managed.

Example:

Sub ImplicitExample()
    label = "gfg"
    MsgBox label
End Sub

• Explicit Declaration: Use the Dim statement to declare a variable with a specific data type, which improves code clarity and reduces errors. This also helps avoid naming conflicts and typos.

Example:

Sub ExplicitExample()
    Dim Num As Integer
    Num = 10
    MsgBox Num
End Sub

Syntax for Declaring Variables:

Sub VBA_Variable_Example()
    Dim VariableName As DataType
End Sub

Variable Naming Rules:

• Must be less than 255 characters.
• Must start with a letter (not a number or special character).
• Cannot contain spaces or reserved characters (e.g., !, @, #).
• Cannot be a VBA reserved word (e.g., Sub, Dim).
• Periods (.) are allowed but not recommended for clarity.
• Use meaningful names to improve code readability.

Examples:

Example:

In this example, we will define a macro in excel, And we will enter the code inside that macro and execute the code to understand the working of VBA variables in excel.

Step 1: First, we will make our Developer option available in the Excel toolbar. For this, go to any of the tools(here, we are choosing Draw) and then right-click on it and select the "Customize the Ribbon.." option.

The excel will open pop-up options, there we need to check the Developer checkbox and click on OK. This will enable the Developer option and make it available in the excel top toolbar.

Step 2: In this step, we will declare our variable in the visual basic editor. For this go to Developer > Visual Basic Editor.

This will open the Visual Basic Code Editor, where we are required to write our VBA script.

Step 3: In this step, we will write our VBA scripts. For this, we will double click on This workbook under Microsoft excel objects in the left pan and open the editor and write the following code to it.

Sub VBA_Variable_GFG_Example()
Range("a1").Value = "Data Structure Course"
Range("b1").Value = "Data Structure Course"
Range("c1").Value = "Data Structure Course"
Var = "Data Structure Course"
Range("a3").Value = Var
Range("b3").Value = Var
Range("c3").Value = Var
End Sub

In the above, we can see without using variables if we want to make changes to the string "Data Structure Course" and add "GeeksForGeeks" before every string, we need to repeat it at three different places. But if we use a variable then we just need to change it at one place where we declare our variable. This will reduce the workload.

Sub VBA_Variable_GFG_Example()
Range("a1").Value = "Data Structure Course"
Range("b1").Value = "Data Structure Course"
Range("c1").Value = "Data Structure Course"
Var = "GeeksForGeeks - Data Structure Course"
Range("a3").Value = Var
Range("b3").Value = Var
Range("c3").Value = Var
End Sub

We will write this code in the VBS script editor and execute it. This will print the output string in the cells defined in the code.

Once, we execute it using the Run button, we will get the following output.

Scope Of VBA Variables

The scope of a variable determines where it can be accessed in your code, while the lifetime determines how long it retains its value. VBA supports three levels of variable scope:

Procedure-Level Scope

Variables declared inside a procedure (a Sub or Function) are only accessible within that procedure. They are created when the procedure starts and destroyed when it ends.

Example: In this example, we will see the procedure level of the VBA variable's scope. For this open the VBA editor and write the following code to it.

Sub ProcedureLevelDemo()
    Dim res As String
    res = "This variable is only accessible within ProcedureLevelDemo."
    Range("A1").Value = res
End Sub

Once, we click on the Run button in the VBA editor, we will the output. The text will get printed to cell A1.

Module-Level Scope

Variables declared with Dim at the top of a module (outside any procedure) are accessible to all procedures within that module. Use the Private keyword explicitly for clarity.

Example: In this example, we will look for the module scope of variables. For this, we will create a new module. We will open VBA Editor and in the left pane (project explorer) we will Right-Click and create a new module add the following code to it.

' Declare module-level variable
Private txt As String

Sub ProcedureDemo()
    txt = "Module-level variables are accessible within the same module."
    Range("A1").Value = txt
End Sub

Sub PrivateModuleDemo()
    txt = "This procedure can also access txt."
    Range("A2").Value = txt
End Sub

After this, we just need to click on the Run button. Also, when we run it, it asks for which macros to run, there we need to choose "Macros In: <All Projects>".

 Once we run it, we will get the output in cells A1 and A2. 

Public Module-Level Scope

Variables declared with the Public keyword at the top of a module are accessible across all modules in the VBA project.

Example: In this example, we will add two different modules and define a public VBA variable inside one module and try to access it from both modules. Below is the code for module1 and module2.

Example:

' Module1
Public res As String

Sub Demo1()
    res = "geeks"
    Range("A1").Value = res
End Sub

' Module2
Sub Demo2()
    res = "geeksforgeeks"
    Range("A2").Value = res
End Sub

Once, done with both the modules. Just click on the Run button. The output will get printed in cells A1 and A2,

Lifetime of Variables

By default, variables lose their value when their scope ends (e.g., when a procedure finishes). To make a variable retain its value between procedure calls, use the Static keyword.

Example:

Sub StaticVariableDemo()
    Static count As Integer
    count = count + 1
    MsgBox "Count is: " & count
End Sub

After this, we need to execute it. Every time we will click on the run button the MessageBox will return the updated value. (1, 2, 3.....) and thus it will extend its scope and lifetime.

Best Practices:

• Use procedure-level variables for temporary data to minimize unintended changes.
• Use module-level or public variables sparingly to avoid complexity.
• Always declare variables explicitly with Dim, Private, or Public to control scope.

VBA Data Types

In computers, we use data types to differentiate between the integers and strings, etc. The data types which we will assign to a variable, decide what should be stored in that variable, i.e., the values that need to be stored in the variable is depends on the data type of the variable. In VBA, data types are divided into two major types:

Numeric Data Type:

Numeric data types are used to perform mathematical operations such as addition, subtraction, etc. It is used to handle the numbers in various representations format. In numeric data type, the Integral Type represents only the whole numbers(zero, positive & negative). Non-Integral Types represent both the integer and the fractional part.

Non-Numeric Data Type: Non-Numeric data types are not manipulated by the arithmetic operators. These are comprised of texts, data, etc.

Note: If we do not declare any data type, the VBA will be default makes variable as a variant type.

Example:

In this example, we will write a simple script to create a button and with one click of that button an event will occur and we will get the output.

Step 1: First we will insert a command button in our excel worksheet. For this go to Developer > Insert > CommandButton and click on it.

 After that, we can insert the button anywhere in the sheet we want. We just need to drag it over the excel sheet.

Step 2: In this step, we will write the script for our button. For this just double-click on the button. This will open the VBA Script Editor where we will write the following code. In the below code, we are defining GFG and course as String data type and res as Integer data type.

Private Sub CommandButton1_Click()
Dim GFG As String, res As Integer, course As String
GFG = "GeeksForGeeks"
res = "01"
course = "Interview Preparation"
Range("a1").Value = GFG
Range("b1").Value = res
Range("c1").Value = course
End Sub

Step 3: In this step, we will save our script. For this click on the Save button in the VBA Editor. 

The excel will popup a window asking for saving the macros. We need to click "Yes" and it will save it.

Step 4: Now, we will execute our script. For this, we need to click over the command button. But before clicking over it, we are required to come out of Design Mode. To move out of any mode just click on mode once.

Once We are done with this, we will execute our script by clicking over the commandButton1. It will give the following output.

Note: If we write "01" in any cell in excel, it will print "1" by default. That's why the in output, in cell b1 only "1" is printed.