Publications: Basic rules of VBA programming in Excel

The purpose of this article is to highlight some basic principles of VBA programming for people who just begin writing code for their Excel projects. Unfortunately, it is too easy to write bad code in VBA that would solve a particular problem once, but would not be reliable or maintainable. It takes a bit of effort and a bit of knowledge to create quality VBA automation.

The below 10 rules are the most fundamental ones that we would like to point out:

1. Never access cells on worksheets using constant references, like Range("B6"), or Cells(6,2).

Why? The constants will not be updated in the VBA code when rows or columns are inserted or deleted on the worksheet. This would break the code straight away.

What then? Always use defined names, e.g. Range("MyNamedRange")
2. Never access worksheets by their tab names, like Worksheets("Sheet1")

Why? People tend to change the tab names of worksheets and it would break your code.

What then? Always refer to worksheets using their code names (e.g. Sheet1.Cells(...))

Tip:You can assign meaningful code names to worksheets through the Project Explorer and the properties window in VBE.
3. Always indent the code according to its logical structure, i.e. shift the lines between IF and END IF, FOR and NEXT, etc. by 2 or 4 spaces to the right consistently.

Why? It makes it much easier to read and understand the code.

Tip: To shift many lines at once, select all of them and hit TAB to shift them to the right, or SHIFT+TAB to shift them to the left. Whether it will be 2 or 4 spaces is defined in Tools > Options > tab Editor > Tab Width.
4. Do not select or copy-paste values on worksheets through VBA

Why? It is extremely slow and almost never necessary.

What then? To copy values from one range to another, just use the assignment statement, e.g. Range("NamedTargetRange").Value = Range("NamedSourceRange").Value. And you can access any range on a worksheet without selecting it.
5. Always use Option Explicit

Why? First, if you do not use Option Explicit and misspell a variable, VBA will treat it as a new variable having a value of zero and won't throw an error. And this error will be hard to find! Second, Option Explicit encourages you to think of proper types for variables, rather than just using Variant, which is often inefficient.
6. Use meaningful names for variables, e.g. Dim rngCurrentRow as Range, sCustomerName as String, not just Dim a as Range, b as String.

Why? It is too easy to forget what the a's and b's mean. Meaningful names save much more time on reading and debugging the code, compared to the time it takes to type their "longer" names when writing the code. They also make the code more self explanatory, meaning less commenting.
7. Use a naming convention

What is it? A naming convention is a set of rules about how to name variables. For example, you might always precede a variable of the type Double with a small "d" (e.g. Dim dSomething as Double), a variable of the Long type with "l" (Dim lSomehingElse as Long), and add an "a" if it is an array (e.g. Dim daSomething() as Double), and so on.

Why? It makes it much easier to write code knowing the type of every variable from its name.
8. Never use Option Base.

Why? If you copy of a piece of code written with reliance on Option Base to another project where the base is different, it will stop working correctly!

What then? Always explicitly dimension the lower and the higher bound of your arrays.
9. Never use GoTo.

Why? It makes your code inevitably more complex to understand and debug. It is always possible to achieve the same result using things such as IF, Exit Do or Exit For, etc. Even if it may look lengthier at first, it would save you time and effort in the long run.
10. Use "On Error Resume Next" only when necessary.

Why? Ignoring errors neither prevents them, nor fixes them. They will still be in the code and you will just find them later and with more effort. This statement can however be very useful if you know exactly what error you expect in a part of your code. You would use "On Error Goto 0" straight after that part and may also want to indent the code between those two lines.