Excelspreadsheet~10 mins

Variables and loops in VBA in Excel - Cell-by-Cell Formula Trace

Choose your learning style9 modes available

Learn Why Deep Sheet Try Challenge Scenario Recall Dash

Sample Data

Column A has numbers 1 to 5. Column B is empty and will hold the running sum calculated by VBA code using variables and loops.

Cell	Value
A1	Start
A2	1
A3	2
A4	3
A5	4
A6	5
B1	Sum
B2
B3
B4
B5
B6

Formula Trace

Sub SumLoop()
 Dim total As Integer
 Dim i As Integer
 total = 0
 For i = 2 To 6
  total = total + Cells(i, 1).Value
  Cells(i, 2).Value = total
 Next i
End Sub

Step 1: total = 0

Step 2: i = 2, total = 0 + Cells(2,1).Value (which is 1)

Step 3: Cells(2,2).Value = total

Step 4: i = 3, total = 1 + Cells(3,1).Value (which is 2)

Step 5: Cells(3,2).Value = total

Step 6: i = 4, total = 3 + Cells(4,1).Value (which is 3)

Step 7: Cells(4,2).Value = total

Step 8: i = 5, total = 6 + Cells(5,1).Value (which is 4)

Step 9: Cells(5,2).Value = total

Step 10: i = 6, total = 10 + Cells(6,1).Value (which is 5)

Step 11: Cells(6,2).Value = total

Step 12: Loop ends after i=6

Cell Reference Map

    A     B
1 |Start |Sum |
2 |  1   | -> |
3 |  2   | -> |
4 |  3   | -> |
5 |  4   | -> |
6 |  5   | -> |

Cells in column A (A2 to A6) are read for values. Column B (B2 to B6) cells are written with running totals by the VBA loop.

Result

    A     B
1 |Start |Sum |
2 |  1   |  1 |
3 |  2   |  3 |
4 |  3   |  6 |
5 |  4   | 10 |
6 |  5   | 15 |

Column B shows the running sum of values from column A after the VBA loop runs.

Sheet Trace Quiz - 3 Questions

Test your understanding

What is the initial value of the variable 'total' before the loop starts?

AValue in A2

DUndefined

Key Result

VBA loop uses a variable to accumulate a running total by reading cells in a column and writing results to another column.