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:
- An order may have any number of line-items. This is a one-to-many relationship.
- 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.
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!
Recent Comments