• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1238
  • Last Modified:

Learning to use OOP in VBA Excel

Hi Experts,
I'm currently trying to learn how to use classes in Excel VBA. I know its not truly OOP but I'm comfortable with just using VBA for now and will dabble into VB.Net in the future.

I came across a scenario and was wondering if the approach I've taken is on the right path and any input/constructive criticism is appreciated.

1. Investments earns various types of income. eg Fixed Interest Income, Money Market Interest Income and Other Income for the purpose of this example.
2. Income has to be classified as either Domestic Income or Foreign Income.
3. Income has to be classified as either for Accounting Purposes or for Tax Purposes.
4. Various Information required when dealing with above mentioned income is scattered through an Excel workbook.
5. Looking to centralise data through class objects via properties thereby removing the need to reference similar data types in various locations on the workbook.
6. The income class object will feed into other income type objects when reallocating components of its income. Eg. A fraction of the total Interest income may need to reclassify to Other income type for tax purposes.

My Approach:
I created the following classes:

1. clsIncome
a. Pointer to a subclass clsIncomeFI
b. Looking to create a method that sums up all the income in all of the subclasses.

2. clsIncomeFI  (A subclass that handles Fixed Interest income)
a. ReadWrite Property for Earned income.
b. ReadWrite Property for Opening Balance of the Income.
c. ReadWrite Property for Closing Balance of the Income.
d. Read Property for Accounting Income (determined based on business logic with data provided in points a to c).
e. Read Property for Tax Income (determined based on business logic with data provided in points a to c).

3. Domestic vs Foreign Classification
To handle Domestic vs Foreign income classification, I instantiate two clsIncome when the workbook opens and called them clsForIncome and clsDomIncome. I then continue to create various functions in the modules to populate the properties by first checking whether income is domestic or foreign and set the properties accordingly.

Another approach I thought of was giving domestic and foreign properties to clsIncomeFI but that would mean I will have to create this type of property throughout my various other income objects just seems very redundant to me. Alternatively was to first create a class object called clsSource then the object model will be like clsSource.clsIncome.clsIncomeFI for fixed interest income object model.

4. Populating Object Properties
Another issue I'm not sure about is where should I populate the properties of the classes? In the attached example I created an Init function in a standard module that would populate the properties but came across various examples that shows the populating properties procedure being called within the class object themselves. I think this makes sense as it removes the object from having to know what the outside world is doing so to speak, we just need to provide the parameters that it needs and class takes it in and populates its properties with it. The problem with my example is if I call an populating properties procedure within the class object itself, how is it to know which income Source it should look for to populate its properties?

Hoping someone out there is keen to take this question on.

Many thanks in advance
Ronniel Allan Castanito
Ronniel Allan Castanito
1 Solution

I've not enough knowledge about the investment stuff but I can tell you something about classes.

There is not "the" best design which is possible, you can reach the goal you want with different designs.
What I would do here is: If you do not really need separated classes you should not create more than needed. For example, if you need to only identify which type of calculation the class should use you could simply create a Public Enum in the class like:

Public Enum EnmInvestmentType
End Enum

Open in new window

Now you can insert a new property "InvestmentType" which could be filled like this:

Private prv_InvestmentType As EnmInvestmentType

Property Get InvestmentType() As EnmInvestmentType
    InvestmentType = prv_InvestmentType
End Property
Property Let InvestmentType(intInvestmentType As EnmInvestmentType)
    prv_InvestmentType = intInvestmentType
End Property

Open in new window

Now you can separate the type inside and outside the class in a verbose form so that you can read in the code which type is actually needed. This would go into one subclass, for example "clsInvestment".

In the main class you can collect all the objects of the subclass in one collection:

Private prv_colInvestmentObjects As Collection

Public Sub AddInvestmentObject(objInvestment As clsInvestment)
    If prv_colInvestment Is Nothing Then Set prv_colInvestment = New Collection
    prv_colInvestment.Add objInvestment
End Sub

Open in new window

In the end you would have a collection which can contain any investment object you want. Each investment object could also have a property with the worksheet cell address where the data can be found (let's say "DataAddress"). Also a property if it is the first or last in range to check for the opening and closing balance for example.

Now you can simply use a loop through the collection to find all the objects you need:

Public Function SumOfInvestmentType(intInvestmentType As EnmInvestmentType, strSheet as String) As Currency
    Dim objInvestment As clsInvestment
    Dim curSum As Currency
    For Each objInvestment In prv_colInvestment
        With objInvestment
        If .InvestmentType = intInvestmentType Then
            curSum = curSum + Worksheets(strSheet).Range(.DataAddress).Value
        End If
    SumOfInvestmentType = curSum
End Function

Open in new window

This is of course only a very simplified example which should only show the way, you would need to add tests if the cell is empty or contains a number, you could also branch into different private functions in the class for different investment types to calculate the result in different ways.

This model would only need two classes forever, you can always expand it with further types by simply adding a new row into the enumeration and insert the logic to calculate it into the loop. There you can also use a Select Case statement to decide between the types.

Another more complicate but also more flexible way is to use an interface class. You can simply create an interface class like any other class module, usually it is named beginning with an "I" and then the class name like "IMyInterface". It is in fact a simple class module. What makes it to an interface is the use. First, an interface usually does only contain a description, that means, it contains for example a Public Sub but inside the sub no code. That defines the interface.
Now you can add i.e. three different class modules which normally needs three different calculations but they all would need some similar interface between them and a main class. You can add the interface class here by using "Implements IMyInterface" at each of the class module start (after "Option Explicit"). If you try to compile now the compiler would throw an error and says that the interface functions were not defined in your class module. You must go to the left combobox in the VBA editor and choose the interface name and then add all elements from the right combobox - your interface definition is complete. You can simply copy and paste the inserted functions into the two other class modules and add the "Implements" row.

The trick is now: You can instantiate objects a,b,c with the three different classes but you can also add an "objMyInterface As IMyInterface" which is now a common interface to all of your three different classes. You could add all three objects to a collection and you don't need to check anymore for an object type to calculate them. That is done by the interface:

Dim objA As clsA
Dim objB As clsB
Dim objC As clsC
Dim objMyInterface As IMyInterface
Dim colObjects as Collection
Dim curSum As Currency

Set colObjects = New Collection

' Fill the objects with data here...

With colObjects
    .Add objA, "A"
    .Add objB, "B"
    .Add objC, "C"
End With

For Each objMyInterface In colObjects
    With objMyInterface
        curSum = curSum + .CalculateSum   ' If there is a function "CalculateSum" in the interface
    End With

Open in new window

The objects in the collection are all different, but the loop uses a "Set objMyInterface = objX" with the "For Each". That means, objMyInterface now has only the properties, subs, functions available which are defined in the interface, it cannot see for example "MethodA" in class "A", "MethodB" in class "B" or "MethodC" in class "C". But as all the classes has an own function for "CalculateSum" they all can have a different method to calculate it, they only need to return the correct value.

What makes this approach flexible is that the main methods don't need to know of which type a class is, you can simply add a class "D" with the same interface and the loop would give the result without any change.

A third trick with objects which is seldom used is the use of events. You can define a main class which contains an own event:

Public Event OutputEvent()

Open in new window

Then you can define in the sub classes:

Private WithEvents objParent As clsMain

Open in new window

Now you can use the left dropdown field in the VBA editor to choose "objParent" and you will find your event in the right dropdown "OutputEvent". If you choose that you will get a new Sub inserted which is your event procedure (if you are familiar with Access you will know what that means). This procedure is automatically executed if the event in the main class will be raised. So if each of the subclasses have for example an output cell in a certain sheet defined you can use that to output your result with an event.

What you need in the main class is a Public Sub like "Public Sub RaiseOutputEvent" which looks like this:

Public Sub RaiseOutputEvent()
    RaiseEvent OutputEvent
End Sub

Open in new window

Now if you use the main class anywhere in your code where you want to calculate the values you can simply use:


Open in new window

Now anywhere in your code where these subclasses "live" at the moment (for example in a collection or in a simple object defined in the same sub or in a module level object or global object) they will be triggered to calculate and output their result into their defined output cell. You don't need an interface, a type or a loop, they all would execute the needed code and output it. Easy, isn't it? (And an event can of course have additional parameters, you can add as many events as you want, so you can also trigger only specified type of objects and so on.)

You see, that were only three completely different approaches to solve a calculation, I think there are also other methods, it's only up to your fantasy. I hope this little demonstration has shown you the real power of class modules.

A hint for enumerations at the end: They are a little buggy at least in Access, I'm not sure if you will find the same problems in Excel, but here's what can happen and the workaround:

1. You should ALWAYS save your work before trying to create an Enumeration. It is possible that VBA crashes when you wrote the line and press enter. If it was crashed, load your project again and do the same again, normally the second try always works.

2. Enumerations are sometimes "forgotten" by VBA. You compile and the compiler throws an error that a enum value could not be found. Solution: Simply go to any enumeration in your application you want, go to the definition line, enter a space in the name, leave the line (error is shown), go back and remove the space. That will force VBA to refresh the namespace and your compiler works again. This can happen at any time when you compile your code, not very often. Simply do it again, it will always repair this issue.

Enums are too good to not use them because of these issues, you should use them whereever possible, it makes your code more readable and more compiler friendly because you don't need to use literals or values like "True,False" etc., you have verbose code which tells you exactly what happens here.


That's an excellent comment by Christian.  I will add that enumeratied types aren't self-validating.  You can use any integer numeric value in place of the enumerated type and the compiler will accept it. If you are preparing this framework for other developers, you might want to consider this limitation.  If it is a concern, you can tweak the

Property Let InvestmentType(intInvestmentType As EnmInvestmentType)
  'validate enum value
  Select Case intInvestmentType
    Case enmInvType_FixedInterest, enmInvType_MoneyMarket, enmInvType_Other
        prv_InvestmentType = intInvestmentType
    Case Else
        Error 555   'just an example error number since E is the fifth letter
  End Select
End Property

Open in new window

Ronniel Allan CastanitoIT ManagerAuthor Commented:
Thanks very much experts. Sorry about the late reply. To be honest, I didn't expect an answer.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now