In this advanced chapter of OOP with Excel VBA, we implement objects composition with a dynamic collection in Excel VBA using Class Modules. Not for the faint of heart…

In Part 1 on this exercise of implementing OOP in Excel VBA, we left off with a half-baked order object. It had some properties and a couple of methods, but its line-items were missing.

Today we’re going to round up our order object to include its line-items.

Thinking about the right structure and arrangement of the line-items within the order object, two main characteristics should guide our thinking:

  1. An order may have any number of line-items. This is a one-to-many relationship.
  2. All line-items have the same structure. We’re dealing with similar records that together form a collection of records.

A collection seems like a perfect structure to maintain the dynamic nature of the number of line-items per each order. But a collection of what?

One option that come to mind is a user-defined type. This would fit perfectly with the record nature of the line-item. In this approach, we would have defined a user type representing a line-item record and construct a collection of variables of that type. Instead of a collection, we could also construct an array of these line-item types.

These solutions are counter-intuitive to the object oriented programming approach we’re perfecting here and will render the elegance of our order object awkward.

Here comes another object

As we’re now thinking “objects”, it only comes naturally that a line-item is an object!

Our strategy would be to have each order object hold a collection of line-items objects.

Let’s start off by implementing a new Class Module for our line-item object, I named it clsLineItem:

Private p_LineItemIndex As Integer

Private p_Product As Long

Private p_Qty As Integer

Private p_Total As Single

 

Property Get LineItemIndex() As Integer

    LineItemIndex = p_LineItemIndex

End Property

 

Property Get Product() As Long

    Product = p_Product

End Property

 

Property Get Quantity() As Long

    Quantity = p_Qty

End Property

 

Property Get LineTotal() As Long

    LineTotal = p_Total

End Property

 

Public Sub CreateLineItem(intLineIndex As Integer, lngProduct As Long, intQty As Integer, sngAmount As Single)

   

    p_LineItemIndex = intLineIndex

    p_Product = lngProduct

    p_Qty = intQty

    p_Total = sngAmount

End Sub

Order Object Composition

Next, we need to adjust our clsOrder Class Module, to accommodate our collection of line-items objects.

We need to declare a private collection and implement adding and removing line-items.

Private p_colLineItems As Collection

Next, we need to change the AddLineItem and RemoveLineItem methods. When adding, we are now receiving a line-item object and simply adding it to the private collection:

Public Sub AddLineItem(itmLineItem As clsLineItem)

    'Process new line-item here

    p_colLineItems.Add itmLineItem

    'Update the order's object variables

    p_LineItemsCount = p_LineItemsCount + 1

    p_OrderTotal = p_OrderTotal + itmLineItem.LineTotal

End Sub

We remove a line item by its index (line number within the order). Here it is a little tricky, as we need to fetch the line item amount before we actually remove it, so that we can update the order total variable – subtract the removed line-item’s amount from the order total. Nothing too complicated that a helper private Sub can’t polish for us:

Public Sub RemoveLineItem(intLineItemRow As Integer)

    'Update the order's object variables while removing the line item

    p_LineItemsCount = p_LineItemsCount - 1

    p_OrderTotal = p_OrderTotal - RemoveLineItemFromCollection(intLineItemRow)

End Sub

 

Private Function RemoveLineItemFromCollection(intLineItemRow As Integer) As Single

'Returns the line-item amount

    Dim itmLine As clsLineItem

   

    iRowIndex = 0

    For Each itmLine In p_colLineItems

        iRowIndex = iRowIndex + 1

        If itmLine.LineItemIndex = intLineItemRow Then

            RemoveLineItemFromCollection = itmLine.LineTotal

            p_colLineItems.Remove iRowIndex

            Exit Function

        End If

    Next

End Function

We’re looping through the collection until we find our winning line-item by its row index. Same technique applies in a new method we need that returns a line-item object (it is a Function this time, not a Sub) based on a row index of that line item. This is part of the public interface and allows to query line items’ data of an order:

Public Function GetLineItem(intLineIndex As Integer) As clsLineItem

    Dim itmLine As clsLineItem

   

    For Each itmLine In p_colLineItems

        If itmLine.LineItemIndex = intLineIndex Then

            GetLineItem = itmLine

            Exit Function

        End If

    Next

End Function

Lastly, as in most cases all line items of an order are needed together, let’s offer a public method for returning the line-items collection:

Public Function GetLineItems() As Collection

    Set GetLineItems = p_colLineItems

End Function

The Complete Order Class Module

After all adjustments, our new clsOrder Class Module should look like this:

Private p_OrderDate As Date

Private p_LineItemsCount As Integer

Private p_OrderTotal As Single

 

Private p_colLineItems As New Collection

 

Private Function RemoveLineItemFromCollection(intLineItemRow As Integer) As Single

'Returns the line-item amount

    Dim itmLine As clsLineItem

   

    iRowIndex = 0

    For Each itmLine In p_colLineItems

        iRowIndex = iRowIndex + 1

        If itmLine.LineItemIndex = intLineItemRow Then

            RemoveLineItemFromCollection = itmLine.LineTotal

            p_colLineItems.Remove iRowIndex

            Exit Function

        End If

    Next

 

End Function

 

 

'Properties

 

Property Get OrderDate() As Date

    'Return current order date

    OrderDate = p_OrderDate

End Property

 

Property Let OrderDate(datOrder As Date)

    'Store new order date

    p_OrderDate = datOrder

End Property

 

Property Get LineItemsCount() As Integer

    'Return current count of line-items

    LineItemsCount = p_LineItemsCount

End Property

 

Property Get TotalAmount() As Single

    'Return current order total

    TotalAmount = p_OrderTotal

End Property

 

 

'Methods

 

Public Sub AddLineItem(itmLineItem As clsLineItem)

    'Process new line-item here

    p_colLineItems.Add itmLineItem

    'Update the order's object variables

    p_LineItemsCount = p_LineItemsCount + 1

    p_OrderTotal = p_OrderTotal + itmLineItem.LineTotal

End Sub

 

Public Sub RemoveLineItem(intLineItemRow As Integer)

   

    'Update the order's object variables while removing the line item

    p_LineItemsCount = p_LineItemsCount - 1

    p_OrderTotal = p_OrderTotal - RemoveLineItemFromCollection(intLineItemRow)

   

End Sub

 

 

Public Function GetLineItem(intLineIndex As Integer) As clsLineItem

 

    Dim itmLine As clsLineItem

   

    For Each itmLine In p_colLineItems

        If itmLine.LineItemIndex = intLineIndex Then

            GetLineItem = itmLine

            Exit Function

        End If

    Next

 

End Function

 

Public Function GetLineItems() As Collection

    Set GetLineItems = p_colLineItems

End Function

Using our Order and LineItem Objects

Finally, we can make use of our objects by re-writing our (regular) module Orders. I remind you that we are processing the following two tables into a collection of orders and printing out a report with all orders data. A more detailed explanation of some of this code is waiting for you in the first part of this Blog post.

Excel VBA Object Oriented Programming

Our new Orders module now looks like this:

 Sub ProcessOrders()

    'Our orders collection

    Dim colOrders As New Collection

    'Our order object variable

    Dim objOrder As clsOrder

   

    'Our table variable

    Set tblOrders = ThisWorkbook.Worksheets("Data").ListObjects("OrdersTable")

   

    'Main processing loop

    For Each rowOrder In tblOrders.ListRows

        'Set an instance of an order object

        Set objOrder = New clsOrder

        'Set the date property value of our order object

        objOrder.OrderDate = rowOrder.Range(1, 3)

        'Call to process order's line items

        Call ProcessOrderLineItems(objOrder, rowOrder.Range(1, 1))

        'Add order to our orders collection

        colOrders.Add objOrder

    Next

   

    'We now have an elegant collection of orders, let's print their details:

    Call PrintOrderReport(colOrders)

   

    'Release order lines table autofilter

    ThisWorkbook.Worksheets("Data").ListObjects("OrderLinesTable").Range.AutoFilter

End Sub

 

 

Sub ProcessOrderLineItems(objOrder As clsOrder, lngOrderNumber As Long)

    Dim iRowIndex As Integer

    'Declaring our line-item object

    Dim objLineItem As clsLineItem

 

    Set tblOrdersLines = ThisWorkbook.Worksheets("Data").ListObjects("OrderLinesTable")

       

    'Filter order lines table on current order number

    tblOrdersLines.Range.AutoFilter Field:=1, Criteria1:="=" & lngOrderNumber

    'Loop all filtered rows

    iRowIndex = 0

    For Each cell In Intersect(tblOrdersLines.ListColumns(1).DataBodyRange, _

        tblOrdersLines.Range.SpecialCells(xlCellTypeVisible))

        iRowIndex = iRowIndex + 1

        'Process order line item

        'We instantiate a line item object and populate it with data

        Set objLineItem = New clsLineItem

        objLineItem.CreateLineItem iRowIndex, cell.Offset(0, 2), cell.Offset(0, 3), cell.Offset(0, 4)

        'Adding the line item object to the order object

        objOrder.AddLineItem objLineItem

    Next

End Sub

 

 

Sub PrintOrderReport(ByRef colOrders As Collection)

 

    Dim colLineItems As New Collection

    Dim lineItem As clsLineItem

   

    For Each ord In colOrders

        Debug.Print "Order Details: ", ord.OrderDate, ord.LineItemsCount, ord.TotalAmount

        Debug.Print "  Line Items: "

        Set colLineItems = ord.GetLineItems

        For Each lineItem In colLineItems

            Debug.Print "  ", lineItem.LineItemIndex, lineItem.Product, lineItem.Quantity, lineItem.LineTotal

        Next

        Debug.Print vbCrLf 'New line space between orders

    Next

End Sub

When running the ProcessOrders Sub, we end up with the following report of our orders:

Order Details:              25/04/2020     2             107

  Line Items:

               1             28            2             70

               2             33            1             37

 

 

Order Details:              22/04/2020     1             109

  Line Items:

               1             32            2             109

 

 

Order Details:              22/04/2020     3             294

  Line Items:

               1             10            2             90

               2             19            2             88

               3             16            2             116

Summary

Over the last 4 Blog posts, we learned about objects in computer programming, OOP in general and how OOP is implemented in Excel VBA.

We demonstrated how complex use of Class Modules, or objects, is realized in Excel VBA with elaborated code modules.

Remember that OOP has its advantages and disadvantages, so be familiar with its benefits and chose where it makes most sense to use.

I know that this Blog series is most valuable to anyone using Excel VBA, so be a good friend and share this widely, so that we can help many other VBA programmers too.

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!