When writing OOP code in VBA, it’s important to keep a number of things in mind:
- A class can be given a default instance, which makes all its public members usable without needing to create a new instance.
- An interface can very well expose only public
property getaccessors, but no mutator.
- A class
Implementsas many interfaces as needed.
- Events cannot be exposed by an interface.
If you ever exported a class module and examined it in your favorite text editor, you probably noticed these:
Attribute VB_Name = "Class1" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = False Attribute VB_Exposed = False
VB_Name attribute determines the identifier the class will be referred to in code;
VB_GlobalNameSpace makes its members global which is kinda anti-OOP..
VB_Creatable can only be
False in VBA projects, and means that other VBA projects cannot directly create a new instance of that class.
VB_Exposed determines whether other VBA projects can see this class or not.
The one we’re interested in, is
VB_PredeclaredId. If you export a UserForm module, you’ll notice it’s predeclaredId attribute is
True. This is what allows you to work against a form without creating an instance – you’re using the default instance when you do that.. and you shouldn’t.
Finding the cheapest hotel
Here’s a little problem that I’m going to solve in VBA, with full-blown OOP:
A hotel chain operating in Goa wishes to offer room reservation services. They have three hotels in Goa: GreenValley, RedRiver and BlueHills. Each hotel has separate weekday and weekend (Saturday and Sunday) rates. There are special rates for rewards customer as a part of loyalty program.Each hotel has a rating assigned to it.
- GreenValley with a rating of 3 has weekday rates as Rs1100 for regular customer and Rs800 for rewards customer. The weekend rates are 900 for regular customer and 800 for a rewards customer.
- RedRiver with a rating of 4 has weekday rates as Rs1600 for regular customer and Rs1100 for rewards customer. The weekend rates are 600 for regular customer and 500 for a rewards customer.
- BlueHills with a rating of 5 has weekday rates as Rs2200 for regular customer and Rs1000 for rewards customer. The weekend rates are 1500 for regular customer and 400 for a rewards customer.
IMPORTANT: Before you read any further
This exercise isn’t about solving the problem. The problem is rather easy to solve. It’s about managing changes, writing code that can survive changes. Specifications are never carved in stone, they change all the time. Today the hotel chain has 3 hotels, tomorrow they might have 3,500. Today the hotel chain has two types of customers. Tomorrow they might have three; eventually the chain acquires another chain in another country, and then the prices need to be converted between USD and EUR before they can be compared. The foreign hotels might have different statutory holidays, and it wouldn’t matter until the CEO decided that July 4th reservations would be 25% off, but only in the US hotels.
This solution isn’t the one OOP way to do things. It’s a solution; your mileage may vary. There are many, many ways to do this – but a monolithic block of procedural code wouldn’t survive very long with the hectic reality depicted above, would it? Or it would, but then bugs would start appearing, and more changes would have to be made, perhaps introducing new bugs, too. Sounds familiar? Keep reading.
So, let’s say I want to store information about some pricing rule, based on some
DateType and some
CustomerType. I could describe this type as follows (the enums don’t belong to the interface, they’re just public types that were convenient to define there):
Option Explicit Public Enum CustomerType Regular Premium End Enum Public Enum DateType WkDay WkEnd End Enum Public Property Get DateType() As DateType End Property Public Property Get CustomerType() As CustomerType End Property Public Function ToString() As String End Function
Let’s call this interface
In well-designed OOP, one doesn’t design an interface to change. This IPricingRuleInfo interface will change, as soon as the requirements change and we need to expose a new property. But we’re going to use VBA interfaces differently here… just bear with me.
What we’re going to do with this interface, is a façade that the program will be written against, while we hide the implementation details.
The implementation would look like this:
Option Explicit Private Type TInfo DateType As DateType CustomerType As CustomerType End Type Private this As TInfo Implements IPricingRuleInfo Public Property Get CustomerType() As CustomerType CustomerType = this.CustomerType End Property Public Property Let CustomerType(ByVal value As CustomerType) this.CustomerType = value End Property Public Property Get DateType() As DateType DateType = this.DateType End Property Public Property Let DateType(ByVal value As DateType) this.DateType = value End Property Public Property Get Self() As IPricingRuleInfo Set Self = Me End Property Public Function Create(ByVal dtType As DateType, ByVal custType As CustomerType) As IPricingRuleInfo With New PricingRuleInfo .DateType = dtType .CustomerType = custType Set Create = .Self End With End Function Private Property Get IPricingRuleInfo_CustomerType() As CustomerType IPricingRuleInfo_CustomerType = this.CustomerType End Property Private Property Get IPricingRuleInfo_DateType() As DateType IPricingRuleInfo_DateType = this.DateType End Property Private Function IPricingRuleInfo_ToString() As String IPricingRuleInfo_ToString = CStr(this.CustomerType) & ";" & CStr(this.DateType) End Function
Create method: that’s the Factory Method, intended to be used from the default instance. The properties are instance members that really belong to an instance of the class; the implementation also exposes
Property Let accessors, so that
Create can assign the proprerty values of the instance to create and return.
Self getter is a little trick that enables this neat
With New syntax.
The private type helps remove awkward prefixes by legalizing identical field and property names, and if the class’ state ever needs to be serialized, it’s child play.
Any code that works with a PricingRuleInfo instance will have access to its setters and default instance. But the client code wouldn’t do that: the client code works with the IPricingRuleInfo interface, and know nothing of a default instance, a factory method, or
Property Let members: it only sees CustomerType and DateType read-only values, and a ToString method that concatenates them into a string.
And now we can have an
IPricingRule interface like this:
Option Explicit Public Property Get RuleInfo() As IPricingRuleInfo End Property Public Function Evaluate(ByVal info As IPricingRuleInfo) As Currency End Function
And then we can have as many implementations as we like – here, a simple one called FixedAmountPricingRule, that takes an amount at creation, encapsulates it, and then uses it to return a fixed amount when evaluating the rule:
Option Explicit Private Type TRule RuleInfo As IPricingRuleInfo Amount As Currency End Type Private this As TRule Implements IPricingRule Private Property Get IPricingRule_RuleInfo() As IPricingRuleInfo Set IPricingRule_RuleInfo = this.RuleInfo End Property Private Function IPricingRule_Evaluate(ByVal info As IPricingRuleInfo) As Currency IPricingRule_Evaluate = this.Amount End Function Public Property Get RuleInfo() As IPricingRuleInfo Set RuleInfo = this.RuleInfo End Property Public Property Set RuleInfo(ByVal value As IPricingRuleInfo) Set this.RuleInfo = value End Property Public Property Get Amount() As Currency Amount = this.Amount End Property Public Property Let Amount(ByVal value As Currency) this.Amount = value End Property Public Property Get Self() As IPricingRule Set Self = Me End Property Public Function Create(ByVal info As IPricingRuleInfo, ByVal value As Currency) As IPricingRule With New FixedAmountPricingRule Set .RuleInfo = info .Amount = value Set Create = .Self End With End Function
Again, we give this class a default instance by setting its
VB_PredeclaredId attribute to
True and re-importing the module into the project.
Next we’ll need an abstraction for hotels – enter
Option Explicit Public Property Get Name() As String End Property Public Property Get Rating() As Byte End Property Public Function CalculatePricing(ByVal info As IPricingRuleInfo) As Currency End Function Public Function GetDateType(ByVal value As Date) As DateType End Function
Notice how the interface exposes nothing of
IPricingRule. The implementation has a dependency on IPricingRule and IPricingRuleInfo, but knows nothing of the concrete types. Here’s the code:
Option Explicit Private Type THotel PricingRules As New Scripting.Dictionary Name As String Rating As Byte End Type Private this As THotel Implements IHotel Public Property Get Name() As String Name = this.Name End Property Public Property Let Name(ByVal value As String) this.Name = value End Property Public Property Get Rating() As Byte Rating = this.Rating End Property Public Property Let Rating(ByVal value As Byte) this.Rating = value End Property Public Property Get Self() As IHotel Set Self = Me End Property Public Function Create(ByVal hotelName As String, ByVal stars As Byte, Optional ByVal rules As Collection = Nothing) As StandardHotel Dim rule As IPricingRule With New StandardHotel .Name = hotelName .Rating = stars If Not rules Is Nothing Then For Each rule In rules .AddPricingRule rule Next End If Set Create = .Self End With End Function Public Sub AddPricingRule(ByVal rule As IPricingRule) this.PricingRules.Add rule.RuleInfo.ToString, rule End Sub Private Function IHotel_CalculatePricing(ByVal info As IPricingRuleInfo) As Currency Dim rule As IPricingRule Set rule = this.PricingRules(info.ToString) IHotel_CalculatePricing = rule.Evaluate(info) End Function Private Function IHotel_GetDateType(ByVal value As Date) As DateType IHotel_GetDateType = IIf(Weekday(value, vbMonday) <= 5, WkDay, WkEnd) End Function Private Property Get IHotel_Name() As String IHotel_Name = this.Name End Property Private Property Get IHotel_Rating() As Byte IHotel_Rating = this.Rating End Property
GetDateType function: it allows a given
IHotel implementation to come up with funky creative ways to determine the
DateType for a given date value.
Also interesting, the
AddPricingRule procedure, which isn’t exposed by the
IHotel interface, but that adds pricing rules to the encapsulated dictionary of pricing rules; given an
IPricingRuleInfo instance, we can now calculate the price by evaluating the rule.
HotelFinder class is just an object that encapsulates the logic to find the cheapest hotel, given two dates and a
Option Explicit Private Type TFinder Hotels As Collection End Type Private this As TFinder Public Property Get Hotels() As Collection Set Hotels = this.Hotels End Property Public Function FindCheapestHotel(ByVal fromDate As Date, ByVal toDate As Date, ByVal custType As CustomerType) As String Dim place As IHotel Dim checkedDate As Date Dim cheapestAmount As Currency Dim cheapestHotel As IHotel Dim hotelTotal As Currency For Each place In this.Hotels hotelTotal = 0 For checkedDate = fromDate To toDate Dim info As IPricingRuleInfo Set info = PricingRuleInfo.Create(place.GetDateType(checkedDate), custType) hotelTotal = hotelTotal + place.CalculatePricing(info) Next If cheapestAmount = 0 Or hotelTotal < cheapestAmount Then cheapestAmount = hotelTotal Set cheapestHotel = place ElseIf hotelTotal = cheapestAmount And cheapestHotel.Rating > place.Rating Then 'same price, but higher rating; higher rating gets precedence Set cheapestHotel = place End If Debug.Print place.Name, Format(hotelTotal, "$#,##0.00") Next FindCheapestHotel = cheapestHotel.Name End Function Private Sub Class_Initialize() Set this.Hotels = New Collection End Sub Private Sub Class_Terminate() Set this.Hotels = Nothing End Sub
So, we iterate a collection of hotels, evaluate the stay at each one (output the amount to the debug pane), and return the name of the cheapest hotel.
At the top of the call stack lies a procedure that creates an instance of that
HotelFinder, populates its
Hotels collection, and ouputs the result of the
FindCheapestHotel function. This is where we reap the benefits of OOP: initializing the hotels reads pretty much exactly like reading the specs.
Option Explicit Public Sub Test(ByVal checkin As Date, ByVal checkout As Date, ByVal custType As CustomerType) Dim finder As New HotelFinder InitializeHotels finder Debug.Print finder.FindCheapestHotel(checkin, checkout, custType) End Sub Private Sub InitializeHotels(ByVal finder As HotelFinder) With StandardHotel.Create("Green Valley", 3) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Premium), 800) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Premium), 800) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Regular), 1100) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Regular), 900) finder.Hotels.Add .Self End With With StandardHotel.Create("Red River", 4) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Premium), 1100) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Premium), 500) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Regular), 1600) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Regular), 600) finder.Hotels.Add .Self End With With StandardHotel.Create("Blue Hills", 5) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Premium), 1000) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Premium), 400) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Regular), 2200) .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Regular), 1500) finder.Hotels.Add .Self End With End Sub
And we get output:
Test Now, Now + 3, Premium Green Valley $3,200.00 Red River $4,400.00 Blue Hills $4,000.00 Green Valley
Is that over-engineered? As I said above, most definitely. But then, how would a Java, C#, or VB.NET solution look like? Not much different, save a PricingStrategyFactoryFactory class for the Java code of course! The point, again, was an exercise in writing code resistant to change, not just solving a problem. Now when the specs change and we need a new pricing rule that grants 20% off on the first Tuesday of every second month, we don’t need to change any code except for the code that initializes the hotels: we just implement the new functionality, without changing code that already works: that’s the Open/Closed Principle at play. In fact, I tried to depict all of SOLID in this code – I hope I did something like that.