Excel VBA Tutorial Part 1 - VBA Code Presentation

When learning to produce VBA code, it is important, from the very start, that you adopt good habits
in terms of code presentation, so that it is easy to see what the purpose of the code is, and how it works.

While you may, at the time you are writing the code, have a clear idea of what it does and how it
works, you need to consider what the code will look like when you return to it in 6 months' time.
Worse still, what if someone else needs to work on your code and has to figure out how it works?

This page discusses comments, code indentation and line breaks
all of which will assist in making your code clearer and easier to interpret.

Comments

The single most important practice for writing clear, decipherable code is to add frequent comments.

Comments are lines in your code which act as notes to yourself or others, to explain what the code means
or what it is doing.
Comments are not executed during the running of the program, so have no impact on the result your macro.
VBA considers any line that starts with an apostraphe (') to be a comment and the Excel VBA editor
highlights these lines by colouring them in green, so you can see, at a glance, that they are comments
and will not be executed.

See the example below, which shows comments used to clarify the details of a simple subroutine:

' Subroutine to search cells A1-A100 of the current active
' sheet, and find the cell containing the supplied string

Sub Find_String(sFindText As String)

Dim i As Integer' Integer used in 'For' loopDim iRowNumber As Integer' Integer to store result in

iRowNumber = 0

' Loop through cells A1-A100 until 'sFindText' is found

For i = 1 To 100If Cells(i, 1).Value = sFindText Then

' A match has been found to the supplied string
' Store the current row number and exit the 'For' Loop
iRowNumber = iExit For

End IfNext i

' Pop up a message box to let the user know if the text
' string has been found, and if so, which row it appears on

Don't worry if you don't understand some of the code in the example above - this will be explained later in this tutorial.
The example has been included simply to show how comments are used to to explain what each section of the code is for.

It is easy to get lazy about adding comments to your code, but it really is worth making the effort.
- the minutes invested in ensuring your code is well commented could save you hours of frustration in the long term.

Code Indentation

Another way of assisting in making your code readable is by adding indentations to it.
VBA code is generally indented inside Functions, Subroutines, If blocks or Loops.
These indented sections enable you to easily see where each block of code starts and ends.
If you look at the code block above (in the previous 'Comments' section), you will see how the indentations highlight
where the 'For' Loop and the 'If' blocks start and begin.

The Excel VBA editor helps you by automatically inserting indentations into certain parts of code,
but you may sometimes need to add further indentations when you are editing your code.

Line Breaks

Your code can also be made more readable and easier to work with by inserting line breaks in the middle
of long lines of code. In VBA, if you are going to split a line up, you need to add a space followed by
an underscore ( _) just before the line break.
This tells the VBA compiler that the current line of code continues on the following line.

The following example shows how simple line breaks can be used to make long lines of code much easier
to read and understand. Consider the following line of code: