TOP FIVE MOST COMMON VBA ERRORS AND THEIR SOLUTIONS

Here are the five most common errors I have found (and made) while programming in VBA. Most errors are as valid in Excel VBA as they are in Microsoft Access VBA.

Still debugging while in production.

Congratulation, you are now in production. Are you really? You have still left some debugging tools active in your production code. How do I know? You have left some command such as STOP,  Debug.Print and Debug.Assert.

Although very useful when construction is on, you should always take them off after launch for the same reason you don't let your scaffolds out after you finished painting your ceilings.

Breakpoints should all be removed too.

1) 'Case Else' is missing. 

Select Case are a convenient way to handle several different values. It is also much more efficient than several imbricated IF...elseif...elseif...
If an unexpected value appears, the following  Select Case statement has no effect.

  x = 10
  Select Case x
    Case 1 To 5
      'Statement
    Case 6 To 9
      'Statement
  End Select
 

Always use a Case Else branch to ensure that all possible scenarios in your code are considered. 
 

  x = 10
  Select Case x
    Case 1 To 5
      'Statement
    Case 6 To 9
      'Statement
    Case Else
      MsgBox "Unexpected value:" & x
  End Select

Personally, I always declare the base structure as
 

Select Case x
     Case else
    'oops
    ...
End Select

2) Variable, constant or function declared without a data type. 

You should never declare a variable, a constant or a function without specifying its data type. In such cases, the default Variant data type is used. Using variant is usually sign of sloppy design (you don't know yet what you will get) and also slow on execution.
Avoid declaring variables using the declaration data type char, it is nearly as bad as not declaring anything.

          %                 Integer
          &                 Long
          !                 Single
          #                 Double
          $                 String
          @                 Currency

Instead of:

Dim strSQL$
Dim recNumber&

be specific: 

Dim strSQL as String
Dim recNumber as Long

3) Handle your errors like a great programmer, not like a rookie!

Even after year of experiences and in a simple code, there will be errors. If you didn't include error handling in your procedure, when you have a runtime error, the program will stop and display an error message. There are many ways to handle that, here is my way:

Function Display2Char(ByVal AsciiCode As String) As Boolean
   Dim strText As String
   On Error GoTo Display2Char_Error '<< first instruction: engage error management
   ' Function body goes here
   ' ...
Display2Char_Error: '<< we always check for error before leaving
   Select Case Err.Number
      Case Is = 0:
         ' do nothing since there is no error
      Case Is = 513:
         ' Invalid ascii char
         '...
         Resume Next
      Case Else:
           MsgBox "Error " & Err.Number & " (" & Err.Description & _
               ") in Sub Display2Char of Module Demo"
   End Select   
   On Error GoTo 0 '<< Release Error Management
End Function

And the number-one problem is...
 

4) Option Explicit is missing.

The statement "Option Explicit" should always be the first line of all VBA modules. I have stopped counting the number of problems I avoided since I use that instruction. It forces you to declare all your variables before you use them. Besides the advantage of having a cleaner program with declared variables, doing so (and most importantly) prevents you from making typos in variable name.

For example, if you have a declared variable named MyAccessKey, you will get compilation error when you later  try to use a variable mistakenly written MyAccesKey (missing one "s") or MyAccessKeys (one "s" too many).

So those were the top five most common errors in VBA. If you liked ... What? You see really four problems instead of five? Let me check that... Ok, I see where you are going with that: The number one problem partially encompassed problem number 3. So, because I am a great soul, and because I don't want to look cheap, I will give you,  free of charge, an extra problem #3 and its solution.

5) Parameter not explicitly declared with either ByVal or ByRef

By default, all parameters are passed as reference (ByRef). It means that the variable passed to a procedure can be changed, even accidentally, and will now appear changed from the calling procedure. It is usually not a good idea to do that but life can be full of surprises... Most of the time, when we pass a variable as a parameter, really want to pass its value (ByVal) instead of the variable itself. So, in order to pass the right thing, we should always clearly specify whether we want to pass a variable ByVal or ByRef.

Sub x()
    klm As Double
    = 5
    klm, "Start x"
   Call y(klm)
    klm, "end x."
End Sub
Sub y(ByVal wzk As Double) <-- nbsp="" span="" style="background-color: plum; box-sizing: border-box;">ByVal is added, or not, in the tests below.
    wzk, "Start y"
   wzk = wzk * 2
    wzk, "end y."
End Sub

Results WITHOUT specifying ByVal (ByRef by default)

5           Start x
       5    Start y
      10    end y.
10          end x. <-- nbsp="" span="" style="box-sizing: border-box; text-decoration: underline;" sub="" to="" unknown="" value="" x="">WAS 

changed.

Results WITH ByVal specified.

5           Start x
       5    Start y
      10    end y.
5           end x. <-- p="" remains="" subroutine="" to="" unchanged="" value="" x.="">

That really conclude my top five most common VBA error's countdown. Do you have other errors? Let me know.

Excel is Fun.