VBA Variables
Variable is a specific memory used to hold values that are stored in a storage system and computer memory. You can use these values in the code, and it can be changed during script execution. The computer fetches these values from the system and shown in the output.
Each variable must be given a name. Some are the basic rules to name the VBA variable, such as:
- You must use the letter as the first character or not begin with a number.
For example, you can’t write a variable name which starts from a number as “1_hello”, the correct way is “hello1”. - Name must be less than 255 characters.
- No space, period (.), exclamation mark (!), or characters (@, #, $, *) is allowed.
For example, you can’t write “Student ID” as it contains the space between Student and ID, the correct way of representing this variable name is “StudentID”. - You can’t use “Visual Basic” reserved keywords as a variable name.
- The period is not allowed.
Syntax for VBA variable
you need to declare the variables before using them by assigning names and data type, in VBA.
In VBA, variables are either declared implicitly or explicitly.
Implicitly: let’s see through an example of a variable declared implicitly.
- Label = tutoraspire
- Volume = 10
Explicitly: let’s see through an example of a variable declared explicitly.
- Dim Num as Integer
- Dim password as String
VBA variable is not different than other programming languages. To declare a variable in VBA, you can use the “Dim” keyword in syntax.
How to Execute the Variables
Before we execute the variables, we need to record a macro in Excel. To record a macro, follow the steps which are given below:
Step 1: Select the Record Macro.
Step 2: Enter the macro name such as macro1.
Step 3: And click the OK button.
Step 2: Then, click on the Stop Recording.
Step 3: Open the macro editor, and enter the code for the variable in the macro1.
Step 4: Execute the code for Macro1, and you will get the below output in your sheet.
Before declaring variables, We will create a basic VBA program that displays an input box to ask for the user’s name then shows a greeting message.
Step 1: Click on the Developer tab.
Step 2: Then, click on Insert drop down box.
Step 3: Select a Command Button, as shown in the below screenshot.
Step 4: You will get the Dialogue window.
1. Enter the Macro name.
2. Click on the New button.
3. You will get the code window and enter the following code.
Step 5: Close the code window. And,
1. Right-click on the button named Button1.
2. Select Edit Text option.
3. Enter Hey There.
4. Click on Hey There button.
5. You will get the input box as shown in the below screenshot.
6. Enter the name, i.e. Edward.
7. Then you will get the message box as shown in the below screenshot.
How to Declare Variables
Let’s take an example on how to declare variables in VBA. We will maintain the three types of variables, such as joining date, string, and currency.
Step 1: Previously, we insert a Command Button in excel sheet.
Step 2: Go to the Macros and
- Select a created Macro.
- Click on the “Step Into” button.
- It opens the code window as shown in the below screenshot.
Step 3: Write your code to declare the variables.
Step 4: After that,
- Click on the Save button and save your file.
- Then click on the Excel icon to return the Excel sheet.
- You will get the Design Mode “ON” as shown in the below screenshot.
Step 5: Turn off the Design Mode before clicking on the command button.
Step 6: Then click on the Command button. It will show the variables as an output for the range we declared in the code.
- Name
- Joining Date
- Currency
