It is only a matter of time before Excel powerusers discover that Excel has a hidden gem which can boost productivity enormously: The macro recorder. Once they have started recording macro’s they soon find themselves editing the code generated by the recorder so it becomes more flexible. Seasoned VBA editors then start writing their own procedures in VBA. If you are such a seasoned VBA coder, but haven’t touched the more advanced things like class modules and optimising your code for speed, then this masterclass is for you!
Classes in VBA
For many VBA programmers, the concept of a class module is a mystery, even though this feature has been available in Visual Basic for many years – it was added to Excel beginning with Excel 97.
A class module is a special type of VBA module that you can insert into a VBA project. Basically, a class module enables the programmer to create a new object class. As you should know, programming Excel really boils down to manipulating objects. A class module allows you to create new objects, along with corresponding properties, methods, and events.
Classes and Objects
Instantiating a class
Methods, properties, Events
Instantiating an object from a class
Classes of classes
If your VBA project needs to show more than a simple message to or get more than a single input from a user, the best way to handle that is by creating your own dialogs, which VBA calls Userforms.
Events, methods, properties
Making a userform sizable
Using in an Object oriented way
Efficient coding in VBA (2 sessions, one theoretic one and one hands-on lab)
A common complaint about VBA in general, and particularly procedures that automate Excel, is poor performance. While there is some truth in that, say when compared to C++, it is very often due to poorly structured or poorly written code. This is probably because VBA makes it very easy to write code that works, but quite difficult to write code that works fast. As a general rule, the speed of a well-optimized procedure can often be an order of magnitude faster than the original code, and improvements of two orders of magnitude are not uncommon.
Break the rules
Fast VBA algorithms
Efficiently exchanging information with Excel
Bring your computer as we’ll be handing out some assignments for you to practise with and a challenge to optimise a routine.