Best practice for VBA/Macros in Excel

[Martyn Rowlands]

Hi Everyone,

I am just starting a project to review complex spreadsheets that contain a lot of VBA and Macros. Is there some best practice around on this?  A lot of what people are using is old, unsupported and indeed untested.  

[#2]

It really depends on what you're looking at.  If you are pretty familiar with VBA you can just look at the code and it will "speak to you".  It's always good to comment code.  I can't tell you how much time it's saved me when reviewing past projects.  It's a pain to do, but you'll be thankful later.  If you supply some of the code I'll be happy to review a bit of it for you.  You can pretty much tell if someone really knew what they were doing at a glance.

[#3]

Hi,
The best practice is to maintain the mindset that VBA is a powerful object oriented programming language, not just a macro environment.
- Choose meaningful variable, resource and method names.
- Declare variables and resources at the start of any methods.
- Release/close any resources at the end of your methods.
- Always use Option Explicit.
- Indent your code.
- Comment your code.
- And always think before you start coding.

Refactoring someone else's code is always a frustrating journey but the satisfaction you get when everything works the way you need it to is really rewarding. Enjoy :thumbs_up: