Core contributor to the Rubberduck project, co-author of Microsoft Access in a Sharepoint World (2011), Professional Access 2013 Development (2013), and Effective SQL: 61 Specific Ways to Write Better SQL (2016), 10-times Microsoft Access MVP award recipient (2009-2019), Ben Clothier wrote a paper about class modules and OOP that makes a great on-topic addition to this blog. Enjoy!
Introduction
There are popular misconceptions surrounding VBA and object-oriented programming (OOP), usually in 2 forms:
- VBA isn’t really OOP, so you can’t really use OOP principles with VBA
- OOP makes things too complicated; procedural programming is all you need anyway
Both are incorrect because OOP is not a language feature but rather a principle of how we should design our code. In modern programming languages and IDE, there are features that makes it easier to apply & enforce the principles. In the end, it is still up to us, the programmers, to actually apply & enforce the principle. Thus, the language of choice has no bearing on whether you can apply the principles of OOP or not. If you are still skeptical, consider that the C programming language predated the development of OOP but there is a demonstration on writing OOP code in C.
The 2nd objection is commonly raised because when looking at the OOP code, it can seem frustrating because it refers to several other objects and you find yourselves looking at more files in order to see what a program does. Coming from a procedural mindset, that can feel like you’re dealing with several layers of lasagna. That does require a change in how you perceive the code.
That is the goal of this article, to help you discover how applying OOP principle can help you write better code, not just for VBA but for any programming language. If you’ve worked on a complex project, you might have had an episode where when you fixed a bug in one spot, 2 new bugs appeared in 2 unrelated places. Surely, you’d find that quite frustrating, taking out all the fun in the programming. Procedural design enables you to solve business problems quickly so that you can get on with other stuff. However, what if it’s so successful, that they come back for more; asking you for more features? How many changes do you have to make? With procedural programming, the upkeep is cumulative; first few feature requests are easy and put in action quickly. Next few, it takes more time and more tweaking. Some more, then it feels a bit harder and harder. But coding should not be like that! Adding a new feature should not scale on an exponential scale! That is what the OOP promises; by keeping a clean codebase, it is easy to describe the new feature and integrate it into the codebase with minimum change.
In fact, most programmers nowadays should be emphasizing writing refactor-friendly code. What do we mean by refactor-friendly? Basically, it is a codebase that is easy to change because you are able to change only pieces that actually needs to change and no more than that. That is very difficult to do in a purely procedural system. For long time, refactoring has not been something on average VBA developers’ mind because there were no tools to refactor VBA. Rubberduck exists to provide those tools. To get the most from refactoring, we do need to raise our level of code writing and apply good design to our VBA codebase.
The other important aspect to learn is that we want to make the wrong code look obviously and blatantly wrong. I highly encourage you to also read Joel Spolsky’s article on that subject. His article deals with the Hungarian notations, but we want to go beyond just the naming conventions. Taking up on OOP principles can significantly help us with making wrong code look wrong which means it becomes easier for us to fix the code. You’ve probably had to deal with a giant hundreds-line procedure with the great wall of declarations and deeply nested code and had the thrill of debugging it and cursing while your minor change cascades into something catastrophic. Well, there’s a better way!
The article assumes that you are familiar with VBA and procedural programming but otherwise have never or rarely used classes or interfaces. It further assumes that you might have had heard of object-oriented programming but otherwise are unfamiliar with the design and use of such. Also, it assumes that you are familiar and comfortable with using code-behinds and events in the document modules. (e.g. Excel’s workbook or worksheet, Access’ form or reports, Word’s document, etc.) We will build up on the class design and eventually apply OOP principles in designing our classes. To reinforce the concepts, we will do a build-up starting with familiar approach and transforming it into a clean codebase that is very refactor-friendly. The benefit is that you end up with a codebase that is easy to read, understand and maintain. Because this assumes you are familiar with procedural procedure (e.g. writing small functions or routines that perform a complex task by breaking it down into small steps), we need to provide a good transition from procedural mindset to object-oriented mindset. For that reason, we will take a route around the town instead of a direct route. I believe the indirect route will be beneficial in seeing what we would accomplish with a clean object-oriented codebase. This is not intended to be an exhaustive treatise but rather provide enough of fundamentals for you to see the advantages of the OOP principles in a VBA codebase.
We will start with creating custom types, and doing work with it, then use it as a basis for our first class.
Creating your custom types
You may have already used a user-defined type (UDT), which is a convenient way to create a structure of closely related properties together. You may have used it before especially if you’ve ever had to use certain API functions via the Declare statements. Let’s start with a Person UDT. We can create a new standard module and define a UDT within the module:
Public Type Person
FirstName As String
LastName As String
BirthDate As Date
End Type
'figure 2-1: a Person UDT
The UDT provides us with 3 members that tells us something about a Person; namely the first & last name and the birth date. Obviously, we can have more but we want the example to stay simple. The calling code to use a Person UDT could look something like this:
Public Sub Test()
Dim p1 As Person
Dim p2 As Person
p1.FirstName = "John"
p1.LastName = "Smith"
p1.BirthDate = #1970-01-01#
p2.FirstName = "Jane"
p2.LastName = "Doe"
p2.BirthDate = #1970-01-01#
Debug.Print VarPtr(p1), VarPtr(p2)
End Sub
'figure 2-2: testing code for using a Person UDT
This should demonstrate clearly that with a UDT, we could create several “instances”, which are independent of one other. Our setting of p2.FirstName does not affect the p1.FirstName. Note the last line printing out the VarPtr(p1) and VarPtr(p2). This prints out the variable’s memory address, which demonstrates that the p1 and p2 variables resides in a different region of memory and thus do not share anything. As an exercise, you can check the VarPtr for members of p1 and p2. For example, you could look at VarPtr(p1.FirstName) or VarPtr(p2.LastName) and compare to their respective counterpart.
In typical cases, we might want to have a collection of persons so that we can work with them in bulk or something similar. You might have done this but using a worksheet or a database table as the backing data structure. There is one crucial difference between a UDT and a database table or worksheet; the UDT is always ethereal, resides in the memory whereas the same data saved into a worksheet, a database table, or XML file are persisted and requires a specific method to read or write data to those source.
One thing about a UDT is that it cannot have any methods. We’ll start with creating a new person. In the above code example, we declared a Person variable for each object we needed, but we can do better than that. Let’s have a procedure that returns a new Person instead, as demonstrated below:
Public Function Create( _
FirstName As String, _
LastName As String, _
BirthDate As Date _
) As Person
Dim NewPerson As Person
NewPerson.FirstName = FirstName
NewPerson.LastName = LastName
NewPerson.BirthDate = BirthDate
Create = NewPerson
End Function
'figure 2-3: a Create function for a Person UDT
Now the calling code looks like this:
Public Sub Test()
Dim p1 As Person
Dim p2 As Person
p1 = Create("John", "Smith", #1970-01-01#)
p2 = Create("Jane", "Doe", #1970-01-01#)
Debug.Print VarPtr(p1), VarPtr(p2)
End Sub
'figure 2-4: calling code using a Create function to create Person UDTs
Much more compact code, yes? More importantly, when we read the code, it is easy to understand what it is doing because we separate the mechanics of the creation from the current context which just needs something created without knowing the particular details in the act of creation.
But it doesn’t have to be just about the creation. Let’s say we want to provide name change. Perhaps because Ms. Doe got married and is now Mrs. Holly. We could then write a new function to help us:
Public Function ChangeLastName( _
Person As Person, _
NewLastName As String _
) As String
ChangeLastName = Person.LastName
Person.LastName = NewLastName
End Function
'figure 2-5: a ChangeLastName function to mutate a Person UDT
Thus, we could have our calling code do the name change:
Public Sub Test()
Dim p1 As Person
Dim p2 As Person
p1 = Create("John", "Smith", #1970-01-01#)
p2 = Create("Jane", "Doe", #1970-01-01#)
Debug.Print VarPtr(p1), VarPtr(p2)
Dim OldName As String
OldName = ChangeLastName(p2, "Holly")
Debug.Print OldName, p2.LastName
Debug.Print VarPtr(p1), VarPtr(p2)
End Sub
'figure 2-6: calling code using ChangeLastName on a Person UDT.
The calling code demonstrates that when we change the last name for Ms. Doe to Mrs. Holly, the variable p2 is still the same; only its content has changed. If you are now wondering why we couldn’t have just assigned the UDT member directly instead of calling ChangeLastName, that’s exactly one of the problems we face with using UDTs:
p2.LastName = "Holly" 'Overwriting the original "Doe" entry
'figure 2-7: bypassing the ChangeLastName function by writing directly to the UDT member.
There is no way for us to control the access. VBA does not allow us to create an UDT that cannot be edited once created. That is often referred to being “immutable”. So, when we pass around UDT, we are always trusting that everyone will follow the same convention we build around the type. However, as human beings, we excel at being inconsistent and forgetful, so it’s too easy to fail to follow the convention, especially because the compiler won’t care whether you do a p2.LastName = "Holly" or ChangeLastName(p2, "Holly"). Both are legal syntax; but we don’t want it to be that way.
If you are wondering why we should want to control access and require use of a ChangeLastName instead, consider that in a typical business process, nothing stays the same for very long. What may have been true yesterday may be no longer true today. To stick to our example, we could suppose that we have a requirement that name change must be approved and is restricted to only those who are 18 years or older, based on the birth date. If we directly set the LastName property, there’s no guarantee that the checks have been enforced. We can write down a sticky note “Use ChangeLastName”, but that won’t be enforced by the compiler. One key to writing a clean code base is to have the compiler do as much work as possible in telling you that some certain action is verboten.
To recap what we’ve learned so far. We’ve seen how we can define a custom user-defined type to group a set of closely related properties. We saw that the UDT can be instantiated multiple times, enabling us to juggle more than one instances of same type at the same time. We wrote some procedures that interacts with the UDT to compensate for the shortcomings of the UDT such as making creation easy or managing some sensitive change such as changing person’s last name which may have additional constraints beyond just the code itself. We saw that an UDT does not really do a good job of managing the access to properties, which requires us to follow conventions that are not enforced by the compiler, which can make the coding around an UDT highly prone to errors or omissions.
Creating our first class module
With all that information we’ve learned, we now have enough working knowledge to start creating a class. Let’s get started by creating a new class module. We’ll call it “Person”. We create a class module via the VBIDE’s toolbar and choosing the Class Module command.
The very first thing we want to do with our first class is to define the private data it needs to have to work correctly. We could start with nothing but public fields, like this:
Public FirstName As String
Public LastName As String
Public BirthDate As Date
'figure 3-2: initial class design with fields only.
The class module is probably still unnamed and may have a default name of Class1. To provide it with a name, you can fill in the name via the Properties toolwindow:
However, this is no better than the original UDT we started with in Figure 2‑1. We would still have the same problem with controlling the access. For example, we might not want to allow arbitrary changes to LastName but rather control it via a dedicated ChangeLastName procedure. We could use Property statements instead. If you’ve never used one before, they are a way to provide a procedural access to a member of the data structure, which grants us additional control on how the property may be accessed. We could revise the code accordingly:
Private mFirstName As String
Private mLastName As String
Private mBirthDate As Date
Public Property Get FirstName() As String
FirstName = mFirstName
End Property
Public Property Let FirstName(NewValue As String)
mFirstName = FirstName
End Property
Public Property Get LastName() As String
LastName = mLastName
End Property
Public Property Let LastName(NewValue As String)
mLastName = LastName
End Property
Public Property Get BirthDate() As String
BirthDate = mBirthDate
End Property
Public Property Let BirthDate(NewValue As String)
mBirthDate = BirthDate
End Property
'figure 3-4: a class design using Property statements instead of public fields.
This is still functionally equivalent to the original version of class and the UDT; all members are readable & writable. However, because property is a procedure, we can add additional logic beyond just setting a field. A typical example might be to require validation such as the code in Figure 3‑5:
Public Property Let FirstName(NewValue As String)
If Len(NewValue) = 0 Then
Err.Raise 5, Description:="First name cannot be blank"
Else
mFirstName = NewValue
End If
End Property
'figure 3-5: an example of a property procedure with validation enforced.
Note that we had to define 3 private fields (mFirstName, mLastName, and mBirthDate). We had to use prefixes because we don’t want name collisions with the public-facing properties of same name. However, this is problematic for two reasons:
- All private fields are now sorted together in the IntelliSense, requiring you to type in a “m” to locate the module level field. This becomes annoying when you have other public members that might start with the letter “M” but will now mingle with those various backing fields. That hampers the discoverability of the code.
- Because of intermingling, you can’t tell quickly whether a public member
LastNamehas a backing field or not; you’d need to look in two different places to make that determination. While the examples above have shown us properties that provide data, we could create a get-only property that is calculated. For instance, we can create anAgeproperty that is calculated based off themBirthDatebacking field rather than having its ownmAgebacking field.
We can do better! Let’s use a UDT instead of a bunch of fields. Since we are improving upon the original UDT, we will start with a UDT, but this time we’ll make it Private, and we will only need a single instance of it. We’ll also take the opportunity to make all properties read-only by not providing a Property Let like we did in the original example:
Private Type TPerson
FirstName As String
LastName As String
BirthDate As Date
End Type
Private This As TPerson
Public Property Get FirstName() As String
FirstName = This.FirstName
End Property
Public Property Get LastName() As String
LastName = This.LastName
End Property
Public Property Get BirthDate() As String
BirthDate = This.BirthDate
End Property
'figure 3-6: a class using an UDT as its backing field for several properties.
With this approach, we have only a single module-level variable, called This. This enables us to get a nicely filtered IntelliSense listing only the backing field, which can be now the same name as the public member and this is now much easier to enforce with compiler’s help.
This is obviously an improvement, but we now have no way of setting the data to the This instance. Let’s add a procedure to do just that:
Public Sub FillData( _
FirstName As String, _
LastName As String, _
BirthDate As Date _
)
If Len(This.FirstName) = 0 Then
This.FirstName = FirstName
This.LastName = LastName
This.BirthDate = BirthDate
End If
End Sub
'figure 3-7: a FillData procedure to write to the private data of the class.
Now we can create the persons with this revised calling code:
Public Sub Test()
Dim p1 As Person
Dim p2 As Person
Set p1 = New Person
Set p2 = New Person
p1.FillData "John", "Smith", #1970-01-01#
p2.FillData "Jane", "Doe", #1970-01-01#
Debug.Print VarPtr(p1), VarPtr(p2)
End Sub
'figure 3-8: revised testing code using the Person class.
Hopefully this illustrates how much cleaner the code is. More importantly, because the fields in the class are now private and can be only set via the FillData method, and exposed as read-only, we make it possible to leverage the compiler to help us enforce guarantees about the access to those fields. That becomes important in a more complex class where we need to be able to make safe assumptions about the class’ internal state. We would not have that with an UDT.
We also have a validation check that This.FirstName is not already a zero-length string, and throwing a runtime error to prevent erroneous use of the FillData procedure. However, this is a runtime validation, rather than compile-time validation. We want the compiler to do the work for us.
Can we do that? Absolutely! We will look at how we can achieve this using interfaces next to help us hide the methods.
Controlling access to methods via interfaces
Above, you saw how we could use a class module to protect the internal data structure and thus control the access to the data, which helps us write code that we can verify at the compile time to do the correct thing. However, we still need to deal with the methods themselves. As noted, we needed to create a FillData method to write data to the internal state. As it is, it would expose the procedure to all consumers and there’s nothing preventing them from inappropriately calling it. We can use a runtime validation but the objective of this paper is to convert as much errors we can from run-time to compile-time. So we need to do something about the FillData method. We want to basically hide the FillData member once the instance has been created. How do we do that? With interfaces. What are interfaces in VBA? They’re actually just class modules. VBA does not actually make a semantic distinction between a class and interface. To further muddy the water, all VBA classes also have a default interface – that is, the class itself (i.e. its Public members). To keep it simple, we will say that an interface is basically a VBA class module with no code. Here’s how we will set up our IPerson interface:
Public Property Get FirstName() As String
End Property
Public Property Get LastName() As String
End Property
Public Property Get BirthDate() As String
End Property
Public Function ChangeLastName(NewLastName As String) As String
End Function
'figure 4-1: IPerson interface.
You might note that this has similar properties like we saw in Figure 3‑6, with the addition of a modified version of ChangeLastName we saw in Figure 2‑5. More importantly, the FillData method is not present on the IPerson interface. By itself, it does not do much because there is no implementation for the interface. We will now make the Person class implement the IPerson interface. This is done with the Implements statement:
Implements IPerson
Private Type TPerson
FirstName As String
LastName As String
BirthDate As Date
End Type
Private This As TPerson
Private Property Get IPerson_FirstName() As String
IPerson_FirstName = This.FirstName
End Property
Private Property Get IPerson_LastName() As String
IPerson_LastName = This.LastName
End Property
Private Property Get IPerson_BirthDate() As String
IPerson_BirthDate = This.BirthDate
End Property
Private Function IPerson_ChangeLastName(NewLastName As String) As String
IPerson_ChangeLastName = This.LastName
This.LastName = NewLastName
End Function
Public Sub FillData( _
FirstName As String, _
LastName As String, _
BirthDate As Date _
)
If Len(This.FirstName) = 0 Then
This.FirstName = FirstName
This.LastName = LastName
This.BirthDate = BirthDate
End If
End Sub
'figure 4-2: Person class implementing the IPerson interface.
If you compare the original class in figure 3‑6 with the code above, you should note the following differences:
- The members are now
Privaterather thanPublic. - The members now have the prefix
IPerson_. You might have seen similar setup with event handlers. Obviously this is special in the sense that if you have anIPersonvariable, you are able to access the implementation even though it’sPrivatebecause theIPersoninterface defines the member asPublic(see figure 4‑1). - The
FillDataprocedure is not on theIPersoninterface, but is stillPublicand doesn’t have anIPerson_prefix.
Let’s set up some testing code to demonstrate that we’ve in fact hidden the FillData procedure:
Public Sub Test()
Dim p As Person
Set p = New Person
p.FillData "John", "Doe", #1970-01-01#
Dim i As IPerson
Set i = p 'We can assign a Person to IPerson because of Implements
i.FillData "Invalid", "Invalid", #9999-12-31#
End Sub
'figure 4-3: testing code to demonstrate that FillData on a IPerson variable is disallowed at compile-time.
Once we’ve set up the code above, we should try to compile the code. This should yield a compile-time error, like this:
This is an immense improvement over the original code from the Figure 3‑7, which would only be enforced at run-time, not at compile-time. By writing code that we can get the compiler to aid us in checking, we reduce the likelihood of introducing bugs due to an incorrect use of methods.
However, you might be wondering if we have to create a Person variable, what’s there to stop us from accidentally using a Person variable when we should be using an IPerson variable? Indeed, the testing code in figure 4‑3 is suboptimal. Ideally we would have a method that will provide us with an IPerson variable so that we don’t actually need to create the Person variable at all. Thus, we need to learn about creating a factory, and also learn about separation of concerns.
Factory Design Pattern
We saw how we can use interfaces to hide the methods that shouldn’t be available to consumers but we also saw that it does little good if we create the implementation then cast it in the same routine. We need to be able to separate the creation so that we will get the interface, rather than creating the implementation ourselves. By separating the two concerns, or the scope of work, we achieve cleaner code. To that end, we will need to create a factory. There are few different ways we can create a factory, with their pros and cons. Because this article intends to build up on the lessons we learned, we will start with the most simple possible way to implement a factory.
One way to create a factory in VBA is to create a standard module and treat it like a class. By using a standard module in this manner, we avoid the need to create an instance of the factory itself and keep things straightforward. Let’s create a PersonFactory module:
Public Function Create( _
FirstName As String, _
LastName As String, _
BirthDate As Date _
) As IPerson
Dim NewPerson As Person
Set NewPerson = New Person
NewPerson.FillData FirstName, LastName, BirthDate
Set Create = NewPerson
End Function
'figure 5-1: PersonFactory module.
We can then revise the calling code from figure 4‑3 to look like this:
Public Sub Test()
Dim p As IPerson
Set p = PersonFactory.Create("John", "Doe", #1970-01-01#)
End Sub
'figure 5-2: revised calling code using a PersonFactory module.
Note that we now only have an IPerson variable; we don’t even need to know that it’s the Person class that is the implementation for the variable. More importantly, we are able to hide the members of Person class:
At this point, we’ve successfully hidden the FillData member and the changes we have introduced makes it much easier for us to analyze our codebase and make wrong code look obviously wrong. You may be wondering what’s stopping us from simply creating a new Person variable and casting to it. The answer is actually nothing, really. However, the same answer applies to preventing us from making a Private procedure a Public one or promoting a local variable to a global one: we know to not make a private procedure public because that breaks the encapsulation and makes for more messy codebase. By the same token, we want to become comfortable using abstractions (e.g. the interfaces) as a perfectly normal way to work with classes where we need to hide the implementation details from the consumers.
You might have noticed that in the figure 5-3, we used the syntax PersonFactory.Create(). A common misconception regarding naming of procedures in VBA is that they cannot be the same name when they are Public and in a standard module. Thus, a common approach would be to name the method something like CreatePerson, which is a OK name. However, trying to come up with unique names for everything can quickly become a hassle. However, there is nothing preventing us from creating a Public Create() As IPerson in a PersonFactory module and a Public Create() As IWidget in a WidgetFactory module! By qualifying our calls to the Create() methods with the module name, it immediately becomes obvious what we are using to create our objects and without the overhead of creating yet another class. This ought to illustrate why semantic naming matters much more than using notations which only add more noise without making wrong code look wrong.
As already has been mentioned, there are different ways we can create factories. It could be an actual class that you could allow the use of New on. It could be a member on the predeclared class module. It depends on what you need. The key takeaway here is that by separating the concern of creating objects from the code that consumes them, we are able to make good use of interfaces to hide the members of the implementations.
Conclusion
You’ve learned how to create a class module and apply some of the good design principle including encapsulating and separating the concerns. You’ve also learned how you can use interfaces to control the access to members that shouldn’t be used by the calling code promiscuously. All those combine up to enable us to write a codebase that compiler is able to validate and enforce for us. Having the compiler do the work for us means we have less work when we review and try to understand what the code is doing. You’ve seen that does require more objects. We’ve had to create 2 class modules, 1 standard module and another standard module to do the testing/calling. With the original UDT approach outlined in section 2, it might be possible to do all in one module. A number of VBA developers may feel that keeping everything in one module makes for easier porting of code from one project to another. They may also feel that protecting the internals is easier to do with Private methods. However, as you saw, sometimes that is simply not possible using a UDT which need public methods to provide “API” around it. Also, too many private members within a module usually makes the code untestable and brittle. Finally, using a Worksheet, UserForm, or an Access form as the “class” only encourages highly coupled code that itself cannot be verified at compile-time. It is possible to set up a convention of doing things but if the convention can’t be enforced by the compiler, we would have do additional work in analyzing the code to ensure it is doing what it is intended. We will analyze those issues in a future post.
By applying good OOP design principles, we are able to make it easier on ourselves to write verifiable and testable code. The concern with managing the number of modules and being able to easily refactor code is actually a problem with the IDE, not with the language. For that reason, Rubberduck exists to alleviate those shortcomings and make it easier to apply good OOP design principles to your VBA code.
Rubberduck News 
As someone who only coded in a procedural way for 20+ years and was truly introduced to oop by Mat’s articles, this is a really nice complement to get started. Thanks for sharing. Clear and straightforward.
LikeLiked by 3 people
The author has many awards. I envy him.
I agree with the author that procedural programming is usual for solving modest tasks and OOP is profitable for the developer for the following support.
But the author has not yet mastered OOP development. He owns CLASS-Oriented development – he writes procedures with classes.
The procedure program contains in the code the solution of the task. The OOP does NOT contain solutions in the code.
If it is possible to solve a problem procedurally, it is imperative to solve it procedurally.
OOP comes when the complexity of an algorithmic solution does not suit the developer.
OOP = solution of the problem by the communication of objects.
Interaction of objects is organized, which solves the problem and it is not always possible to predict the solution in advance – because the solution paths found by objects with the help of computer are usually not calculated by human thinking.
Let’s use a paradox – weak human computational thinking creatively controls strong computational thinking.
And when you need many similar objects to solve a problem, you use the ability of the class to multiply objects.
When debugging an OOP program there appear pleasant surprises from new ways of solving the problems found by the program.
And it is not OOP to create classes for working with data that will be stored somewhere.
LikeLike
Thanks for your comments! I’m always learning new things and it’s not surprising if I got things wrong; I still do! That said, the goal of the article is to provide a transition from procedural-based mindset to object-oriented mindset that is easy to follow. I certainly cannot do OOP its proper justice in only one article. In fact, I left out several things in order to keep the article focused. As mentioned toward the end, I intend to follow up with more examples. You cited one example about data storing which is definitely a common mistake people make when starting out with VBA classes, making it a thin wrapper over the data storage. That will be addressed in an upcoming article.
LikeLiked by 2 people
> If it is possible to solve a problem procedurally, it is imperative to solve it procedurally.
Strong disagree. Off the top of my head right now at this moment I can’t think of any problem that cannot be solved procedurally. Or with OOP. Or with FP (though admittedly I haven’t done any FP, just extrapolating here). Some problems have undisputably better solutions with such or such paradigm, that’s a given – but many people have written perfectly-working Battleship games without OOP, be it in Python, JavaScript, or VBA. The problem with OOP in VBA is that unlike, say, a Java newbie writing their first “hello world”, it’s been hammered into the VBA crowd’s head, for *decades*, that class modules are somehow an “advanced” topic that they likely don’t even need to look into… and many don’t… and miss out all the fun. Heck, VBA *itself* is an “advanced” topic that’s discussed as an afterthought… and then there’s only hardly enough room to cover the basics and give a handful of tips: higher-level concepts like abstraction and separation of concerns are rarely even mentioned, even though they are essential for any well-written procedural piece of code.
Writing about the *mechanics* of how OOP is done in VBA, *concretely*, with simple examples, is important and makes a huge difference for many people that DON’T *already* master OOP but are curious and want to experiment and learn things. OOP and SOLID design might *emerge* out of the necessity of making things scalable and testable, but if all one knows about OOP is that classes are involved, it’s not going to happen all by itself.
The more we write about how classes work, what interfaces are, how you can parameterize a factory method in VBA… the less we’re going to read that VBA can’t do OOP, can’t parameterize constructors, can’t leverage polymorphism, etc.
LikeLiked by 2 people
I do not agree either.
Because I had written “if possible, then it is possible,” but the translator replaced it with “imperative” :-).
Regarding “can’t think of any problem that cannot be solved procedurally” – you can google “Academician Ershov cybernetic uncertainty alpha birth.”
I was led to object interaction by the loss of pleasure from programming with increasing complexity of projects – the brain is lazy.
The way out was found in the OOP tradition of SmallTalk, for myself I call it: the interaction of performers.
As usual – I never tire of thanking RubberDuck VBA!
I support your enthusiasm for OOP.
But starting with classes to store data about employees is neither right nor fun.
Are you intellectually prepared to refuse the use of encapsulation violators – setters and getters?
Here is an example of the interaction of executing objects, without using the classes https://inexsu.wordpress.com/2020/02/28/%d0%be%d1%82%d0%bb%d0%b8%d1%87%d0%b8%d1%8f-%d0%be%d0%b1%d1%8a%d0%b5%d0%ba%d1%82%d0%bd%d0%be-%d0%be%d1%80%d0%b8%d0%b5%d0%bd%d1%82%d0%b8%d1%80%d0%be%d0%b2%d0%b0%d0%bd%d0%bd%d0%be%d0%b3%d0%be-%d0%b8/
LikeLiked by 2 people
Great article – I’ve mentioned before I’d like to see a course from start to finish that’s done strictly in VBA OOP. Looking at the OOP Battleship program was like looking at lasagna code (as stated in this article) and very hard for me to follow.
Currently my foray into using classes is mostly just enhanced UDTs.
In other instances I’ve only copied / used other developer’s objects (such as a progress bar) that uses interfaces. Luckily, since that’s just a single object, I was able to understand the workings and enhance it a bit on my own.
I look forward to more articles from Ben Clothier as a good primer and hope to fully grasp VBA OOP and truly transform my own Procedural VBA code!
LikeLike
This is good feedback. As another commenter mentioned, this is more about classes than it is about OOP. A class like Person, implementing an IPerson interface, is a fundamental building block, but it doesn’t stop there – at that point what we’re looking at is essentially a glorified UDT that’s better at encapsulating data. It’s an object, yes, but its purpose is just to structure and hold data – which makes it ideal to cover the mechanics involved; what we can do with classes goes much, much further than that.
The Battleship articles (and the project itself) intentionally kind of skipped over the basics to delve into OOP architectural design patterns, where objects have a purpose other than just holding state: Model/View/Controller are just the names we give to the objects in these architectural roles, to make the role of each object clearer (design patterns are communication devices: every programmer that knows them will instantly recognize the classes and how they work together) – I (or Ben) can’t write about OOP architectural patterns *and* cover the basics of classes and interfaces in the same article, it’s just too much material!
Encapsulation, private UDTs, properties, interfaces, factory methods, abstract factories, they’re like bricks; the architectural patterns are like the manual telling us what bricks to put where.
LikeLike
Well, I definitely look forward to learning how to build more “bricks” and then know how to make those bricks work/talk with one another. Once I fully understand that, I feel confident I can build my own OOP architecture! 🙂
— This is probably beyond the scope of this article, but in one of my recent projects I’m using a class to hold a Task and then a collection of those Tasks. In the standard module where I loop through the collection, I have a local private UDT to hold the properties of the Task class…now the ONLY reason I did this was that before when I was stepping through the code and it referenced a property of the my Task object, the VBE would jump into the class. I then have to step through the Get assignment steps before I could return back to the flow of the program. By using the UDT to hold the properties, I avoided this VBE step-through. Is this just leading to more useless/spaghetti/overhead code?
LikeLike
The road to using UDTs with collections is paved with… exploding bear traps! Collections work infinitely better with objects. As for your question, no it doesn’t – the overhead of a stack frame is ridiculously irrelevant I promise: if your code is experiencing performance issues, the very last place to look would be abstractions and encapsulation. The VBE debugger has F8 for stepping *through*, but also Shift+F8 for stepping *over* – which is what you want to be doing if you want to skip stepping through properties.
LikeLiked by 1 person
Yeah – the UDT was just my crutch from forgetting to use the Shift-F8, I may accidentally step over the code I actually want to step through! 😉 And if I implied that I was using UDT in collection, that was not my intention. I’m using the class object in a collection. In my For each task in task collection, I then reassign the task properties back into a UDT – again only as a crutch in the debugger! 😛
Thanks again for addressing my question! I’m actually in the process of redoing my Task class with Interfaces and Factories derived from my knowledge from this article!
LikeLike
Sorry to post these “trouble shooting” questions here, but I verified this with the Person Class.
When p is set to the class (through the factory), the properties all display the following in the Locals Window:
: BirthDate : : String
Luckily, if you put p.Birthdate in the Watch Window, you do get the data stored – though it would be easier to view in the Locals Window.
Is this part of the encapsulation process in VBA – you won’t be able to view the object properties in the Local Window?
LikeLike
What’s in the locals toolwindow depends is what’s local in the context of the current scope; if you break anywhere inside the class module you should see everything you need to see, with the backing fields neatly wrapped under the `this` node.
But yes, there’s also a bug in the VBE’s locals toolwindow that apparently breaks things when interfaces are involved.. I do work with the immediate pane a lot more than with the locals toolwindow though, might affect how much of an issue I’m making this to be.
LikeLiked by 1 person
oops, looks like the webpage took out the text between the LT and GT signs, the text Locals Window shows:
Object doesn’t support this property or method
LikeLike
You wrote “You may be wondering what’s stopping us from simply creating a new Person variable and casting to it.”.
Out of curiosity: Would there be a possibility to prevent this?
If not in the same project, would there be a possibility to prevent it for consumers using a library we program?
LikeLike
Public classes not being creatable in VBA does have a couple of advantages!
LikeLike
I wasn’t asking regarding creating but casting to a class from an interface instance. 😉
LikeLiked by 1 person
If you can put a class in an `As` clause, the question answers itself, no?
Sure you *can* do that. It breaks the LSP: if code says it’s working with an `ISomething` then any implementation of that interface should work… and that won’t be the case if you cast the interface to some specific class.
Do no, nothing is preventing it, but that type of casting would only be considered as a last resort or something, and definitely with an explanatory comment!
LikeLike
Yes, as Mat was alluding to, if you make a class `PublicNotCreatable`, and put it in a separate project, then you can in fact prevent unwanted creation of such classes except via the factory methods you provide in the project. Note that in this case, the factory itself must be either a standard module or a predeclared class so that you are able to access the factory without creating it. Both factory and the actual class has to reside in the same project, after all.
The article here deliberately didn’t get into library projects and assumes that we’ll have everything in one project, where there is no such boundary to control the object creation. In that case, interface casting and conventional agreement to not create concretes is the best thing you can do in this situation.
LikeLike
Fantastic post, thanks!
I wish this had been my introduction to VBA classes. Instead I cobbled together my initial understanding from a number of posts on Stack Overflow and Chip Pearson’s site, and I struggled for a long time to really *get* what was going on
I am now implementing interfaces based on what I’ve gleaned from you, but I’m sure I will look back on them with embarrassment (much like my early classes!) at some point.
Like an earlier poster, I read through your Battleship posts and the sample code feeling like I was constantly in the wrong layer of the lasagna, but excited by the potential.
I hope you can flesh this post out into a whole series to help bridge the gap from where I am to where you are 🙂
Thanks again
LikeLike
[…] when I said default instances and factory methods (here too) are some kind of fundamental building block, I mean we’re going to be building on top of […]
LikeLike
Mathieu, excellent content like always!
One thing I have always noticed since reading your posts on here is that whenever we open up the Locals Window to view the interface values we always get “”
Why is that?
LikeLike
Hi Cortez, sorry for the (very) late reply – somehow I’m only getting notifications for comments on articles I wrote – belated thanks for the feedback!
The VBE’s “locals” toolwindow is kind of buggy and doesn’t play nice with interfaces. The problem is basically with the type system VBA uses: the locals toolwindow isn’t really equipped to deal with compile-time types vs run-time types, the tooling gets confused about what members to display and basically bails out. Maybe one day RD will manage to tap into the debugger and fix that!
LikeLiked by 1 person
I tried following your examples and have put this in a class called Person:
Option Explicit
Private Type TPerson
FirstName As String
LastName As String
BirthDate As Date
End Type
Private This As TPerson
Public Property Get FirstName() As String
FirstName = This.FirstName
End Property
Public Property Get LastName() As String
LastName = This.LastName
End Property
Public Property Get BirthDate() As String
BirthDate = This.BirthDate
End Property
Public Sub FillData( _
FirstName As String, _
LastName As String, _
BirthDate As Date _
)
If Len(This.FirstName) 0 Then
This.FirstName = FirstName
This.LastName = LastName
This.BirthDate = BirthDate
End If
End Sub
In a standard module, I have this:
Option Explicit
Public Sub Test()
Dim p1 As Person
Dim p2 As Person
Set p1 = New Person
Set p2 = New Person
p1.FillData “John”, “Smith”, #1/1/1970#
p2.FillData “Jane”, “Doe”, #1/1/1970#
Debug.Print VarPtr(p1), VarPtr(p2)
End Sub
When I step into it, it seems Len(This.FirstName) is ALWAYS zero!
Have I put the code in the wrong place?
LikeLike
Hi Dug, sorry I haven’t seen your comment earlier! If I understand correctly, you’re referring to the conditional block in the Person.FillData method; from your Test procedure, I would expect Len(This.FirstName) to be zero (and so for the conditional block to be entered) for the first invocation against the p1 and p2 instances.
Try this:
p1.FillData “John”, “Smith”, #1/1/1970# ‘ <~ expected: Len(this.FirstName)=0
Debug.Print p1.FirstName '<~ expected: "John"
p1.FillData "Jane", "Doe", #1/1/1970# '<~ expected: Len(this.FirstName)=4
Debug.Print p1.FirstName '<~ expected: "John", because FirstName wasn't empty when changed to "Jane".
LikeLike
I’m still trying to get my head around this post and I certainly need some help here. I can’t seem to properly understand the use of the Interface. Initially, the goal was not to expose some members of the class due to the fact that it could raise a run-time error, which we want to avoid (compile errors are preferred). When not exposing a procedure member like a function, the best way is to use an Interface. I get that. But two questions arise:
– Why haven’t you included the FillData signature in the Interface just as you did with the ChangeLastName Function? One could argue that then the Interface might not be implemented in other classes because they don’t need to implement this Function but, wouldn’t this comply with the Interface Segregation Principle (i.e. having several interfaces is preferred over having one)?
– Anyways, let’s stick to the Factory Design, wasn’t the ultimate goal of this post to rely on compile errors? What is the point of having the Factory if it’s still using the FillData method. I can see it throwing a run-time error as soon as we pass in wrong data types.
I’m by no means an expert, I’m trying to understand the concepts you are exposing.
Thank you very much for your posts and the RubberDuck project.
LikeLike
Great questions!
The FillData method is not a part of the IPerson interface because we want the interface to restrict the access to the private members. Had we kept it on the interface, there is nothing stopping you from abusing it to circumvent the validation that would be present in the ChangeLastName method. The figure 4-4 demonstrates that calling it on the IPerson interface is now a compile error. The only thing that can call it is the factory, which we saw later. We unfortunately need to have a FillData method because that is the only way an external object (e.g. factory in this case) can access and modify the internal backing data within the Person class instance. In other languages, it would have been handled by using a parameterized constructor. However, the limitation of COM prevents us from being able to create a class that requires parameters to construct an instance.
I’ve had cases where objects requiring a complex construction logic having another interface such as ICreatePerson, to segregate the logic for creating a new instance of a Person object while still having an class that a factory instance can construct. In this example, however, I wanted to keep it simple by keeping the FillData method on the Person class for the PersonFactory to call. The key takeaway is that when the PersonFactory returns, it returns a IPerson interface, not a Person class, restricting the access to that method.
Which leads to the 2nd question — when I was discussing the compile-error vs. runtime, I was focusing more on controlling the access to the FillData method itself, than whether the FillData member will throw a runtime validation error. The goal is to ensure that FillData will not be called 2nd (or even n-th) time later in its lifetime. Once first name and birth date are entered, they must stay fixed. However, if they can call FillData 2nd time, they can violate the promise that first name and birth date won’t change. By keeping the FillData member off the interface and expecting the consuming code to work with an IPerson interface instead of a Person class, we make it unlikely for a consumer to accidentally call FillData when it shouldn’t.
I hope that help clarifies.
LikeLiked by 2 people
I would add to what Ben said, that it’s hard to wrap one’s head around interfaces – it’s normal to not “get it” at first. But as you use interfaces and start understanding their implications (they’re just another tool in your box), their many uses become more apparent. The alternative to compile-time polymorphism would be late-bound polymorphism: when something is wrong one refuses to compile (let alone to run), and the other blows up at run-time.
The next post will be about unit testing, where compile-time polymorphism through abstract interfaces gets leveraged quite much.
Don’t hesitate to download the example code from any article on this blog, and explore, and tweak, and break, and fix, and extend, and.. play, have fun, learn! Cheers!
LikeLiked by 1 person
Test
LikeLike
bgclothier, I’m the user “enlatierradeoz”.
It’s a bit discouraging that the site isn’t working. I’ve been trying for 8 days in a row to post this answer but the site won’t allow me to. My issues don’t get answered on GitHub either, so it’s a pity because I love the project and your posts but this will be my last attempt. There’s no point in visiting the site if I can’t use it properly and my questions cleared up.
Thanks a lot for your time and explanations! It has taken me a couple of hours to understand your explanation, read through the post again and write this answer. I hope it’s up to yours!
Let me give you some background and why it’s still a bit difficult for me to properly understand some concepts, because I’m afraid I’m still a little (less!) confused. Please, bear with me. [YOU CAN SKIP THE FOLLOWING SECTION AND READ “QUESTIONS”]
I have been using VBA for quite some years and then got attracted by C# and the possibility to create good code. I thought that I could somehow leverage the knowledge of one language and “implement” in another, and all the way around. Half a year ago I discovered that Interfaces could be used in VBA and I thought to myself that this would be even better to reinforce theory and become a better “programmer” (I’m a Mechanical Engineer but I love programming). And then I came across Rubberduck and thought to myself: “you not only can make better applications, but Rubberduck will help you grasp good OOP concepts that you will use in other programming languages!”. I think – maybe others won’t agree – that the beauty of Rubberduck is not only the high value it brings in when programming in VBA but also the high educational component that brings into the community to broaden their (our) minds in a world that has been taken by storm by coding.
I “understand” concepts like delegates, events, abstract classes and interfaces from C#. I know about inheritance and the cornerstones of OOP, what’s more, I “understand” them! The problem is, I understand them individually, not as a whole, i.e. how can I use them at once (if possible) in an application and conforming to SOLID principles (which I’m still trying to understand!). That’s why I’m trying to read all your entries and understand them, to improve my VBA applications (very complex, with lots of forms), my VBA skills and C#. All in all, I want to become a better programmer, BUT I’M IN THE FRUSTRATING STAGE WHERE THE BIG PICTURE IS NOT CLICKING 😦 (I’m not yelling).
QUESTIONS
I think everything makes much more sense now. I must admit that I have been a little bit misled by the “compile-error” thing as if it was the sole purpose alongside encapsulation.
So let me see if I got it correctly, starting from your clarification on figure 4.4. By instantiating the Person class first, we can use FillData as a constructor as we keep the rest of the class members private. Thus, by next using the Interface it is as though we could “hide” what was before exposed by the class (i.e. FillData) and show what had been hidden (like a ON/OFF switch). In short, by not being able to use FillData a second time (thanks to casting Person to IPerson) we achieved the same functionality as that of a Constructor plus having a compile error. The compile error is just when accessing FillData from outside, the potential run-time errors from within FillData code (e.g. passing in wrong data types) will still have to be prevented by using error handling. Am I right?
Then the Factory Design Pattern allows for “hiding” the Person class and avoid the compile error we got when we tried to invoke the constructor a second time (from the constructor).
Could you please confirm this is what you meant through your answer?
LikeLike
Two things spring to mind regarding OO in VBA:
1. I can (probably!) read OO code written in VBA and make sense of it but what gets me is if someone said “write a program that does xyz”, OO would not be my first approach. The problem is not my lack of understanding of the syntax of OO but the thought process, ie the step before actual writing the code takes place. How does one plan to use classes, how many to use, how they interact with each other? Most sources explain how to write but not the thinking behind it all.
2. One main drawback I can see with OO is speed. It is definitely slower using classes. There’s no ifs and buts, so if speed is of the essence, I would not use classes.
LikeLike
Ben, aren’t the 2 lines of the body of Sub ChangeLastName swapped?
LikeLike