When preparing a dynamic report in Excel, you need to arrange the data in the report structure. In this Blog post I demonstrate how to clear the report range and re-populate it with data.
Types of reports
Reports can take many forms, as far as presenting data is concerned.
A report is any artifact created from data for end use, or as required by another system as input.
A report can be a single page summarizing information about something (see the certificate example of last week’s Blog post), it can be an order for an employee to do something (such as in a production order) and it can be a listing of records (rows) of data with headers and possibly sub-sections summaries.
In this Blog post I will focus on the classic presentation of records presented as a table, without breaking the report by any column, meaning, there are no sub-headers or sub-summaries. Here is an example of such a report:
Understanding the dynamic report process
A dynamic report is an arrangement of formatted data and graphic elements, generated every time with updated data.
The finished report can be used in several ways and routed to several outlets. It can be saved as a separate Excel Workbook, sent to the printer, published as a PDF file, and emailed as an attachment.
A dynamic report is automatically generated and is triggered by a specific event, such as a click of a button.
The typical steps for the automatic report generation process we need to program include:
- Collecting and staging the data in the structure required
- Clearing contents, formatting, merged cells, borders and resetting rows’ height in the dynamic range of the report
- Populating the report range with data
- Adding additional elements such as charts (if needed)
- Applying layout and format: rows’ heights, numbers format, colors, borders, merging cells
- Save/publish/print the final report as needed
Populating the report with data
In my previous Blog post I explained and presented the printing aspects of a report in a PDF format.
Today, I will present some VBA code to populate the report with data.
First, you design A dedicated Worksheet for the report with its headers.
The access point to the report data range is by a single cell: the top-left cell in the data region of the report. In the above report example, it would be the cell to receive “110” as the first employee ID. Therefore, I name this range (a single-cell range) to be: Report_Data_Anchor.
The main controlling Subroutine orchestrating the report process, may look like the following set of statements to fulfill steps 1 to 3 above:
Const EMPLOYEES_REPORT_COLUMNS = 5
Sub EmployeesReportMain()
Set rngReportAnchor = ThisWorkbook.Names("Report_Data_Anchor").RefersToRange
Dim arrReport() As Variant
Dim lngRecords As Long
Dim rngReportAnchor As Range
Call ClearReportData("Report", "Report_Data_Anchor", EMPLOYEES_REPORT_COLUMNS)
lngRecords = PopulateEmployeesReportArray(arrReport)
If (lngRecords > 0) Then
lngRecords = PlaceArrayDataInReportSheetAtPointer(rngReportAnchor, arrReport)
End If
End Sub
(error handling, totals accumulators and such were omitted for brevity).
Clearing the report data area
The first statement calls for a Subroutine to clear all content and formatting from the last time this report was produced.
As complex reports may contain colors, borders, rows heights, merged cells, number format etc., we need to clear everything from the data area.
I find the last row populated with data by taking the last populated cell in either column “A” or in the first report column and add an extra 10 lines for any report summaries presented below the data records area.
I usually start the data columns in column “B”, but I’m looking at column “A” anyway, for possible special sub-headers I may have placed there.
Sub ClearReportData(ByVal strSheetName As String, ByVal strAnchorRangeName As String, ByVal intReportColumns As Integer)
Dim rngAnchor As Range
Dim FirstRow As Long
Dim LastRow As Long
With ActiveWorkbook.Sheets(strSheetName)
Set rngAnchor = .Range(strAnchorRangeName)
FirstRow = rngAnchor.Row
LastRow = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, .Cells(Rows.Count, rngAnchor.Column).End(xlUp).Row) + 10
With .Cells(FirstRow, rngAnchor.Column).Resize(LastRow, intReportColumns)
.UnMerge
.ClearContents
.ClearFormats
.EntireRow.RowHeight = STANDARD_ROW_HEIGHT
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlGeneral
.Orientation = xlHorizontal
End With
End With
End Sub
Populating the data records cells
Fetching the data for the report is a whole discussion for a couple of Blog posts.
Among the possible approaches I will mention:
- A staging table in a hidden helper Worksheet with Excel Worksheet formulas
- In-memory processing arrays with loops
- SQL over data tables in Excel Worksheets
- SQL over data tables stored in an external relational database
I rarely use option one with data records reports, for more than one reason. I’d only recommend considering this option for a small amount of data, usually a summary report without listing records.
For data records listing I usually use SQL. It is handy and performant and once you have the standard functions to serve the automation of reports it is fast to set up.
In some cases, I’d upload Excel tables into arrays and loop over these arrays to construct the report data set into another array.
Either way, with data records listing reports I always seek to have an array holding the resulting report data set after all extractions and calculations.
As SQL is beyond the scope of this Blog post, I do want to show an example of looping through arrays, to complete the above code.
The following Function uploads employees from an employees table into arrEmp(), and uploads orders from an orders table into arrOrders().
The target array for the report data is passed into the Function: arrReport(). Recall that arrays are passed ByRef, therefore the calling Subroutine will enjoy a fully populated array accessible for next steps of the program once this Function completes.
Function PopulateEmployeesReportArray(ByRef arrReport() As Variant) As Long
Dim arrEmp() As Variant
Dim arrOrders() As Variant
Dim lngRows As Long
Dim i As Long
With ThisWorkbook.Worksheets("Data")
arrEmp = .Range("ExcelarateEmployeesTable[#Data]").Value
arrOrders = .Range("ExcelarateOrdersTable[#Data]").Value
End With
lngRows = UBound(arrEmp, 1) - LBound(arrEmp, 1) + 1
If (lngRows > 0) Then
ReDim arrReport(1 To lngRows, 1 To EMPLOYEES_REPORT_COLUMNS)
For i = LBound(arrEmp, 1) To UBound(arrEmp, 1)
arrReport(i, 1) = arrEmp(i, 1) 'Emp ID
arrReport(i, 2) = arrEmp(i, 2) 'Name
arrReport(i, 3) = arrEmp(i, 6) 'Region
arrReport(i, 4) = arrEmp(i, 5) 'Quota
arrReport(i, 5) = GetTotalSalesPerEmployee(CLng(arrEmp(i, 1)), arrOrders) 'Total sales
curTotalSalesInReport = curTotalSalesInReport + arrReport(i, 5)
Next i
End If
PopulateEmployeesReportArray = lngRows
End Function
The last report column (built into column 5 of the array) is a summary of all orders placed by the currently iterated employee. For that I call a dedicated Function to loop through the orders array and summarize those values that meet the relevant criteria: orders of that employee, for the report year and that are not in Cancelled status. Here it is:
Function GetTotalSalesPerEmployee(lngEmpId As Long, arrOrders() As Variant) As Currency
Dim intYear As Integer
Dim curTotalSales As Currency
Dim i As Long
intYear = ThisWorkbook.Names("Report_Year_Selector").RefersToRange.Value
curTotalSales = 0
For i = LBound(arrOrders, 1) To UBound(arrOrders, 1)
If (Year(CDate(arrOrders(i, 3))) = intYear) And _
(arrOrders(i, 2) <> "Cancelled") And _
(CLng(arrOrders(i, 5)) = lngEmpId) Then
curTotalSales = curTotalSales + CCur(arrOrders(i, 6))
End If
Next i
GetTotalSalesPerEmployee = curTotalSales
End Function
Lastly, we need to transfer the report data from the designated array to the Worksheet range expecting the report data.
This is a simple one assignment statement taking a 2-dimentional array and “throwing” its content into a range:
Function PlaceArrayDataInReportSheetAtPointer(rngAnchor As Range, ByRef arr() As Variant) As Long
Dim lngRows As Long
Dim intCols As Integer
lngRows = UBound(arr, 1) - LBound(arr, 1) + 1
intCols = UBound(arr, 2) - LBound(arr, 2) + 1
rngAnchor.Resize(lngRows, intCols).Value = arr
PlaceArrayDataInReportSheetAtPointer = lngRows
End Function
What else is missing
In this Blog post I focused on the steps for arranging and placing the data in a report.
This is a simple example of a report, and typically there would also be a summary section with totals and possibly sub-totals and graphic elements.
Since the report length is unknown, it could span more than one page. I therefore define the top rows of the report Worksheet, including the columns’ headers row, to repeat at the top of each page. This is done in the Page Setup dialog box, of course.
I trust I provided you with a solid foundation for data records reports, on which you can build more as per your needs.
Finally, our report is missing formatting. For this, you’ll have to get back next week in which I demonstrate highly useful generic functions for formatting numbers in the report columns, borders, alignment and colors!
In the meantime, share this Blog post far and wide, for others to appreciate your big heart!
WANT TO LEARN PROGRAMMING WITH EXCEL VBA?
I created a complete 8-course program for you.
From programming to databases with Excel VBA to deliver business-grade solutions.
Take the first course today: Computer Programming with Excel VBA.
Click the button to see the complete program and start now!
Recent Comments