Object oriented VBA: a report builder using a template and row data (Concrete example of user defined classes)

I will display several examples of VBA classes and their use in modules, with large pieces of code that you can copy, modify and reuse if you feel like it.

Object oriented VBA programming style is not an easy skill to acquire and even more so if you don’t practice. So the best advice once can possibly give you is practice, practice, practice! That being said, there is nothing like a set of examples to learn from your peers. Once you have understood the way object oriented VBA works, it can even be fun to write applications this way.

Today I thought it would be fun to create a class that builds reports using a data file, a template and a parameter file giving the location of the cells containing data.

The case study: the zoo accountant

You are the new accounting and reporting intern at the city zoo. Your boss insisted that you should do a monthly reporting on the number of animals in the zoo every month, using data he archived in a csv file. He has given you a specific template to use for this very simple reporting, that only contains the name of the month, the number of elephants and the number of zebras.

The objective of the application is to generate reliably the reports for every month that was archived in a row, in a way that protects the data and the initial template.

The solution below is only a suggestion and of course the “right” application to use always depends on the specific situation you are in.

The files needed in the application

Our application needs 3 files in input:

  • A data file that contains on each line the data for one full report. There will be one report for each line in this data file.
  • A template file that displays the style to be used in each cell of the reports (given that all reports are similar).
  • A location parameter file that contains the location of each variable to be inserted in the report. This is more conceptual: the application will try to find the names of the columns of the data file inside the location file, and use this location to insert the relevant data in the relevant spot in the template.


The VBA class code

Public ReportWk As Workbook
Private ParametersWk As Workbook
Public CsvContent As Collection
Private ColumnNames As Variant
Property Let setTemplate(PathName As String)
    Workbooks.Open PathName, UpdateLinks:=False, ReadOnly:=True
 Set ReportWk = ActiveWorkbook
End Property
Property Let setLocationParams(PathName As String)
    Workbooks.Open PathName, UpdateLinks:=False, ReadOnly:=True
    Set ParametersWk = ActiveWorkbook
End Property
Function BindData(csvFilePath As String, Optional sep As String = ";")
' Parse the csv file into a collection
    Set CsvContent = New Collection
    FileNb = FreeFile
    Open csvFilePath For Input As FileNb
    'read the csv file
    LineCount = 1
    Do Until EOF(FileNb)
        Line Input #FileNb, textline
        If textline <> "" And LineCount = 1 Then
            ' headers in the csv file
            ColumnNames = Split(textline, sep)
        ElseIf textline <> "" Then
            Set CsvLine = New Collection
            For i = 0 To UBound(Split(textline, sep))
                CsvLine.Add Key:=ColumnNames(i), Item:=Split(textline, sep)(i)
                ' each items will be named by its corresponding column header
            CsvContent.Add CsvLine
         'csvContent is a collection that contains collections
        End If
        LineCount = LineCount + 1
    Close #FileNb
End Function
Sub create()
    Dim cellLocation As Range
    For Each LineofData In CsvContent
    ' save a report for each line of data
        For Each DataColumn In ColumnNames
            Set cellLocation = ParametersWk.Sheets(1).UsedRange.Find(DataColumn)
            ' Use the coordinates of this cell to put the corresponding data in the template
            ' note that with find we would need to add an error handler
            ' to take care of the case of failure to match
            ReportWk.Sheets(1).Cells(cellLocation.Row, cellLocation.Column).Value = LineofData(DataColumn)
        ReportWk.SaveAs LineofData("month"), ReadOnlyRecommended:=True
        ' save the report with the name of the month
End Sub
Sub closeReport()
End Sub

The VBA module code


Sub test()
Dim Report1 As New cReport
Report1.setTemplate = "C:\Users\....\POO VBA\template.xlsx"
Report1.setLocationParams = "C:\Users\....\POO VBA\location.xlsx"
Report1.BindData "C:\Users\....\POO VBA\test.csv", ","
End Sub
Matthieu Liatard
Remember this!
Remember this
Take a quizz on this subject

Hello :)
At Question-Player, we want to share more knowledge and help you never forget anything ever. And for free.

To show us your support, you can create an account:

Login / Create an account
Rate this article :

Up next

Related posts