Top 5 Tips For Coding Excel VBA Macros

VBA, Visual Basic for Applications, is the programming language that Excel understands and although Excel provides an easy to follow macro wizard, using VBA enables you ultimate control in ensuring the macro performs exactly as you want it to. A macro is simply a set of instructions and is usually created by letting Excel record the series of instructions that you want it to later remember and perform automatically. Here are top five tops for coding Excel VBA macros.

Always have a spreadsheet you can play with!

Unfortunately the undo function doesn't work with macros so, while you are initially building it, I would recommend that you do so on a version of your spreadsheet you are happy to delete later on. Consider it a first draft and a way to ensure that you test your macro before letting it potentially weak havoc on your final spreadsheet.

Use pre generated code as your guide

A big time saver and a great way to reduce the chances of making mistakes, use the macro recorder to record you performing a series of similar steps that you want your coded macro to perform. Then, view the code generated and use this as your guide to structuring your VBA code. This can help you in your learning process with VBA but also save time in deciding the best way to structure your VBA code.

To start recording your macro click on the 'developer' tab and then 'record macro'. This will bring up a dialogue box asking you to name your macro. As soon as you click 'OK' Excel will bring recording everything you do as a macro.
Macro Record window

When you have completed your macro you need to stop it recording which you do by clicking ' Stop recording'. Next to see the VBA code that you have generated just click on the 'Visual Basic' button.
Stop recording button

Here the macro just asks Excel to select Cell A1, to write Acuity Training in it and then to select Cell A2.
Visual Basic editor window

To avoid errors due to misspelling, always code in lower case letters.

If you have spelt everything correctly, the necessary words will automatically get capitalised which means that if you find nothing is getting capitalised, you have made some spelling mistakes. A great way to avoid the potentially long winded process of debugging later on.

Use the Immediate Window

The immediate window enables you to test statements during the debugging process and is a great tool to see immediate results from your code. You can open it using the shortcut Alt+F11 and then Ctrl+G. It allows you to check the results of your code line by line.

In the example below we are checking what is in cell A1, which we put Acuity Training into earlier in the article with our macro.
VBA immediate window

Break your code up into small chunks

The process of breaking code into small chunks is called modularization. It is a great way to ensure that you can test and debug parts of the code in isolation and enables you to reuse parts of the code easily.

The great thing about using VBA to create macros is that you do not need to be a programmer in order to master the process. VBA is commonly used in Excel to automate repetitive tasks but more importantly, it opens the door to more possibilities than what the user interface offers. It means that you do not have to only rely on the capabilities that Excel generally hosts but can instead create your own.

Bio - Hollie Miller

Hollie provides Microsoft VBA training for Acuity Training. In her spare time she loves to go to the gym and dress making.

One response to "Top 5 Tips For Coding Excel VBA Macros"

  1. vcoolio says:

    Hello Hollie,

    I was just wondering if you could clarify a part of the following line of code that I have in a macro. I have received conflicting explanations on this so I would be very interested in your opinion.

    ActiveCell.EntireRow.Copy WS1.Range("A" & Rows.Count).End(3)(2)

    Is the section of the code, .End(3)(2), a 'Last' function referring to the last cell in the last column? Or, is it just equivalent to .End(xlUp).Offset(1, 0)?

    Thanks in advance for your advice.
    Cheers,
    vcoolio.

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)
Ultimate Suite for Microsoft Excel
Merge two tables
Combine Sheets
Merge Duplicates
Consolidate Sheets
Copy Sheets
Merge Cells
Vlookup Wizard