Excel VBA Classes – high speed low drag but with costs

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.

%d bloggers like this: