Chapter Seventeen: Mistakes To Avoid
If you are reading this chapter, you will be familiar with Excel VBA. It is easy for anybody to make mistakes when they write a code in VBA. These mistakes will cost you greatly. This chapter lists the common mistakes that most VBA amateurs make.
Not Using Arrays
An interesting mistake that most VBA programmers make is that they try to process all the functions in a large nested loop. They filter the data down through the different rows and columns in the worksheet during the process of calculation. This method can work, but it can lead to performance troubles. If you have to perform the same function repeatedly, the efficiency of the macro will decrease. When you loop through the same column and you extract the values every single time, you are not only affecting the macro, but also affecting the processor. An efficient way to handle a list of numbers is to use an array.
If you have not used an array before, let me introduce it to you now. An array is a set of elements that have the same data type. Each element in the array is given an index. You must use this index to refer to the element in the array. An array can be defined by using the following statement: Dim MyArray (12) as Integer. This will create an array with 12 indices and variables that you will need to fill. Let us look at how a loop with an array will look [48] like:
Sub Test1()
​ Dim x As Integer
​ intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
​ Range("A2").Select
​ For x = 1 To intNumRows
​ arrMyArray(x-1) = Range("A" & str(x)).value)   
​ ActiveCell.Offset(1, 0).Select
​ Next
End Sub
In this example, the code is processing through every cell in the range before it performs the calculation function.
Using .Select or .Activate
You do not have to always use the .Select or .Activate functions when you write code in VBA. You may want to use these functions since the Macro Recorder generates them. These functions are unnecessary for the following reasons:
You should use these functions only when you want to bring the user to a specific cell or worksheet. Otherwise, you should delete the line of code since it will be doing more harm than good.
Using Variant Type
Another mistake that most programmers make is to use one Type when they are actually using another. If you look at the following code, you will think that a, b, and c are of the Long type. Well, that is incorrect since the variables a and b are of the Variant type. This means that they can be any data type, and can change from one type to another.
It is dangerous to have a variant type since it will become difficult for you to identify the bugs in your code. You should always avoid Variant types in VBA. There are some functions that will need the use of a Variant type, but you should avoid them if you can.
Not Using Application.ScreenUpdating = False
When you make a change to a cell or a group of cells in your code, Excel will need to repaint the screen to show the user the changes. This will make your macros slow. When you write a macro the next time, you should use the following lines of code [49] :
Public Sub MakeCodeFaster()
​ Application.ScreenUpdating = False
​ ' Block of code
​ ' This setting should always be reset back
​ Application.ScreenUpdating = True
End Sub
Referencing the Worksheet Name With a String
[50] People will refer to a worksheet using a String. Look at the following example:
Public Sub SheetReferenceExample()
​ Dim ws As Worksheet
​ Set ws = Sheets("Sheet1")
​ Debug.Print ws.Name
End Su b
This does seem harmless does it not. In most cases, it is harmless. Imagine that you give another person this workbook, and that person decides to rename the sheet to “Report.” When he tries to run the macro, the macro will look for “Sheet1,” which no longer exists. Therefore, this macro will not work. You should choose to reference the sheet by using an object instead of using the “Sheets” collection. To be more resilient, let us use the following block of code:
Public Sub SheetReferenceExample()
​ Dim ws As Worksheet
​ Set ws = Sheet1 ' used to be Sheets("Sheet1")
​ Debug.Print ws.Name
End Sub
If you want to rename Sheet1 to something more meaningful, you can go to the VBA Project properties window and make a change to the name of the module. Once you rename the module, you will also need to update the VBA code.
Not Qualifying the Range References
[51] This is a common mistake that most people make when they write their code, and it is a real pain to debug this error. This error comes up when you do not qualify the range reference in the VBA code. You may wonder what I mean when I say range reference.
When you say Range(“A1”), which sheet do you think the code is referring to? It is referring to the Activesheet. This means that the compiler will look at cell A1 in the worksheet that the user is referring to. This is harmless on most occasions, but there are times when you may add more features to your code. These features make it hard for the compiler to execute the code. When the user or even you run the code, and you click on another worksheet, the code will behave differently. Let us look at the following example:
Public Sub FullyQualifyReferences()
​ Dim fillRange As Range
​ Set fillRange = Range("A1:B5")
​ Dim cell As Range
​ For Each cell In fillRange
​ Range(cell.Address) = cell.Address
​ Application.Wait (Now + TimeValue("0:00:01"))
​ DoEvents
​ Next cell
End Sub
Run the code in VBA and see what happens. If you do not specify the worksheet when you use the Range() function, Excel will assume that you are looking at the active sheet. To avoid this, you should make a slight change to your code. All you need to do is change Range(cell.Address) = cell.Address to Data.Range(cell.Address) = cell.Address.
In the second statement, data refers to the sheet object. There are other ways to do this, but I wanted to use a simple example which did not need the addition of too much code .
Writing a Big Function
If you go back to some of the old functions you may have written, you will notice that they are very long. You will need to continue to scroll until you reach the end of the function.
You should remember that the function you write should fit your screen. You should be able to view the code without having to scroll. You must ensure that you keep the methods short by creating sub procedures or helper functions.
Using Nested For or If Statements
[52] You may have read earlier that you can include many levels of nesting when you write your code. Do you think that is a good idea? You will need to add comments and indent the code to ensure that another user can read your code. If you are unsure of what I mean by nesting, let us look at the following example:
Public Sub WayTooMuchNesting()
​ Dim updateRange As Range
​ Set updateRange = Sheet2.Range("B2:B50")
​ Dim cell As Range
​ For Each cell In updateRange
​ If (cell.Value > 1) Then
​ If (cell.Value < 100) Then
​ If (cell.Offset(0, 1).Value = "2x Cost") Then
​ cell.Value = cell.Value * 2
​ Else
​ ' do nothing
​ End If
​ End If
​ End If
​ Next cell
End Sub
This is certainly not a clean code. If you use more than three levels of nesting, you have gone too far. To reduce the number of nesting levels, you should invert the condition in your If statement. In the example above, the code will make a change if a bunch of statements pass. You can invert this to ensure that the compiler will only execute the statements for the opposite case. That way you can skip the many levels of nesting.
Let us look at the updated [53] version of the above example.
Public Sub ReducedNesting()
​ Dim updateRange As Range
Set updateRange = Sheet2.Range("B2:B50")
​ Dim cell As Range
​ For Each cell In updateRange
​ If (cell.Value <= 1) Then GoTo NextCell
​ If (cell.Value >= 100) Then GoTo NextCell
​ If (cell.Offset(0, 1).Value <> "2x Cost") Then GoTo NextCell
​ cell.Value = cell.Value * 2
NextCell:
​ Next cell
End Sub
You can also combine the If statements in the code above if you wish.