How to Set Variable to Cell Value in Excel VBA?

Last Updated : 23 Jul, 2022

Adding data to the worksheet is often done and it is essential to learn how to set variable to cell value in excel VBA. There can be instances when you want to add the same data to your worksheet. This task could easily be achieved with the help of Excel VBA variables. You can assign your cell values with variables. Let's learn how to set variables to cell values in Excel VBA.

Declaring Variables in VBA

Before learning the assignment of variables, one needs to know what are variables and why they are used? Like any other programming language, VBA has variables. Variables are the containers to store data in them. There are some rules for storing data in them.

Rules for naming Variables

The variable name cannot start with a number.
While naming the variables you cannot have space between the names which means a space character (' ') is prohibited.
The length of the variable name should be less than Two-hundred and fifty-five (255) characters.

Implicit and Explicit Declaration

In Excel VBA, variables can be classified into 2 categories implicit and explicit,

Implicit Declaration

The implicit declaration declares a variable of data type variant. When we create variables implicitly, we never mention the data type to be used. The VBA automatically considers that variable as of variant type.

Syntax: variable_name = assigned_value

The variant data type is of two types,

Numeric Variant: The numeric variant data type is provided by VBA when the variable is assigned with a number. The bytes used are 16. It can take any value to Double.

Text Variant: The text variant data type is provided by VBA when the variable is assigned with a text. The bytes used are 22. It's always possible to take variable sizes of the string.

Explicit Declaration

The explicit declaration declares a variable of custom data type. One can always mention the data type of the variable being used at the time of declaration. The keyword used is 'Dim'.

Syntax: Dim variable_name as data_type

variable_name = assigned_value

Opening VBA immediate Window

The excel immediate window can be compared with a terminal that displays the code output in any other programming language. We will use this immediate window to run our code in the macro itself and display the code output immediately. You can also use the immediate window for debugging and running multiple VBA macros at once.

Different Ways to Open Immediate Windows in VBA

Press Ctrl + G

Open your VBA, then use the Short cut Ctrl + G on your keyboard, and the immediate window will open at the bottom side of your VBA.

Using View Tab

Step 1: Go to the Developer Tab, under the code section, and click on Visual Basic.

Step 2: The VBA editor is open now.

Step 3: Go to View Tab, and click on Immediate Window.

Step 4: The Immediate Window appears.

Set Variable to Cell Value

After learning how to declare variables, and how to access the immediate window, you are ready to learn how to set variables to cell Values. Given the name of a student, 'Arushi'. Write her Age and Aim in the worksheet by assigning variables in the VBA Macro.

Step 1: Go to the Developer Tab, under the code section, and click on Visual Basic.

Step 2: Your VBA editor is opened. Create a new Module. The name of the Sub created is geeks(). Declare an Age variable explicitly with custom data type as Integer. Assign the variable value as 19.

Step 3: Set the cell value by the variable name. Use =Range(cell).Value function to access any cell in the worksheet. Assign the Age variable to it. Run your macro.

Step 4: The value of cell C5 is set to 19.

Step 5: Repeat steps 2, 3, and 4 to set Aim for cell D5. Declare a variable name Aim explicitly with data type as Variant. Assign the variable value "CA". Again, use the range function and set the cell value of D5 to the variable Aim.

Step 6: The value of cell D5 is set to "CA".

Set Cell Value to Variable

We can also assign the cell values in the worksheet to the variables. Consider, the same data set as above but this time the age and aim of the student 'Arushi' are prefilled. Your task is to assign these values to the variables and print them in the immediate window.

Step 1: Go to the Developer Tab, under the code section, and click on Visual Basic.

Step 2: Declare the Age variable explicitly using the Dim keyword.

Step 3: Assign the Age variable with the value of cell 'C5'. This can be achieved using =Range(cell).Value function. Print the Age variable in the immediate window using Debug.Print(variable) function.

Step 4: Click on the run button. The macro will run, and 19 is printed in the immediate window of the VBA.

Step 5: Repeat Steps 2, 3, and 4. Declare the Aim variable explicitly. The Range function assigns a cell value of D5 in the variable Aim. Print the Aim variable in the immediate window.