Conditional Compile in VBA

In Microsoft Excel and Access, when coding in Visual Basic for Applications (VBA) we can use conditional compile to create multiple versions of our compiled code. This is extremely useful when we want to create different versions for different platforms (e.g. 32 bit or 63 bit … or windows vs Mac). We can also use…

How to Create a Custom Visual Basic Editor Toolbar

A good workman always knows his tools and in this tutorial I show you how to create your own custom toolbars in the Visual Basic Editor. It’s a free beginner level tutorial and I use Microsoft Excel 2016. The video uses MS Excel 2016 but the same steps are used for MS Excel 2010, Excel…

Dynamic Named Ranges in Excel

A great way of making your macros and code more robust is to use dynamic named ranges. These automatically adjust and resize as the underlying data changes and I use them whenever I can. In this beginner level tutorial we start with a completely blank spreadsheet. We build a number of lists within a worksheet…

How to Open the Visual Basic Editor in Excel

In Microsoft Excel, when we want to do some coding in Visual Basic for Applications (VBA) we first need to open up the Visual Basic Editor (VBE). There are three key methods I tend to use to do this: Using the Developer Ribbon Tab; Using a shortcut key combination (Alt + F11); If we have…

Techniques to Remember Vast Amounts of Stuff!

A short while back I finished the UK Actuarial exams and more recently I have been mentoring other Actuarial students.  One of the key exams they have problems with is CA1.  Partly this is due to the change in exam technique from earlier exams but another big reason why it’s so difficult is the sheer…

Three ways to copy and paste worksheets using VBA

A very common task for VBA coding and macros is to copy a worksheet, rename and update it in some way.  For example if you have an inputs and output worksheet for a given calculation that stores input assumptions and variables.  You might want to perform sensitivity analysis or stress tests on your calculations using…

Interest Rate and Bond Definitions

Before we really get into the meat of any posts I think it’s a good idea to start with some basic definitions and notation around interest rates. I have chosen some conventions which I think are clear and avoid confusion and in most cases consistent with other literature I have come across (though not all).…

Hello World and MsgBox in VBA

A traditional first programming exercise is the “Hello World” program and this is what we are going to cover in this post.  In the process we are going to take a look at how to use a message box in VBA to display some information to the user, how to configure these message boxes in…

Inheritance by construction in VBA

There is no built in method for class inheritance in VBA – only interface inheritance (via implementing an interface).  Even if we could inherit from another class a general good programming rule is to prefer interface inheritance and use construction (and delegation) to achieve the behaviour we want in our classes … but what does…

Making your VBA code more flexible with interfaces

Interfaces are essential to object orientated programming – especially when we start to use design patterns. They allow programs to be flexible and easily extended without huge changes to the code base. They can be a tricky concept to get your head around though and I often find they are best explained with examples. Throughout…