Let’s kick things off with a quote – “To understand how things end, you must first understand how they begin”.
That’s pretty much what this section is all about. I’ll give you a brief overview of what Object Oriented Programming (OOP) is and why it’s a great model to use. Don’t skip over this module, it’s vital to understand the core principles of VBA programming.
Is OOP A Programming Language?
OOP can be described as a programming language model. It is not a language itself, but more so a type/style of language. Visual Basic for Applications (VBA) is a programming language that adheres to the OOP model. This puts focus on working with Objects rather than simply carrying out sequential actions.
Each Object can hold information (but only information that is relevant to it) through the use of Properties. Furthermore, Properties of an Object can be controlled or influenced by programming Methods.
I know this may sound like i’m talking in dothraki (Game of Thrones anyone?), but these terms (Object, method, property) are a key concept in VBA.
Objects, Methods & Properties
Consider this – a bicycle, in the real world, is an Object or a tangible ‘thing’ that we can interact with (see, touch, hear etc). Let’s say we like the bicycle but would rather the frame was green instead of blue. How could we fix this in the real world? We would get our paint brush out and channel our inner Picasso to change it’s color to what we want.
So you could say we took our Object (the bicycle) and applied a Method (painted it) it to change it’s Property (color) to green.
Let’s review some more ways we could interact with our Bicycle Object.
|Method (Action)||Property (What)|
|Ride it||to change its||Location|
|Inflate Tyres||to change its||Tyre Pressure|
|Add carbon pedals||to reduce its||Weight|
So you can see that we can take certain actions (or methods) to influence the bicycle (and it’s properties). It’s worth nothing, that not all actions available to us can be used on the bicycle. For example, we could eat (Method) a sandwich (Object), but we can’t eat (Method) a bicycle (Object).
OOP Structure in Excel VBA
Now you’re probably wondering, wtf is this? There are no bicycles in Excel…. Whilst that is true, it’s important to understand how these link together in principle.
Let’s see how this translates into Excel & VBA. The table below shows some examples of each type, but its far from an exhaustive list.
|Objects (Interact With)||Methods (Actions)||Properties (What)|
In VBA you’ll see objects methods & properties separated into code via periods, for example:
In a similar way, VBA limits which Methods and Properties can be changed for each Object. For example you can Activate (Method) Cell C1 (Object) but can’t Open (Method) Cell C1 (Object).
The Object Browser
In truth, there are many Excel Objects, with thousands of methods and millions of properties. Therefore, there is no point going through all of them 1-by-1. All of these can be accessed via the Object Browser which can be viewed by pressing F2 on your keyboard within the Visual Basic Editor.
As you can see above, I have selected the ‘ChartObject’ and can then view all the available Methods & Properties shown as ‘Members’. In conclusion, if all else fails.. and you don’t know the correct syntax to use, just google it.