Hello and time for a another Macro Monday, welcome if this is your first time, and welcome back if you are a regular vistor. If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.
Today let’s look at my top 3 reasons to use comments in your Excel VBA code.
1. Reminder Of Your Thought Processes.
So, you understand your code now, but what if someone picks up your code a year or two down the line, or you come back to the code in six months, can you really remember exactly the stream of thought you had at the time you wrote the code?. Probably not. I know that I certaintly do not, after all I create a lot of Excel solutions and code created for many different people. I find it good practice to always comment on my code.
2. It Helps When Testing Your Code.
By converting code to comments you can actually use this to test removing an adding pieces of code. For example, by adding an apostrophe character to the beginning of a line of code and hitting Enter, the code will turn green and will now seen by Excel as a comment and not as code. If you have code over multiple lines you will need to add apostrophe’s at the beginning of each line to ensure Excel sees it is commentary. Alternatively you can comment out a whole block of VBA code using this method here.
Here is an example. I have recorded a Macro which turns the text in cells A2:B5 green. You can see when I code using the Macro Recorder I get a lot of extra details in the coding that you may not want. By commenting out pieces, you can test if you really need them, then decide to delete them or save them for a later date.
In this example I have extra coding highlighted below after I created my code using the Excel Macro Recorder.
To see if the Macro is affected by removing this extra code, we can put an apostrophe in from of that line of code.
It does not affect it. So, either I can leave it commented out or remove it altogether from the coding. This time I think I will leave it.
3. Clearer Thought Processes.
Commenting on your code as you write it can really help to think about what you are writing. By commentting as you go, you think about what you want to achieve, then go ahead and are more likely to translate this into the code your are writing.