Learning to use OOP in VBA Excel

Posted on 2012-08-24
Last Modified: 2012-09-08
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
Question by:RiCzN
    LVL 24

    Accepted 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.


    LVL 44

    Expert Comment

    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


    Author Comment

    Thanks very much experts. Sorry about the late reply. To be honest, I didn't expect an answer.


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now