Variables and loops in VBA in Excel - Dashboard Guide

Dashboard Mode - Variables and loops in VBA

Dashboard Goal

This dashboard helps track monthly sales and calculates total sales using VBA variables and loops. It shows how to use variables to store data and loops to process multiple rows automatically.

Sample Data

Month	Sales
January	100
February	150
March	200
April	250
May	300

Dashboard Components

• KPI Card: Total Sales
  Formula: VBA code uses a variable totalSales to add sales from each month inside a loop.
  Code snippet:

  Dim totalSales As Double
  Dim i As Integer
  
  totalSales = 0
  For i = 2 To 6 'Rows with sales data
      totalSales = totalSales + Cells(i, 2).Value
  Next i
  MsgBox "Total Sales: " & totalSales

  
  Output: Message box shows 1000 (sum of 100+150+200+250+300)
• Table: Monthly sales data as shown above.

Dashboard Layout

+----------------------+------------------+
|      Monthly Sales    |   Total Sales    |
|      (Table)          |   (KPI Card)     |
|                      |                  |
+----------------------+------------------+

Interactivity

The VBA code runs when you click a button. It loops through the sales data rows, adds each sales value to the totalSales variable, and then shows the total in a message box. You can update sales numbers in the table and rerun the code to see the new total.

Self Check

If you add a new month with sales in row 7 and update the loop to For i = 2 To 7, what will the new total sales be? How does changing the loop range affect the total?

Key Result

Dashboard shows monthly sales and calculates total sales using VBA variables and loops.