Home

Best practice for VBA/Macros in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-207409%22%20slang%3D%22en-US%22%3EBest%20practice%20for%20VBA%2FMacros%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207409%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20just%20starting%20a%20project%20to%20review%20complex%20spreadsheets%20that%20contain%20a%20lot%20of%20VBA%20and%20Macros.%20Is%20there%20some%20best%20practice%20around%20on%20this%3F%26nbsp%3B%20A%20lot%20of%20what%20people%26nbsp%3Bare%20using%20is%20old%2C%20unsupported%20and%20indeed%20untested.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-207409%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207513%22%20slang%3D%22en-US%22%3ERe%3A%20Best%20practice%20for%20VBA%2FMacros%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207513%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20really%20depends%20on%20what%20you're%20looking%20at.%26nbsp%3B%20If%20you%20are%20pretty%20familiar%20with%20VBA%20you%20can%20just%20look%20at%20the%20code%20and%20it%20will%20%22speak%20to%20you%22.%26nbsp%3B%20It's%20always%20good%20to%20comment%20code.%26nbsp%3B%20I%20can't%20tell%20you%20how%20much%20time%20it's%20saved%20me%20when%20reviewing%20past%20projects.%26nbsp%3B%20It's%20a%20pain%20to%20do%2C%20but%20you'll%20be%20thankful%20later.%26nbsp%3B%20If%20you%20supply%20some%20of%20the%20code%20I'll%20be%20happy%20to%20review%20a%20bit%20of%20it%20for%20you.%26nbsp%3B%20You%20can%20pretty%20much%20tell%20if%20someone%20really%20knew%20what%20they%20were%20doing%20at%20a%20glance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-866652%22%20slang%3D%22en-US%22%3ERe%3A%20Best%20practice%20for%20VBA%2FMacros%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-866652%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EThe%20best%20practice%20is%20to%20maintain%20the%20mindset%20that%20VBA%20is%20a%20powerful%20object%20oriented%20programming%20language%2C%20not%20just%20a%20macro%20environment.%3CBR%20%2F%3E-%20Choose%20meaningful%20variable%2C%20resource%20and%20method%20names.%3CBR%20%2F%3E-%20Declare%20variables%20and%20resources%20at%20the%20start%20of%20any%20methods.%3CBR%20%2F%3E-%20Release%2Fclose%20any%20resources%20at%20the%20end%20of%20your%20methods.%3CBR%20%2F%3E-%20Always%20use%20Option%20Explicit.%3CBR%20%2F%3E-%20Indent%20your%20code.%3CBR%20%2F%3E-%20Comment%20your%20code.%3CBR%20%2F%3E-%20And%20always%20think%20before%20you%20start%20coding.%3CBR%20%2F%3E%3CBR%20%2F%3ERefactoring%20someone%20else's%20code%20is%20always%20a%20frustrating%20journey%20but%20the%20satisfaction%20you%20get%20when%20everything%20works%20the%20way%20you%20need%20it%20to%20is%20really%20rewarding.%20Enjoy%20%3Athumbs_up%3A%3C%2FLINGO-BODY%3E
Martyn Rowlands
Occasional Visitor

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 Replies

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.

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
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies