How to code with VBA and Macros in Excel

01 Introduction to coding with VBA and Macros

Excel is a powerful tool. Even the basic functions and formulas can be utilised to produce some truly incredible works. However there are limitations to what you can accomplish when you solely rely on formulas and there are times when you need to go beyond the surface of what Excel can do. Using code will allow you to fully unleash the power of Excel and give you access to functionality which formulas cannot. For example

• Loops

          o Without coding, you create a formula for each cell you want to calculate against. If the workbook has a lot of cells, this can create a large file size and slow Excel down as it recalculates all the formulas each time anything changes. With VBA you can create code for a generic formula and loop through the data using this code instead.

          o Looping through the data allows you to detect specific values and act accordingly. For example you can check if a list contains a certain number of repeated values and produce a separate list of these

• External programs

          o You can create emails and word documents using data entered in the spreadsheet.

          o You can reference the operating system functions including creating files and folders, dictionary objects, user ids, etc

• Interactive spreadsheets

          o You can build forms and create buttons which can load in data from external sources, run reports, store data etc

Coding in Excel

There is a huge amount you can achieve with a small bit of coding and it is entirely possible to transform Excel into something unrecognisable as a spreadsheet. However the caveat is that it requires a slightly different way of thinking to achieve this and unfortunately not everyone is able to think in the required manner (which is why companies like Xlteq exist, to help you achieve what you want without you breaking into a sweat). Of course you won’t know how good you are until you give it a go so the following a basic example of how to think like a coder.

There are three basic skills required to be able to code.

1. Being able to break down a process into its smallest parts.
2. Recognising points of failure
3. Logical organisation (using conditional statements)

Breaking down the process

For example, let’s look at the process to make a cup of tea. If you asked someone how to do this then you might get a 5-stage reply:

1. Put teabag onto cup
2. Add hot water
3. Leave for 3 minutes
4. Remove teabag
5. Add milk

Now the above might be a typical “human” response but were we to break this down into steps suitable for a computer to understand (as much as it could understand about making a cup of tea) then this would fall far short. In fact you could end up with well over 100 steps. For example the following provides an expanded list for just point 1 above (and there are a lot more, but lets keep this simple for now)

1. Go to cupboard
2. Open cupboard
3. Get cup from cupboard
4. Put cup on kitchen worktop
5. Go to tea bag caddy
6. Get tea bag from caddy
7. Put tea bag into cup

Already we have expanded the original point 1 into 7 and even this makes several assumptions including that we only want to make one cup of tea, we only have one type of tea to choose, etc. However, no matter what we have left out, the point to remember is that, as a human, we can understand general instructions and are able to interpret them. With computers, they require specific instructions which they will precisely follow (even if wrong).

Points of failure

The second coding skill, and possibly the hardest to get correct first time, is to take into consideration anything which could go wrong. Just taking point 1 (“Go to cupboard”) above, how many things could go wrong? Lets put some of them down:

1. You can’t find the cupboard (maybe you are not in the kitchen or even in the same house).
2. The cupboard doesn’t exist (perhaps it has been removed for redecorating)
3. You can’t see the cupboard (maybe someone has blindfolded you)
4. The path to the cupboard is blocked (there could be a lion in the kitchen)
5. You can’t move (perhaps someone has tied you to the table)
6. You can’t reach the cupboard (as you a lying on the floor)

So there are 2 points to notice from the above list.

1. There are a lot of things which can go wrong, which is why this part of coding can be difficult, as there are so many things to consider we can miss something.
2. Whilst the chance of some the above happening is very small, we still need to consider what to do if they do happen. The consequences of not doing so will mean, at best, the computer program will stop with an error or, at worst, it will continue and cause a small disaster further down the line.

Remember humans can assume and workaround problems based on experience, however a computer needs to be told what to do. No matter how small the chance of a problem arising, it is better to code in a solution rather than risk something going wrong.

Logical Organisation

Having the previous two skill sets will give you a good start in programming, but the ability to combine these two is critical in being able to produce good code. When coding it is good to think in a slightly different way. We need to perceive what we need to do as a set of logical steps which will action one after the other in a systematic and logical manner. One of the simplest ways to demonstrate this is with a flow chart and the following is a basic flow chart for the first step we outlined above.