Object Oriented Programming with Excel VBA – Part 1

Ever wanted a concise, clear with code examples guide to implement OOP in Excel VBA? It is your lucky day. Read on…

After we covered in detail what OOP is in last week’s Blog post, we’re ready to see how this works in Excel VBA.

Keep your eyes on the Object

The key concept to keep in mind as we implement objects in Excel VBA, is that we are creating our own custom objects, on top of the available objects at our disposal out of the box.

The image here shows a list of the available properties and methods of the Workbook object.

This list is the exposed interface of the Workbook object.

We know that the ActiveSheet property is the object’s variable that will tell us which is the active Worksheet of that Workbook.

We know also that the Activate method will set the Workbook in question to be the active Workbook.

Those Microsoft engineers who had implemented the Workbook object (or Class), took care to write the actual code (or Sub) behind that Activate method, and they also allocated some memory space to hold the pointer to the ActiveSheet.

With that in mind, we are about to create our own new Class, or object, meaning it is our responsibility to decide which properties and methods we expose as its public interface, and to implement the code behind them.

The Class Module

A Class Module is our canvas for creating the blueprint (or template) of our custom object.

Suppose we’re planning on reading a list of orders from a Worksheet table (or a csv file), and we need to scan all rows and process each order.

Typically, we would store each order’s data in a suitable data structure (such as a collection or an array) and process that data per each order in a loop.

With OOP, we understand that we have a case of order as an object, therefore we will create the Order Class with all the required properties and methods for communicating and working with an order object.

As you create a new Class Module, assign the object’s class name to be its name property. This is the first property for you to update in the Class Module’s properties (hit F4 to show the properties window if it’s not open).

I call my Class Module clsOrder.

As I already prepared a standard module I will use later to process my orders, my project looks like this:

Defining the object’s (public) interface

Let’s decide that our Order object will expose the following properties:

  • OrderDate. We allow to set and get the order’s date value.
  • LineItemsCount. We only allow to get the value of this property.
  • TotalAmount. We only allow to get the value of this property.

Note that some properties are read-only. Only the OrderDate property in our Order object can be explicitly set by the calling code that uses an instance of the Order object.

Our Order object will expose the following methods:

  • AddLineItem. Besides adding the actual line-item, this method has the responsibility to increase the value of the LineItemsCount by one and update the TotalAmount of the order by adding the line-item amount to it.
  • RemoveLineItem. Besides removing the actual line-item, this method has the responsibility to decrease the value of the LineItemsCount by one and update the TotalAmount of the order by subtracting the line-item amount to it.

Let’s start writing the public skeleton of our Order Class to accommodate its public interface. We do that in our clsOrder Class Module.

Defining Properties

Our OrderDate property interface is defined like this:

Property Get OrderDate() As Date

    'Return current order date

End Property

 

Property Let OrderDate(datOrder As Date)

    'Store new order date

End Property

We implement a “Getter” to allow getting the current value stored within the object’s instance and we implement a “Setter” to allow storing a new value within the object’s instance.

We now need to handle the hidden part of the order date’s property: its actual storage within the object’s instance.

For that we define a private variable in the Class Module:

Private p_OrderDate as Date

The Private qualifier renders this variable NOT part of the public interface of that object. In other words, Intellisense will not show this variable in the properties and methods list as we use an instance of that order object, and we will not have access to it from outside of an order instance.

I will also remind you that each instance of the order object encapsulates its own set of values, therefore each order carries its own p_OrderDate.

Now that we have allocated storage for our order object, we can complete the implementation of the OrderDate property:

Private p_OrderDate As Date

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

Note the elegant exchange of data between the consumer of an order object instance and its internal storage, facilitated by the “Getter” and “Setter”. On one hand they expose an interface outbound, and on the other hand only they can access the internal (private) variable storing data for that order object instance.

This is the time to recall that in VBA we assign values to objects differently from how we assign values to variables. Recall that variables pointing to objects are assigned their referenced object using the Set statement like this:

Set wkbMyWorkbook = ActiveWorkbook

While static variables are assigned values using the Let statement:

Let intAge = 45

As the Let is optional, we typically omit and write it simply:

intAge = 45

This is no different in Class Modules and had we implemented a property that is an object in itself, we would have implemented its Setter like that:

Property Set OrderCustomer(objCustomer As Customer)

    'Process the objCustomer object here

End Property

 

Defining Methods

Let’s now implement our AddLineItem public interface. Its signature in our Class Module looks like this:

Private p_LineItemsCount As Integer

Private p_OrderTotal As Single

Public Sub AddLineItem(strProduct As String, sngAmount As Single)

    'Process new line-item here

    '

    'Update the order's object variables

    p_LineItemsCount = p_LineItemsCount + 1

    p_OrderTotal = p_OrderTotal + sngAmount

End Sub

Here, again, we draw the line between the exposed interface and the internal hidden elements of the object using the Public and Private qualifiers.

Our AddLineItems method accepts two parameters and it doesn’t return any value, hence it is implemented as a Sub. Not as a Function.

Note how our AddLineItems method is doing the internal updates to our private data, the line items count and the order total. Publicly, we only allow reading (or getting) their values, not setting them.

Our complete Class Module

Catering for all our public properties and methods, the public interface of our order Class Module would look like this:

Private p_OrderDate As Date

Private p_LineItemsCount As Integer

Private p_OrderTotal As Single

 

Private Function GetOrderLineItemAmount(lngOrderNumber, intLineItemRow) As Single

    'Get the amount of the order line-item passed

    Dim sngResult As Single

    'Calculate into sngResult…

    GetOrderLineItemAmount = sngResult

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(lngProduct As Long, sngAmount As Single)

    'Process new line-item here

    '

    'Update the order's object variables

    p_LineItemsCount = p_LineItemsCount + 1

    p_OrderTotal = p_OrderTotal + sngAmount

End Sub

 

Public Sub RemoveLineItem(lngOrderNumber As Long, intLineItemRow As Integer)

    'Process removal of line-item here

    '

    'Update the order's object variables

    p_LineItemsCount = p_LineItemsCount - 1

    p_OrderTotal = p_OrderTotal - GetOrderLineItemAmount(lngOrderNumber, intLineItemRow)

End Sub

Note that we have added a private function to assist our public method in removing a line item from the order.

Using real object instances in our program

So far, we only defined the blueprint of an order object. Remember, there is no order object yet – only the template that can yield real instances of orders based on this blueprint.

Let’s now make use of this object template and work with some orders!

Our input tables look like this:

In a standard module, we address our task of processing a table of orders.

We will add all orders to a collection of orders as we process each order. We are essentially transforming a table data structure of rows and columns into a convenient collection of objects that is suitable for processing and working with.

Our main processing Sub will implement a loop over all orders in the orders table.

For each order in the loop we create a new instance of an order object, add it to our orders collection and call a Sub to loop over the line items of the processed order. We use Excel’s built-in AutoFilter feature for efficiently scoping only the rows of the processed order. We apply an Intersect on the filtered range and the first column of the table, so that our loop is limited in iterations to only the total number of the order’s line items.

Finally, we produce a report of all orders by looping our orders collection and accessing its 3 properties.

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 – a new order is born!

        Set objOrder = New clsOrder

        'Set the date property value of our order object

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

        'Call to process the 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:

    For Each ord In colOrders

        Debug.Print ord.OrderDate, ord.LineItemsCount, ord.TotalAmount

    Next

   

    'Release order lines table autofilter

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

End Sub

 

 

Sub ProcessOrderLineItems(objOrder As clsOrder, lngOrderNumber As Long)

 

    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

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

        tblOrdersLines.Range.SpecialCells(xlCellTypeVisible))

        'Process order line item

        objOrder.AddLineItem cell.Offset(0, 2), cell.Offset(0, 4)

    Next

End Sub

 

What about Objects Events?

While we can define any property and method we want for our own objects, this is not the case with events.

In Excel VBA, every Class Module exposes two events:

  • Class_Initialize. Raised upon instantiating an object.
  • Class_Terminate. Raised upon destroying the object instance.

You can implement any of these events’ handlers in the Class Module, like that:

Private Sub Class_Initialize()

    'Run tasks upon creating a new object instance

End Sub

When is an object instantiated?

In the above example, the object is instantiated at the Set objOrder = New clsOrder statement – and this is when the Class_Initialize event gets fired.

Reminding you here that you can also combine the declaration of the object variable and the instantiating statement into a single statement, such that instead of these two statements:

Dim objOrder As clsOrder

Set objOrder = New clsOrder

You can run:

Dim objOrder As New clsOrder

The key here is that the object is instantiated with the “New” qualifier (not when it is dimensioned for memory space with the Dim statement).

When is an object terminated?

An object is terminated when it loses its scope, or when explicitly terminated by assigning Nothing to it:

Set objOrder = Nothing

Where are the line items of our Order?

The Orders Class Module helped us demonstrate the basic use of Class Modules.

Still, it seems our order object is missing something. In the real world, an order includes its line items. Our order object has 3 properties and a couple of methods, but no data structures (or better yet: objects) to hold its line items.

As you recall from last week’s Blog post in which we explained OOP, objects composition is about nesting objects within objects. Let’s complete our Order object to include its line items in part 2 of this post - click here to read it now!

COMING SOON - DON'T MISS!

Be the first to be invited to the upcoming Excel VBA Inner Circle.

The first to enroll as founding members will enjoy an exceptional opportunity to become part of this professional club.

Click the button below to enlist yourself to the waiting list.

Yes, I want to be the first to know
Close

50% Complete

Two Step

Once you submit your details, you'll receive an email with a confirmation link. That's it! you're subscribed!