The Excel VBA Masterclass

Masterclass description

 

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!

 

Masterclass Outline

 

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.

We’ll address:

  • Classes and Objects

  • Instantiating a class

  • Methods, properties, Events

  • Event classes

  • Instantiating an object from a class

  • Classes of classes

 

Userforms

 

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.

We’ll discuss:

  • Design

  • Events, methods, properties

  • Making a userform sizable

  • Splitter bars

  • 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.

We’ll discuss:

  • Measuring performance

  • Break the rules

  • Fast VBA algorithms

  • Micro-optimization

  • 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.