VBA+OOP: What, When, Why

As I’m writing a series of articles about a full-blown OOP Battleship game, and generally speaking keep babbling about OOP in VBA all the time, it occurred to me that I might have failed to clearly address when OOP is a good thing in VBA.

OOP is a paradigm, which entails a specific way of thinking about code. Functional Programming (FP) is another paradigm, which entails another different way of thinking about code. Procedural Programming is also a paradigm – one where code is essentially a sequence of executable statements. Each paradigm has its pros and cons; each paradigm has value, a set of problems that are particularly well-adapted to it, …and its flock of religious zealots that swear they saw the Truth and that their way is The One True Way: don’t believe everything you read on the Internet – think of one (doesn’t matter which) as a hammer, another as a screwdriver, and the other as a shovel.

Don’t be on the “Team Hammer!” or “Team Screwdriver!”, or “Team Shovel!” – the whole (sometimes heated) debate around OOP vs FP is a false dichotomy. Different tools work best for different jobs.

So the first question you need to ask yourself is…

What are you using VBA for?


If you’re merely scripting Excel automation, you very likely don’t need OOP. An object-oriented approach to scripting makes no sense, feels bloated, and way, way overkill. Don’t go there. Instead, a possible approach to cleaner code could be to write one macro per module: have a Public procedure at the top (your “entry point”), at a high abstraction level so it’s easy to tell at a glance everything it does – then have all the Private procedures it calls underneath, listed in the order they’re invoked, so that the module/macro essentially unfolds like a story, with the high-level bird’s eye view at the top, and the low-level gory details at the bottom.

The concept at play here is abstraction levels – see abstraction is one of the pillars of OOP, but it’s not inherently OOP. Abstraction is a very good thing to have in plain procedural code too!

Procedural Programming isn’t inherently bad, nor evil. Well-written procedural code at the right abstraction level is a pleasure to read, and when you think in terms of functions (i.e. inputs -> output) rather than “steps” or “instructions”, then you can write pure functions – and pure functions can (and probably should) be unit-tested, too.

If you’ve ever written a User-Defined Function that a worksheet invokes, you’ve likely written a pure function: it takes input, and produces output without accessing or altering any other state. If you’ve done that, congratulations, you’ve learned the fundamental building block of the Functional Programming paradigm! ..then again, pure functions aren’t inherently FP.

The vast majority of VBA code written, falls in this category. It would likely be toxic to try to squeeze OOP into such code; I’ll even say that OOP is flat-out the wrong approach here. However, an FP-like approach isn’t necessarily a bad idea… although, VBA clearly wasn’t designed with Functional Programming in mind, so you’ll hit the language’s limitations very early in the process… but it can’t hurt to design your script avoiding side-effecting functions and proliferating global state.

Framework/Toolbox Code

Somewhere in-between the script and the full-blown application, there’s this type of VBA project that you write for yourself as some kind of “toolbox” with all kinds of useful code that you often carry around and pretty much systematically import into every one of your new VBA projects.

This, in my opinion, is where OOP really shines the brightest in VBA: it doesn’t matter if it’s procedural programming code consuming these objects – it’s OOP nonetheless. As much as the Excel object model itself is made of objects, and couldn’t care less if it’s procedural or object-oriented code consuming it.

We could be talking about a fully-reusable ProgressIndicator class, some polymorphic Logger tool that the consuming code can configure as needed to log to the debugger, some text file, or a database, or a set of custom data type classes – a Stack, or an ArrayList wrapper, or a File class that wraps file I/O operations and maybe some Scripting.FileSystemObject functionality, or something else: you get the idea.

Full-Blown Applications

If you’re seeing VBA as a document-hosted VB6 (it pretty much literally is) that can do everything a VB6 program can do, then you’re looking at something else entirely – and the problems you’re solving are in a completely different realm: you’re not automating spreadsheets anymore: you’re writing a CRUD application to automate or facilitate data entry into your ERP system, or you’re maintaining a set of support tables in some corporate database, …likely, something a programmer would look at and ask “hey why are you doing this in VBA?”

“Because I can” is a perfectly acceptable answer here, although “because I have to” is often more likely. Regardless, it doesn’t matter: well-written VBA code is better than poorly-written VB.NET or C# code (or Java, or anything else): if you’re writing VB.NET and it says “Imports Microsoft.VisualBasic” at the top of your modules/classes, then you’re likely not writing idiomatic .NET code, you’re writing glorified VB6 using modern syntax, in a modern IDE.

Bad code is on the programmer, not the language.

When you’re making an application, procedural programming can be actively harmful – you’re building a complex system, using a paradigm that doesn’t scale well. FP would be an option for the bulk of the application logic, but then again VBA wasn’t made for Functional Programming. An Object-Oriented approach seems the most sensible option here.

But what about RAD?

Rapid Application Development software, such as Microsoft Access, blurs the lines: now you’re given a framework to write event-driven code (which does stem from OOP), but using object-oriented patterns (e.g. MVC) can feel like you’re working against that framework… which is never a good sign. The best approach here would be to embrace the framework, and to extract as much of the logic as possible into small/specialized, self-contained components that can be individually tested.

OOP Battleship Part 1: The Patterns


About OOP

If you’ve been following this blog, you know that VBA is indeed very capable of “real” object-oriented code, regardless of what “real programmers” say about the language.

So far I’ve presented snippets illustrating patterns, and tiny example projects – the main reason I haven’t posted recently is, I’ve been busy writing a VBA project that would illustrate everything, from factory methods to unit testing and Model-View-Controller architecture. In this blog series, you will discover not only that VBA code can be very elegant code, but also why you would want to take your skills up to the next level, and write object-oriented code.

You may have been writing VBA code for well over a decade already, and never felt the need or saw a reason to write your code in class modules. Indeed, you can write code that works – OOP will not change that. At one point or another you may find yourself thinking “well that’s nice, but I’ll never need to do any of this” – and you very well might be completely right. Think of OOP as another tool in your toolbox. OOP isn’t for throw-away code or small, simple projects; OOP is for large projects that need to scale and be maintained over the years – projects you would show to a programmer in your IT department and they’d go “but why are you doing this in Excel/VBA?” …and of course the reason is “because that’s the only tool you guys are letting me use!” – for these projects (and they exist, and they’re mission-critical in every business that have them!), the structure and architecture of the code is more important than its implementation details; being easy to extend is more important than everything else: these projects are the projects that will benefit the most from OOP.

Object-Oriented VBA code is much easier to port to another language than procedural VBA code, especially with proper unit test coverage – which simply can’t be done with traditional, procedural code. In fact, OOP VBA code reads very, very much like plain VB.NET, the only difference being the syntactic differences between the two languages. If your mission-critical VBA project ever falls in the hands of your IT department, they will be extremely grateful (not to mention utterly surprised) to see its components neatly identified, responsibilities clearly separated, and specifications beautifully documented in a thorough test suite.

Is OOP necessary to make a working Battleship game in VBA? Of course not. But taking this Battleship game as a fun metaphor for some business-critical complex application, OOP makes it much easier to make the game work with the human player on Grid1 just as well as on Grid2, or making it work with an AI player on both Grid1 and Grid2, or making different difficulty levels / strategies for the AI player to use, or trashing the entire Excel-based UI and making the game work in Word, Access, or PowerPoint, or all of the above… with minimal, inconsequential changes to the existing code.

Any of the above “changing requirements” could easily be a nightmare, even with the cleanest-written procedural code. As we explore this project, you’ll see how adhering to the SOLID OOP principles makes extending the game so much easier.

But before we dive into the details, let’s review the patterns at play.

PredeclaredId / default instance

I’ve covered this before, but here’s a refresher. I find myself using this trick so often, that I’ve got a StaticClass.cls class module readily available to import in any project under my C:\Dev\VBA folder. The file looks like this:

MultiUse = -1 'True
Attribute VB_Name = "StaticClass1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

The VB_PredeclaredId = True attribute is the important part. With this attribute on, the class now has a default instance. What’s critical is to avoid storing instance state in this default instance (see UserForm1.Show). But for pure functions such as factory methods, it’s golden.

Under the hood, every single object is given an ID: when you New up a class, you create a new object ID. When a class has this attribute set to True, VBA automatically pre-declares an ID for an object that’s named after the class itself.


Perhaps the single most powerful (yet underused) feature of VBA: the Implements keyword makes an instance of a class able to present different public interfaces to its clients. This allows us to have public mutators on a class, and yet only expose public accessors to client code that is written against an interface. More on that below.

Think of an interface as a 110V power outlet.


It doesn’t care what it’s powering, so long as it fulfills the contract: any device that operates on a standard North American 110V power outlet can be plugged into it, and it’s just going to work, regardless of whether it’s a laptop, a desktop, a monitor, or a hairdryer.

An interface is a contract: it says “anything that implements this interface must have a method that does {thing}”, without any restrictions on how that {thing} is actually implemented: you can swap implementations at any given time, and the program will happily work with that implementation, unaware and uncaring of the implementation details.

This is a very powerful tool, enabling polymorphism – one of the 4 pillars of OOP. But strictly speaking, every single object exposes an interface: its public members are its interface – what the outside world sees of them. When you make a class implement an interface, you allow that class to be accessed through that interface.

Say you want to model the concept of a grid coordinate. You’ll want to have X and Y properties, …but will you want to expose Public Property Let members for these values? The GridCoord class can very well allow it, and then the IGridCoord interface can just as well deny it, making code written against IGridCoord only able to read the values: being able to make something read-only through an interface is a very desirable thing – it’s the closest we can get to immutable types in VBA.

In VBA you make an interface by adding a class module that includes stubs for the public members you want to have on that interface. For example, this is the entire code for the IPlayer interface module:

Option Explicit

Public Enum PlayerType
End Enum

'@Description("Gets the player's grid/state.")
Public Property Get PlayGrid() As PlayerGrid
End Property

'@Description("Identifies the player class implementation.")
Public Property Get PlayerType() As PlayerType
End Property

'@Description("Attempts to make a hit on the enemy grid.")
Public Function Play(ByVal enemyGrid As PlayerGrid) As IGridCoord
End Function

'@Description("Places specified ship on game grid.")
Public Sub PlaceShip(ByVal currentShip As IShip)
End Sub

Anything that says Implements IPlayer will be required (by the VBA compiler) to implement these members – be it a HumanPlayer or a AIPlayer.

Here’s the a part of the actual implementation for the AIPlayer:

Private Sub IPlayer_PlaceShip(ByVal currentShip As IShip)
this.Strategy.PlaceShip this.PlayGrid, currentShip
End Sub

Private Function IPlayer_Play(ByVal enemyGrid As PlayerGrid) As IGridCoord
Set IPlayer_Play = this.Strategy.Play(enemyGrid)
End Function

The HumanPlayer class does something completely different (i.e. it does nothing / lets the view drive what the player does), but as far as the game is concerned, both are perfectly acceptable IPlayer implementations.

Factory Method

VBA doesn’t let you parameterize the initialization of a class. You need to first create an instance, then initialize it. With a factory method on the default instance (see above) of a class, you can write a parameterized Create function that creates the object, initializes it, and returns the instance ready to use:

Dim position As IGridCoord
Set position = GridCoord.Create(4, 2)

Because the sole purpose of this function is to create an instance of a class, it’s effectively a factory method: “factory” is a very useful OOP pattern. There are several ways to implement a factory, including making a class whose sole responsibility is to create instances of another object. When that class implements an interface that creates an instance of a class that implements another interface, we’re looking at an abstract factory – but we’re not going to need that much abstraction here: in most cases a simple factory method is all we need, at least in this project.

Public Function Create(ByVal xPosition As Long, ByVal yPosition As Long) As IGridCoord
With New GridCoord
.X = xPosition
.Y = yPosition
Set Create = .Self
End With
End Function

Public Property Get Self() As IGridCoord
Set Self = Me
End Property

The GridCoord class exposes Property Let members for both the X and Y properties, but the IGridCoord interface only exposes Property Get accessors for them – if we consistently write the client code against the “abstract” interface (as opposed to coding against the “concrete” GridCoord class), then we effectively get a read-only object, which is nice because it makes the intent of the code quite explicit.


This architectural pattern is extremely widespread and very well known and documented: the model is essentially our game data, the game state – the players, their respective grids, the ships on these grids, the contents of each grid cell. The view is the component that’s responsible for presenting the model to the user, implementing commands it receives from the controller, and exposing events that the controller can handle. The controller is the central piece that coordinates everything: it’s the component that tells the view that a new game should begin; it’s also the component that knows what to do when the view says “hey just so you know, the user just interacted with cell F7”.

So the controller knows about the model and the view, the view knows about the model, and the model knows nothing about no view or controller: it’s just data.


The adapter pattern is, in this case, implemented as a layer of abstraction between the controller and the view, that allows the former to interact with anything that implements the interfaces that are required of the latter. In other words, the controller is blissfully unaware whether the view is an Excel.Worksheet, a MSForms.Userform, a PowerPoint.Slide, or whatever: as long as it respects the contract expected by the controller, it can be the “view”.

Different view implementations will have their own public interface, which may or may not be compatible with what the controller needs to work with: quite possibly, an electronic device you plug into a 110V outlet, would be fried if it took the 110V directly. So we use an adapter to conform to the expected interface:


Or you may have taken your laptop to Europe, and need to plug it into some funny-looking 220V outlet: an adapter is needed to take one interface and make it compatible with another. This is quite literally exactly what the adapter pattern does: as long as it implements the IViewCommands interface, we can make the controller talk to it.