Excel Macros – Building A Clean Code

Excel macros

When building spreadsheets, functions, macros or any other type of complex excel file, it is easy to focus on simply getting things to work and that is what most of us end up doing. What we often forget is thinking with a longer term perspective. It’s often easy to forget that over time, your needs will change and it is more than likely that you will need to modify your file or macro in the future. Even more complex is a situation where other users need to both use and modify the macro. It’s critical to always remember one thing: In a few months/years, you will probably not remember what your macro was intended to do and how it actually worked (steps, etc).

If that happens, you will have 3 options when macros need to be modified (they almost always do eventually):

-Understanding the current macro (painful and very time consuming)
-Start over from scratch
-Patch based on how you believe the macro works

The third option is the one that is generally chosen, it makes macros less effective, more risky (in terms of errors) and macros that include several layers of patches become very difficult to work in. Good macro builders will generally be efficient but not necessarily be the fastest. Why? Like any type of programmer, it is often not about the fact that a macro works but rather about making sure the code is flexible, easy to understand and change. What are ways it can be done?

Writing Clean Excel VBA Code

1-Methodology: When you start using variables for example, it’s a good idea to always define them at the start of the macro, making it easier to understand
2-Leaving notes!!: This will take you additional minutes but believe me that it will save you even much more. Taking the macro that we have been working in the past few weeks, you can see the code is rather difficult to understand. That is generally the case with macros done by recording. Still, you can add comments simply by putting ” ‘ ” in front of a line. That will make the line green but also mean that no code will try to be executed from that line. You can see an example here:

3-Be Consistent: If you build tens of macros, you will certainly improve as a coder but being consistent in the way that you do things will make it easier not only to build but also modify them in the future.





Please Leave a Comment!
