asked on

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.

Scenario:

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
Rick
Sample-Object.xlsm

ASKER CERTIFIED SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

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

Ricky Nguyen

ASKER

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

Cheers
Rick