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.

VBA Variables

Step 2: Enter the macro name such as macro1.

Step 3: And click the OK button.

VBA Variables

Step 2: Then, click on the Stop Recording.

VBA Variables

Step 3: Open the macro editor, and enter the code for the variable in the macro1.

VBA Variables

Step 4: Execute the code for Macro1, and you will get the below output in your sheet.

VBA Variables

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.

VBA Variables

Step 4: You will get the Dialogue window.

1. Enter the Macro name.

2. Click on the New button.

VBA Variables

3. You will get the code window and enter the following code.

VBA Variables

Step 5: Close the code window. And,

1. Right-click on the button named Button1.

2. Select Edit Text option.

VBA Variables

3. Enter Hey There.

VBA Variables

4. Click on Hey There button.

5. You will get the input box as shown in the below screenshot.

VBA Variables

6. Enter the name, i.e. Edward.

7. Then you will get the message box as shown in the below screenshot.

VBA Variables

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.

VBA Variables

Step 2: Go to the Macros and

  1. Select a created Macro.
  2. Click on the “Step Into” button.
  3. It opens the code window as shown in the below screenshot.

VBA Variables

Step 3: Write your code to declare the variables.

VBA Variables

Step 4: After that,

  1. Click on the Save button and save your file.
  2. Then click on the Excel icon to return the Excel sheet.
  3. You will get the Design Mode “ON” as shown in the below screenshot.

VBA Variables

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

VBA Variables

Next TopicVBA Constant

Previous articleVBA Data Types
Next articleCircular reference in Excel