Speeding up VBA Code using Calculation Mode

One of the most common requests from people writing VBA code in Excel is “how do I speed up my VBA code?”. The Speeding up VBA Code series aims to give you some quick and easy to implement techniques to speed up your code. In this free beginner level Excel tutorial we use Microsoft Excel…

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…

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…

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…

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…

Array Assignment in VBA

  I came across an annoying compiler error (and VBA quirk) the other day.  This is one I have encountered before but never wrote down.  It’s counter-intuitive and this makes it harder to track down.  Consider the below set of code (as an example):

When we try to compile the above code we get…

Option Explicit in VBA

One of the easiest ways to improve your VBA code is to include the statement Option Explicit as the first line of each module within a VBA Project. The statement forces all variables to be declared in some manner before they can be used. It might not sound important to declare all variables and most of…