Automating statistical analysis in Excel using VBA

Automating statistical analysis in Excel using VBA can help perform complex calculations quickly and generate reports repetitively without having to manually enter formulas each time. Below is a detailed VBA code that performs basic statistical analysis such as mean, standard deviation, variance, quartiles, and generates an automated report. The code is structured to work with data in a specific range of cells.

1. Objective

The goal of this code is to automate the statistical analysis of the following metrics:

• Mean
• Standard Deviation
• Variance
• Median
• Quartiles (Q1, Q2, Q3)
• Min / Max

The report will be generated in a new worksheet.

2. VBA Code: Automate Statistical Analysis

Step 1: Create a VBA Module

1. Open Excel and press Alt + F11 to access the VBA editor.
2. In the « Insert » menu, select « Module » to create a new module.
3. Paste the following code into this module.

Sub AutomateStatisticalAnalysis()
    ' Declare variables
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim DataRange As Range
    Dim LastRow As Long   
    ' Assign the data sheet and create a new sheet for the report
    Set wsData = ThisWorkbook.Sheets("Data") ' Replace "Data" with the name of your data sheet
    Set wsReport = ThisWorkbook.Sheets.Add
    wsReport.Name = "Statistical_Report"   
    ' Determine the last row with data in column A
    LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row   
    ' Define the data range for analysis (example: from A2 to A100)
    Set DataRange = wsData.Range("A2:A" & LastRow) ' Adjust range as needed   
    ' Add a header to the report
    wsReport.Cells(1, 1).Value = "Statistical Analysis Report"
    wsReport.Cells(2, 1).Value = "Metric"
    wsReport.Cells(2, 2).Value = "Value"   
    ' Perform the statistical calculations and insert them into the report
    wsReport.Cells(3, 1).Value = "Mean"
    wsReport.Cells(3, 2).Value = WorksheetFunction.Average(DataRange)   
    wsReport.Cells(4, 1).Value = "Standard Deviation"
    wsReport.Cells(4, 2).Value = WorksheetFunction.StDev(DataRange)   
    wsReport.Cells(5, 1).Value = "Variance"
    wsReport.Cells(5, 2).Value = WorksheetFunction.Var(DataRange)   
    wsReport.Cells(6, 1).Value = "Median"
    wsReport.Cells(6, 2).Value = WorksheetFunction.Median(DataRange)   
    wsReport.Cells(7, 1).Value = "Quartile 1 (Q1)"
    wsReport.Cells(7, 2).Value = WorksheetFunction.Quartile(DataRange, 1)   
    wsReport.Cells(8, 1).Value = "Quartile 2 (Q2)"
    wsReport.Cells(8, 2).Value = WorksheetFunction.Quartile(DataRange, 2)   
    wsReport.Cells(9, 1).Value = "Quartile 3 (Q3)"
    wsReport.Cells(9, 2).Value = WorksheetFunction.Quartile(DataRange, 3)   
    wsReport.Cells(10, 1).Value = "Min Value"
    wsReport.Cells(10, 2).Value = WorksheetFunction.Min(DataRange)   
    wsReport.Cells(11, 1).Value = "Max Value"
    wsReport.Cells(11, 2).Value = WorksheetFunction.Max(DataRange)   
    ' Format the report
    wsReport.Rows(1).Font.Bold = True
    wsReport.Rows(2).Font.Bold = True
    wsReport.Columns("A:B").AutoFit
    wsReport.Range("A1:B11").Borders(xlEdgeBottom).LineStyle = xlContinuous   
    ' Display a message
    MsgBox "Statistical analysis has been successfully generated in the sheet " & wsReport.Name, vbInformation
End Sub

3. Explanation of the Code

Declaring Variables:

• • wsData: Refers to the worksheet that contains the data to be analyzed.
  • wsReport: Refers to the worksheet where the statistical results will be displayed.
  • DataRange: The range of cells that contains the data for statistical analysis.
  • LastRow: Used to determine the last row with data in column A (adjust if necessary).

Assigning Worksheets:

• • The code refers to the « Data » sheet for the source data and creates a new « Statistical_Report » sheet to display the results.

Determining the Data Range:

• • DataRange is defined as the range of cells containing the data (from A2 to the last row in column A).

Performing Statistical Calculations:

• • The code uses the WorksheetFunction object to calculate the following statistics:
    • Mean with Average
    • Standard Deviation with StDev
    • Variance with Var
    • Median with Median
    • Quartiles with Quartile
    • Min and Max with Min and Max
• Inserting Results into the Report:
  • Each statistic is inserted into the corresponding cells in the new sheet.
• Formatting the Report:
  • The report is formatted with bold headers and auto-fitting columns for better readability.
  • Borders are added to the range for better structure.
• Information Message:
  • Once the analysis is complete, a message box will appear to inform the user that the report has been generated.

4. Using the Code

Make sure your data is in the « Data » sheet, with the values you want to analyze in column A (or adjust the range in the code if needed).

Go to the VBA editor (Alt + F11), create a module, and paste the code.

Run the code by pressing F5 or using the « Run » button in the VBA editor.

A new sheet will be created with the statistical analysis report.

5. Conclusion

This VBA code simplifies statistical analysis by automating calculations and quickly generating a clean report in a new worksheet. You can expand this code to include more complex analyses, charts, or additional statistical measures based on your needs.