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.

See also

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



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard