Develop Customized Data Reporting Dashboards with Excel VBA

Step 1: Prepare Data

Before you can create a dashboard, you need to have your data organized in Excel. This data could come from various sources such as databases, CSV files, or internal records.

Make sure your data is:

• Well-structured (tables or ranges)
• Consistently formatted (dates, numbers, text)
• Clean (no missing or erroneous data)

Step 2: Design Your Dashboard Layout

In this step, decide how you want your dashboard to appear:

• Graphs & Charts: Think about the key metrics you want to track and display. Choose from bar charts, pie charts, line graphs, etc.
• Tables: Use PivotTables or simple tables to display key data.
• Widgets: You may want summary statistics, such as totals, averages, or conditional formatting to highlight specific data points.

Step 3: Open Excel and Access the Visual Basic Editor

1. Open your Excel workbook where you want to create the dashboard.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Step 4: Insert a New Module

Once in the VBA editor:

1. In the left-hand panel, right-click on VBAProject (Your Workbook Name).
2. Choose Insert → Module.

This creates a new module where you will write your VBA code for automating the dashboard creation.

Step 5: Write VBA Code for Dashboard Automation

Here’s an example of VBA code to create a customized dashboard. This will include a basic layout for a dashboard and automate creating a chart based on your data.

Explanation of Code:

• Chart Creation: We will create a basic line chart using data from a table or range in the Excel sheet.
• Dynamic Data: The macro will update the chart every time new data is added or modified.
• Dashboard Elements: The macro will also add titles, labels, and other elements to give a professional appearance to your dashboard.

Sub CreateDashboard()
    ' Declare Variables
    Dim ws As Worksheet
    Dim dashboardSheet As Worksheet
    Dim dataRange As Range
    Dim chartObj As ChartObject
    Dim chartDataRange As Range
    ' Set the worksheet objects
    Set ws = ThisWorkbook.Sheets("Data") ' Assume data is in a sheet named "Data"
    Set dashboardSheet = ThisWorkbook.Sheets("Dashboard") ' Dashboard sheet
    ' Clear previous dashboard
    dashboardSheet.Cells.Clear
    ' Create a Range for data - adjust this based on your data structure
    Set dataRange = ws.Range("A1:D10") ' Adjust data range as necessary
    ' Create a Chart Object
    Set chartObj = dashboardSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    ' Set chart data source
    Set chartDataRange = dataRange
    chartObj.Chart.SetSourceData Source:=chartDataRange
    ' Change chart type to Line Chart
    chartObj.Chart.ChartType = xlLine
    ' Customize Chart Titles and Labels
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Sales Trends"
    ' Customize X and Y Axis Titles
    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Month"
    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales"
    ' Add Text box with some descriptive information
    dashboardSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 50, 300, 30).TextFrame.Characters.Text = "This is a Sales Dashboard"
    ' Format the dashboard with some color and style
    dashboardSheet.Cells(1, 1).Font.Bold = True
    dashboardSheet.Cells(1, 1).Font.Size = 14
    dashboardSheet.Cells(1, 1).Interior.Color = RGB(220, 220, 220) ' Light gray background
    ' You can also add more elements, such as slicers, tables, etc., based on your needs
End Sub

Explanation of Code:

1. Worksheet Setup: We declare two worksheets: one for the data (ws) and one for the dashboard (dashboardSheet).
2. Clear Previous Dashboard: We clear any existing content in the dashboard sheet before creating the new one.
3. Data Range: The dataRange is set to the range containing your data. Adjust it as needed to match the actual data range in your sheet.
4. Chart Creation: A chart is added to the dashboard sheet, and its data source is set to the specified range (dataRange). We use a line chart as an example.
5. Customizations: Titles and axis labels are added to the chart to make it more informative.
6. Textbox: A simple text box is added to the dashboard to provide a brief description or title for your dashboard.
7. Formatting: Some formatting is applied to the dashboard for a better visual look.

Step 6: Run the Macro

Once the code is written, you can run the macro by:

1. Pressing F5 in the VBA editor.
2. Alternatively, go back to Excel, and in the Developer Tab, you can assign the macro to a button.

Sample Output:

After running the macro, you will have:

1. A line chart showing trends for the selected data (in this case, sales data).
2. A formatted dashboard with titles, axis labels, and a text box with a brief description.
3. The chart will be automatically updated if the data in the range changes.

Additional Enhancements:

• You can add more charts and graphs, such as pie charts, bar charts, etc.
• Use PivotTables to create dynamic reports based on the data.
• Add filters or slicers to allow users to interact with the data.
• Use conditional formatting to highlight key metrics, such as high sales or low performance.

By following these steps, you can automate the creation of customized data reporting dashboards in Excel using VBA, which will save you time and ensure consistency in your reporting process.