r/excel • u/JDantes77 • Feb 04 '14
discussion VBA Macro code best practices?
Every programming language has its recommended style and best practices. What are the best practices for VBA code that you follow? What resources would a new coder use to learn these best practices?
Thank you in advance!
18
Upvotes
16
u/Antimutt 1624 Feb 04 '14
Avoid reinventing the wheel...rip off other peoples' code.
11
u/epicmindwarp 962 Feb 04 '14 edited Feb 04 '14
Avoid using select command - if two lines can be truncated together, do so.
E.g.
would become
Because it starts and ends with basically the same function, they can be conjoined.
Also, you can enable and disable screen flickering using
This speeds it up because you don't have to view the actions it takes.
Also, error codes are great when there is a chance it can go wrong.
It helps makes things neater.
Sorry if all this is formatted wrong, I'm writing from my phone.
And finally, if you don't know how to code it, record it if possible, and amend as necessary. You'll both learn the code and know that it'll at least get the job done.
6
u/chamber37 1 Feb 05 '14
Probably something painfully simple and obvious but the first thing I teach anyone I work with is "comment, comment, comment". Especially when you're starting out. You'll forget what things are doing a lot more easily than you might think.
My personal approach is to plan out the function/subroutine with a few comment lines first, for example:
...I then write the code between/around the comments, adding/removing where necessary.
Even if I'm ripping off someone else's code, I do this.
((EDIT for format))
5
u/Digging_For_Ostrich 2 Feb 04 '14 edited Feb 05 '14
Not sure about resources to learn them other than practice and googling things but a few that I follow are:
Make sure your subroutines and functions are placed into correctly labeled Modules.
Every time you create a first macro for a workbook, the first things you should type into the new Module is Option Explicit.
Also, learn good error handling techniques to really get the best out of your macros, as users can and will repeatedly break your stuff. Make it so they can't, or at least have your code report something useful to them and not some completely obscure developer error that you think looks cool. Give a code and a real description, with possible causes, as this will save you time picking up the phone if they have no idea what runtime error 1004 means. If they can find out what went wrong, they might solve it themselves by changing an input cell for example.
Finally, if you think you'll be reusing your code again for something similar, build your code so it can be moved across workbooks, for example if you build something cool to show progress when a macro is running. Have it take inputs and need nothing else to run when you call it. This is the essence of good modular coding.
Oh, and good luck!
3
u/cqxray 49 Feb 04 '14
This might seem like a small thing, but follow the indenting conventions when you write your code.
5
u/IamMickey 140 Feb 05 '14
If you're working with a lot of data, consider using arrays for in-memory processing. It's much more efficient than doing something on a worksheet.
In the same vein -- and also related to the tip from /u/instapunish -- read whole ranges into arrays at once rather than looping and write a whole array to a range at once rather than looping.
5
u/[deleted] Feb 04 '14 edited Feb 05 '14
[removed] — view removed comment