Hello, Excellers it is time for another blog post on #MacroMonday series again. Today I will go through my top 3 reasons to use comments in your Excel VBA Code. This is sometimes also referred to commenting out a block of code.
Top 3 Reason To Use Comments In Your Excel VBA Code.
So, let’s look at my top 3 reasons to use comments or commentary in your Excel VBA code.
When looking at Excel VBA code you may have noticed that some of the code is (usually) in green. This is the comments that the writer of the code has inserted when writing the code. These comments are completely ignored when the code is run. It has no effect on the performance of your code. Any text that follows an apostrophe is considered a comment. So let’s run through my Top 3 reasons to use comments on your code.
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 certainly 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.
It Helps When Testing Your Code.
By converting the 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 be 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 front 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.
Clearer Thought Processes.
Use comments In Your Excel VBA code as you write it can really help to think about what you are writing. By commenting as you go, you think about what you want to achieve. I believe you are more likely to translate this into the code you are writing. You should obviously comment on any changes that you make at a later date also to track development. Comments should be used to describe what you are doing but probably more importantly why you are doing it. They are especially useful with more complex code.
SO, those are my Top 3 reasons for using code comments when writing VBA. Do you use comments when you write your VBA code?
If you want more Excel and VBA tips then sign up to my monthly Newsletter. I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
Merge Multiple Excel Files Into One Sheet says
Thanks for sharing this informative post with us. I just want to add one more point that, In some cases, the actual Cell or Range may not be sufficient to show the information. By adding comments we can provide such information and this will also help your worksheet looks much cleaner as comments will not appear until your user or customer hover on the cell or a range.
IvenBach says
When debugging and getting code to do what you want/testing you can use comments. https://stackoverflow.com/questions/12933279/how-to-comment-and-uncomment-blocks-of-code-in-the-office-vba-editor/23954017#23954017 has a helpful hint on how to add a hotkey to comment/uncomment a block of code.
Follow the rules of :
1.a) Make it Work
1.b) CodeReview/Refactor
2.a) Make it Right
2.b) CodeReview/Refactor
3.a)Make it Fast
3.b) CodeReview/Refactor
With each refactoring (https://en.wikipedia.org/wiki/Code_refactoring) rewrite your code to be self-documenting. Have what is occurring be self evident with good naming. —VBA— ALL code should be well written to the extent it is self-documenting. This is because comments can very easily become outdated and obsolete. Old comments can end up lying as to what code is doing and whether it even needs to be there. *IF* code is self-documenting there is no need for comments to explain *what* the code is doing. What it’s doing should be self evident. Comments may however help explain *why* code is written in that way. Even then they should be succinct and used sparingly. Below is an arbitrarily simple example yet it illustrates the point.
“`vb
Public Sub SimpleExampleOfComentUsage()
Dim foo As Range
Set foo = ActiveSheet.Range(“A1”, “A50”)
foo.Formula = “=row()”
Dim bar As Range
Set bar = ActiveSheet.Range(“C1”, “C50”)
‘bar.Copy
‘foo.PasteSpecial xlPasteValues
bar.Value2 = foo.Value2 ‘copies only values. Equivalent to both lines above
End Sub
“`
The affect of well written code grows as projects become more complex and the code base grows in size.