June 6, 2011 Leave a comment
Look – let’s be honest here – anyone doing programming in this day and age has at some point been exposed to object based and/or object-oriented programming. In the words of Jack and Stan – Nuff said!
So let’s recap. VBA allows three types of variable definitions:
Intrinsic – these are the usual suspects – Strings, Integers etc. Have scope depending on where declared. If you don’t declare a variable as a specific type it becomes a Variant wasting 32 bits of space plus added overhead.
UDT or User Defined Types - a collection of variables which can also contain arrays. Must be declared/defined at the module level – then used as a variable definition later on in your code. Used just as an intrinsic type, multiple variables can be declared using your UDT.
Class definitions – a template that contain three elements – Properties, Methods and Events. Where have you seen those before…
All this can be looked up – it’s the reason for using a class versus any other type that I want to discuss.
Intrinsic types are the building blocks and are pretty much self explanatory. UDT’s are a nice way to create a collection of intrinsic variables and because of that are very helpful.
Classes not only provide a collection of intrinsic variables but also allow code to be written inside the class definition that deals with the data inside the class. That is what can make a class very handy to have.
I’ve used classes in a couple of ways in my coding. One is a way to keep – think of a scratch pad on the side. I’m dating myself here – but remember back in the day doing a math quiz or test? You had scratch paper on the side to allow figuring etc. of a problem but the answer was on a different sheet. Classes can be used as that – plug some data in, do some manipulation and get an answer. Each object that gets declared with a single class definition can obviously have unique and different data – but the manipulation of that data remains a constant.
Second way I use classes is due to the reuse factor. For example I have a utility I wrote as a class – it provides computer name, if the instance (now that we are in the virtual world) is a server or workstation etc. I can and do plug that class into other projects as warranted – makes my life easier.
But here is my all time favorite reason for using classes versus just regular code modules – the Friend keyword. This harkens back to C and C++ concepts – a variable, function etc. should have the minimum amount of visibility as possible. Private locks things down – and Public exposes for all to see. But Friend is a pre-Java Interface construct. The visibility is throughout the project BUT nowhere else. In other words a class function can’t be inadvertently called by some other code running on the same machine. And since a lot of my VBA coding is being done for PeopleSoft nVision reports that are run unattended on a server – that provides me with a more comfortable feeling.
Classes are higher in costs – frankly I find myself trying to get further and further away from them. But they are a valuable part of your toolkit and well worth your time to investigate and use them.