What Is a Variable in VBA?

Reading time: 2 minutes

In What Is a Program in VBA?, you learned how to write an instruction: the right part calculates, and the left part receives the result. In that article, the result was written in a cell with a fixed reference, like this:

Sub WriteInA2()
 Range("A2") = 10
End Sub

This is also the way the Macro Recorder writes its code: each cell you select or change is recorded with its fixed reference, Range(“A2”), Range(“B5”), and so on.

This code works very well. But it has one limit: the program can only write in cell A2. Each time you run it, it writes in A2, and nowhere else. To write in another cell, you must change the code.

1. The same line, three times

Now let’s use a variable named i in our Cell reference in VBA. First, we give it the value 1, and we use it as the row number and value to write in the cell; then we add 1 to it, and we write the same line again:

Sub WriteThreeCells()
 i = 1
 Cells(i, 1) = i
 i = i + 1
 Cells(i, 1) = i
 i = i + 1
 Cells(i, 1) = i
End Sub

Look at the line Cells(i, 1) = i. It is written three times, exactly the same. And yet, the result is different each time:

  • first time, i = 1: the program writes 1 in A1
  • second time, i = 2: the program writes 2 in A2
  • third time, i = 3: the program writes 3 in A3
VBA code that writes the same line three times with the variable i, and the result 1, 2, 3 in cells A1 to A3

2. The line i = i + 1

This line follows the rule you saw in What Is a Program in VBA?, and this rule is the same in all programming languages, not only in VBA:

  • the right part (i + 1) is calculated first: the current value of i, plus 1
  • the left part (i) receives the result

The same variable is used on both sides, but not for the same job: on the right, we read its value to make the calculation; on the left, we store the new value. This is how you increment a value in programming.

3. Replace the fixed reference

With Range(“A2”), the reference is fixed, and this is exactly the problem. The idea is to replace the fixed reference with a variable.

Then, while the program runs, the value of the variable changes, and so does the cell where the program makes its calculation or its changes. In our example, i is used for the row number in Cells(i, 1), and also for the value written in the cell.

What’s next

In this code, the same line Cells(i, 1) = i is written three times. To avoid this heavy way of writing, we will now study the loops in What Is a Loop in VBA?.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

What Is a Variable in VBA?

Reading time: 2 minutes

In What Is a Program in VBA?, you learned how to write an instruction: the right part calculates, and the left part receives the result. In that article, the result was written in a cell with a fixed reference, like this:

Sub WriteInA2()
 Range("A2") = 10
End Sub

This is also the way the Macro Recorder writes its code: each cell you select or change is recorded with its fixed reference, Range(“A2”), Range(“B5”), and so on.

This code works very well. But it has one limit: the program can only write in cell A2. Each time you run it, it writes in A2, and nowhere else. To write in another cell, you must change the code.

1. The same line, three times

Now let’s use a variable named i in our Cell reference in VBA. First, we give it the value 1, and we use it as the row number and value to write in the cell; then we add 1 to it, and we write the same line again:

Sub WriteThreeCells()
 i = 1
 Cells(i, 1) = i
 i = i + 1
 Cells(i, 1) = i
 i = i + 1
 Cells(i, 1) = i
End Sub

Look at the line Cells(i, 1) = i. It is written three times, exactly the same. And yet, the result is different each time:

  • first time, i = 1: the program writes 1 in A1
  • second time, i = 2: the program writes 2 in A2
  • third time, i = 3: the program writes 3 in A3
VBA code that writes the same line three times with the variable i, and the result 1, 2, 3 in cells A1 to A3

2. The line i = i + 1

This line follows the rule you saw in What Is a Program in VBA?, and this rule is the same in all programming languages, not only in VBA:

  • the right part (i + 1) is calculated first: the current value of i, plus 1
  • the left part (i) receives the result

The same variable is used on both sides, but not for the same job: on the right, we read its value to make the calculation; on the left, we store the new value. This is how you increment a value in programming.

3. Replace the fixed reference

With Range(“A2”), the reference is fixed, and this is exactly the problem. The idea is to replace the fixed reference with a variable.

Then, while the program runs, the value of the variable changes, and so does the cell where the program makes its calculation or its changes. In our example, i is used for the row number in Cells(i, 1), and also for the value written in the cell.

What’s next

In this code, the same line Cells(i, 1) = i is written three times. To avoid this heavy way of writing, we will now study the loops in What Is a Loop in VBA?.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *