Develop Customized Data Visualization Dashboards with Excel VBA

Creating customized data visualization dashboards using Excel VBA can be a powerful way to make your data more interactive and dynamic. With VBA (Visual Basic for Applications), you can build dashboards that not only present data in charts but also allow for user interaction, automation, and advanced customization. In this guide, I will walk you through a long and detailed example of how to create a data visualization dashboard using Excel VBA.

Step-by-Step Guide to Creating a Customized Data Visualization Dashboard with Excel VBA

Setting up the Dashboard Environment

Before diving into VBA coding, ensure that you have the necessary data structure set up in your Excel workbook. This will serve as the source for the data that you wish to visualize.

• Data Sheet: Prepare a data sheet (for example, named « Data ») with rows of data and columns for each category. For example:
• Date          | Sales | Expenses | Region
• 01/01/2025    | 5000  | 2000     | North
• 02/01/2025    | 6000  | 2100     | South
• 03/01/2025    | 7000  | 2300     | East
• Dashboard Sheet: Create another sheet (for example, named « Dashboard ») where the actual dashboard will be built. This sheet will contain charts, tables, and buttons for user interaction.

Writing VBA Code to Automate the Dashboard

Let’s write the VBA code to automate the creation of the dashboard.

Step 1: Open the VBA Editor

• Press Alt + F11 to open the VBA editor.
• In the VBA editor, go to Insert > Module to add a new module.

Step 2: Define the Basic Structure for Dashboard Creation

Now, let’s begin writing the basic structure of the VBA code. The goal is to create a chart, automate the refresh of data, and set up interactive controls.

Sub CreateDashboard()
    Dim wsDashboard As Worksheet
    Dim wsData As Worksheet
    Dim chart As ChartObject
    Dim dataRange As Range
    Dim lastRow As Long
    ' Set references to the worksheets
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsDashboard = ThisWorkbook.Sheets("Dashboard")
    ' Clear previous dashboard content
    wsDashboard.Cells.Clear
    ' Get the last row of data
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    ' Set the data range for the chart
    Set dataRange = wsData.Range("A1:D" & lastRow)
    ' Create a sales vs expenses chart
    Set chart = wsDashboard.ChartObjects.Add
    chart.Chart.SetSourceData Source:=dataRange
    chart.Chart.ChartType = xlLine ' Line Chart
    ' Customize chart appearance
    With chart.Chart
        .HasTitle = True
        .ChartTitle.Text = "Sales vs Expenses"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Date"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Amount"
        .SeriesCollection(1).Name = "Sales"
        .SeriesCollection(2).Name = "Expenses"
    End With
    ' Create a summary of total sales and expenses
    wsDashboard.Cells(1, 1).Value = "Total Sales:"
    wsDashboard.Cells(1, 2).Value = Application.WorksheetFunction.Sum(wsData.Range("B2:B" & lastRow))
    wsDashboard.Cells(2, 1).Value = "Total Expenses:"
    wsDashboard.Cells(2, 2).Value = Application.WorksheetFunction.Sum(wsData.Range("C2:C" & lastRow))
End Sub

*

Explanation of Code

1. Define Worksheets:
   • wsData refers to the sheet where the raw data is stored (in this case, « Data »).
   • wsDashboard refers to the sheet where the dashboard will be created (in this case, « Dashboard »).
2. Clear Previous Dashboard:
   • wsDashboard.Cells.Clear clears any existing content from the dashboard sheet.
3. Get Data Range:
   • lastRow is calculated to determine how many rows of data are present in the « Data » sheet.
   • dataRange is the range that holds the actual data for the chart.
4. Create a Chart:
   • A new chart is created using ChartObjects.Add, and the source data is assigned using SetSourceData.
   • The chart type is set to a line chart (xlLine), and customization is applied to the chart title, axis titles, and series names.
5. Summary Calculations:
   • Using Application.WorksheetFunction.Sum, the total sales and expenses are calculated and displayed in the dashboard sheet.

Adding Interactive Controls

One of the powerful features of Excel VBA is the ability to add interactive controls such as buttons, drop-down lists, and input fields. Let’s add a button to refresh the dashboard data.

Step 1: Add a Button to the Dashboard Sheet

1. Go to the « Dashboard » sheet in Excel.
2. Click on the « Developer » tab (if it is not visible, enable it from Excel Options).
3. Click on « Insert » and choose a Button (Form Control).
4. Draw the button on the sheet.

Step 2: Assign VBA Code to the Button

1. Right-click the button and select « Assign Macro ».
2. Choose the CreateDashboard macro.

Now, when the user clicks the button, the dashboard will refresh and display updated data.

Adding Filters and Interactivity

Let’s add a combo box to allow the user to filter data based on a specific region (e.g., North, South, East). This will help users view region-specific data on the dashboard.

Step 1: Add a ComboBox for Region Filter

1. Go to the « Developer » tab and click on « Insert » > « Combo Box » (ActiveX Control).
2. Place the combo box on the dashboard sheet.
3. Right-click on the combo box and choose « Properties. »
4. Set the properties:
   • Name: cmbRegion
   • ListFillRange: Data!D2:D (assuming the region is in column D of the « Data » sheet).

Step 2: Write VBA Code to Filter Data Based on Region

Modify the CreateDashboard macro to include filtering based on the selected region from the combo box.

Sub CreateDashboard()
    Dim wsDashboard As Worksheet
    Dim wsData As Worksheet
    Dim chart As ChartObject
    Dim dataRange As Range
    Dim lastRow As Long
    Dim selectedRegion As String
    ' Set references to the worksheets
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsDashboard = ThisWorkbook.Sheets("Dashboard")
    ' Clear previous dashboard content
    wsDashboard.Cells.Clear
    ' Get the last row of data
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    ' Get the selected region from the combo box
    selectedRegion = wsDashboard.Shapes("cmbRegion").ControlFormat.Value
    ' Filter data based on selected region
    If selectedRegion <> "" Then
        wsData.Rows.Hidden = False
        For i = 2 To lastRow
            If wsData.Cells(i, 4).Value <> selectedRegion Then
                wsData.Rows(i).Hidden = True
            End If
        Next i
    End If
    ' Set the data range for the chart
    Set dataRange = wsData.Range("A1:D" & lastRow)
    ' Create a sales vs expenses chart
    Set chart = wsDashboard.ChartObjects.Add
    chart.Chart.SetSourceData Source:=dataRange
    chart.Chart.ChartType = xlLine
    ' Customize chart appearance
    With chart.Chart
        .HasTitle = True
        .ChartTitle.Text = "Sales vs Expenses"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Date"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Amount"
        .SeriesCollection(1).Name = "Sales"
        .SeriesCollection(2).Name = "Expenses"
    End With
    ' Create a summary of total sales and expenses
    wsDashboard.Cells(1, 1).Value = "Total Sales:"
    wsDashboard.Cells(1, 2).Value = Application.WorksheetFunction.Sum(wsData.Range("B2:B" & lastRow))
    wsDashboard.Cells(2, 1).Value = "Total Expenses:"
    wsDashboard.Cells(2, 2).Value = Application.WorksheetFunction.Sum(wsData.Range("C2:C" & lastRow))
End Sub

Explanation of Filter Code

• ComboBox Value:
  The selected region is captured using wsDashboard.Shapes(« cmbRegion »).ControlFormat.Value.
• Data Filtering:
  If a region is selected, the code hides rows that do not match the selected region by iterating through all the rows and comparing the value in column D (the region column).

Conclusion

You’ve now created a customized data visualization dashboard in Excel VBA with dynamic charts and interactivity. Users can refresh the data, select different regions, and see visualized sales and expenses data. You can further enhance this dashboard by adding more interactivity (e.g., slicers, more charts, advanced filtering), incorporating additional data sets, and improving the design.