Excel 2007 Upgrade FAQ: Macros

Category: General VBA | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q. How do I record a macro?

A. Click the little square icon in the bottom left of the status bar.

Q. How do I run a macro?

A. Choose Macros in the Code group of the Developer tab.

Q. I don't have a Developer tab.

A. Display the Excel Options dialog box, click Popular, and then enable 'Show Developer tab in the Ribbon.'

Q. I recorded a macro and saved my workbook. When I reopened it, the macros were gone!

A. By default, Excel proposes that you destroy your macros when you save the workbook. When you save the file, read the warning very carefully, and don't accept the default "Yes" button.

Q: I recorded a macro while formatting a chart, and the macro was empty.

A: The VBA macro recorder ignores formatting applied to individual chart elements. Maybe this will be fixed in a future service pack.

Q: Using VBA to modify Shapes is very tricky, so I tried to record a macro while working with a Shape. The macro was empty.

A: Maybe this will be fixed in a future service pack.

Q: I'm trying to automate creating a simple SmartArt diagram. Recording a macro produces an empty macro.

A: Maybe this will be fixed in a future service pack.

Q: How do I use VBA to add a simple button to the ribbon?

A: You can't. You must write XML code and insert the document into a workbook file using 3rd party tools. Or, if you're a glutton for punishment, you can do it by unzipping the document and making the edits manually.

Q: How do I use VBA to activate a particular tab.

A: Sendkeys is your only choice. Press the Alt key to find out the keysroke(s) required. For example, to switch to the Page Layout tab, use this: Application.SendKeys "%w{F6}"

Q: I'm trying to display a topic from a *.chm help file from a Messge Box or an Input Box. Using Application.Help simply displays the main Excel help window.

A: Maybe this will be fixed in a future service pack.

Q: Can I use the VBA Application.Help method to display a particular Excel 2007 help topic?

A: No, but you can use Application.Assistance.ShowHelp method. First, navigate the local Help system and identify the topic ID. Right-click, and select the "Copy xxxxxxxx" option (this copies the topic ID to the clipboard. Then use a VBA statement like this: Application.Assistance.ShowHelp "HP10062493". The text in quotes is the topic ID pasted from the clipboard.

Controlling User Scrolling

Category: General VBA | [Item URL]

An Excel worksheet contains millions of cells. Most of the worksheets that you develop use only a tiny portion of the available area in a worksheet. Novice users sometimes get los" in a worksheet. For example, they may hit PgDn a few times and be faced with an apparently blank worksheet.

Hiding Rows and Columns

You can prevent users from scrolling around unused areas of a worksheet by hiding the unused rows and columns using the Format Column Hide and Format Row Hide commands. For example, if the active area in your worksheet consists of the range A1:G25, you can hide columns H through IV and rows 26 through 16384. The result is an apparently smaller workbook that doesn't display any unused rows or columns.

After hiding unused rows, you may discover a problem: If the user presses the PgDn key when the active cell is near the last unhidden row, the worksheet scrolls up and displays a blank area -- in fact, the entire worksheet may scroll out of view. Obviously, this can cause even more confusion than displaying empty rows.

Trapping the PgDn Key

The solution to the problem described above is to trap the PgDn key. In VBA, you can use Excel's OnKey method to execute a subroutine whenever a particular keystroke occurs. The subroutine below, (which is executed when the workbook is opened), causes a subroutine named DownOne to be executed whenever the user presses the PgDn key.

Sub Auto_Open()
    Application.OnKey "{PgDn}", "DownOne"
End Sub

You'll also need an Auto_Close subroutine to restore the key to normal when the workbook is closed.

Sub Auto_Close()
    Application.OnKey "{PgDn}"
End Sub

The DownOne subroutine, listed below, moves the active cell to the row below -- but only if the row is not hidden. The net effect is that pressing PgDn mimics the down arrow key.

Sub DownOne()
    If Not ActiveCell.Offset(1, 0).EntireRow.Hidden Then _
        ActiveCell.Offset(1, 0).Activate
End Sub

Fine-Tuning

The procedures listed above cause the DownOne subroutine to be executed whenever PgDn is pressed. However, you may want this subroutine to be executed only when the workbook with the hidden rows and columns is activated. In other words, you may want PgDn to operate normally in all workbooks except the one that contains the hidden rows and columns.

The procedures listed below accomplish this. Whenever a sheet is activated, the TrapKey subroutine is executed. The TrapKey subroutine sets up the OnKey event if the active workbook is the workbook that contains the hidden rows and columns; otherwise, the OnKey event is cancelled.

Sub Auto_Open()
    Application.OnSheetActivate = "TrapKey"
End Sub

Sub Auto_Close()
    Application.OnSheetActivate = ""
End Sub

Sub TrapKey()
    If ActiveWorkbook Is ThisWorkbook Then _
        Application.OnKey "{PgDn}", "DownOne" Else _
            Application.OnKey "{PgDn}"

End Sub

Sub DownOne()
    If Not ActiveCell.Offset(1, 0).EntireRow.Hidden Then _
        ActiveCell.Offset(1, 0).Activate
End Sub

Creating A List Of Formulas

Category: Formulas / General VBA | [Item URL]

Most users have discovered that Excel has an option that lets you display formulas directly in their cells: Choose Tools Options, click the View tab, and select the Formulas checkbox. However, Excel doesn't provide a way to generate a concise list of all formulas in a worksheet. The VBA macro below inserts a new worksheet, then creates a list of all formulas and their current values.

NOTE: My Power Utility Pak add-in includes a more sophisticated version of this subroutine, plus several other auditing tools.

To use this subroutine:

1. Copy the code below to a VBA module. You can also store it in your Personal Macro Workbook, or create an add-in.
2. Activate the worksheet that contains the formulas you want to list.
3. Execute the ListFormulas subroutine. The subroutine will insert a new worksheet that contains a list of the formulas and their values.

The ListFormulas Subroutine

Sub ListFormulas()
    Dim FormulaCells As Range, Cell As Range
    Dim FormulaSheet As Worksheet
    Dim Row As Integer
    
'   Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
    
'   Exit if no formulas are found
    If FormulaCells Is Nothing Then
        MsgBox "No Formulas."
        Exit Sub
    End If
    
'   Add a new worksheet
    Application.ScreenUpdating = False
    Set FormulaSheet = ActiveWorkbook.Worksheets.Add
    FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name
    

'   Set up the column headings
    With FormulaSheet
        Range("A1") = "Address"
        Range("B1") = "Formula"
        Range("C1") = "Value"

        Range("A1:C1").Font.Bold = True
    End With
    
'   Process each formula
    Row = 2
    For Each Cell In FormulaCells
        Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
        With FormulaSheet
            Cells(Row, 1) = Cell.Address _
                (RowAbsolute:=False, ColumnAbsolute:=False)
            Cells(Row, 2) = " " & Cell.Formula
            Cells(Row, 3) = Cell.Value
            Row = Row + 1
        End With
    Next Cell
    
'   Adjust column widths
    FormulaSheet.Columns("A:C").AutoFit
    Application.StatusBar = False
End Sub 

Selecting All Unlocked Cells

Category: General VBA | [Item URL]

As you probably know, when a worksheet is protected, only the unlocked cells can be changed. You may want to apply different formatting to the unlocked cells, or simply ensure that the correct cells are indeed unlocked.

Oddly, there is no direct way to quickly identify which cells are locked or unlocked. When the sheet is protected, you can use the Tab key to move among the unlocked cells, but it you would like to select them all you'll need a macro.

The macro listed below selects all unlocked cells on the active worksheet.

Sub SelectUnlockedCells()
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "All cells are locked."

    Else
        FoundCells.Select
    End If
End Sub

Looping Through A Range Efficiently

Category: General VBA | [Item URL]

A common type of macro involves looping through a range of cells, and performing an action based on the contents of each cell. For example, you may want to make the cell bold if the value in the cell is negative. This tip describes how to create such a macro. You should be able to adapt the technique described here to handle your own needs.

I list several macros, each increasingly more sophisticated.

Looping Through a Range: Take 1

The subroutine below demonstrates how to loop through all cells in the current selection. The routine checks the value of each cell in the range and adjusts the Bold property of the Font object accordingly. Notice that the subroutine starts by ensuring that the selection consists of a range.

Sub BoldNegative()
    If TypeName(Selection) <> "Range" Then Exit Sub
    For Each cell In Selection
        If cell.Value < 0 Then cell.Font.Bold = True Else Font.Bold = False
    Next cell
End Sub

Looping Through a Range: Take 2

The previous subroutine works fine in most situations. But what if the selection consists of one or more entire rows or columns? Excel users are accustomed to selecting entire rows and columns, so your macro should be able to handle this type of situation. If you select an entire row before running the preceding macro, you'll find that it works, but it's not very efficient - and excruciatingly slow since it checks every cell in the selection. Ideally, the macro should just examine the non-empty cells. You can accomplish this by using the SpecialCells method of the Range object (refer to the online help for details).

The subroutine below improves upon the previous routine. It examines only the non-empty cells in the selection. It does this by first checking the cells with constants, and then the cells with formulas. Notice the use of the On Error statement. This is necessary because the SpecialCells method returns an error if no cells qualify. You'll find that this routine works equally fast even if the entire worksheet is selected.

Sub BoldNegative()
    If TypeName(Selection) <> "Range" Then Exit Sub
    On Error Resume Next
'   Check the cells with constants
    For Each cell In Selection.SpecialCells(xlConstants, 23)
        If cell.Value < 0 Then cell.Font.Bold = True Else cell.Font.Bold = False
    Next cell
'   Check the cells with formulas
    For Each cell In Selection.SpecialCells(xlFormulas, 23)
        If cell.Value < 0 Then cell.Font.Bold = True Else cell.Font.Bold = False
    Next cell
End Sub

Looping Through a Range: Take 3

The preceding subroutine works fine, but it is not as efficient as it could be. You'll notice that a block of code is repeated. When code is repeated in a routine, you can often create a separate procedure, and then call the procedure rather than repeat your code. This is demonstrated in the two subroutines below. The CheckCells subroutine takes a Range object argument, and is called twice by the BoldNegative procedure.

Sub BoldNegative()
    If TypeName(Selection) <> "Range" Then Exit Sub
    On Error Resume Next
'   Check the cells with constants
    Call CheckCells(Selection.SpecialCells(xlConstants, 23))
'   Check the cells with formulas
    Call CheckCells(Selection.SpecialCells(xlFormulas, 23))
End Sub

Sub CheckCells(CurrRange As Range)
    For Each cell In CurrRange
        If cell.Value < 0 Then cell.Font.Bold = True Else cell.Font.Bold = False
    Next cell
End Sub

Looping Through a Range: Take 4

If a single cell is selected when the preceding BoldNegative subroutine is executed, you'll find that all non-blank cells in the worksheet are examined. This may not be what you want. Therefore, it's necessary to make one additional check - to determine if the selection consists of a single cell. If so, only that cell is checked. The routine below incorporates this modification (the CheckCells routine is unchanged).

Sub BoldNegative()
    If TypeName(Selection) <> "Range" Then Exit Sub
'   If one cell is selected, check it and exit
    If Selection.Count = 1 Then
        CheckCells (Selection)
        Exit Sub
    End If
    On Error Resume Next
'   Check the cells with constants
    Call CheckCells(Selection.SpecialCells(xlConstants, 23))
'   Check the cells with formulas
    Call CheckCells(Selection.SpecialCells(xlFormulas, 23))
End Sub

Using Object Variables

Category: General VBA | [Item URL]

Writing VBA procedures for Excel essentially involves manipulating objects. Excel itself is an object (called Application) and it contains more than 100 additional objects. Here's an example of using an object (in this case, a range object). This statement assigns the value in a cell to a variable named Dependents.

Dependents = Workbooks("Taxbook"). _
  Worksheets("Sheet1").Range("A5").Value

In this example, Dependents is a "normal" variable (not an object variable). After this assignment is made, your code can use the Dependents variable anyway it chooses. However, if your code changes the value of the Dependents variable, the change will not be made to cell A5. An object variable is a variable that serves as a substitute for the actual object. To create an object variable, use the Set keyword. The statements below create an object variable for the Range object:

Dim Dependents as Range
Set Dependents = Workbooks("Taxbook").Worksheets("Sheet1") _
   .Range("A5")

After this assignment is made, you can use the Dependents variable as if it were the actual object. This includes modifying its properties and using its methods. For example, you can change the value stored in cell A5 with the following statement:

Dependents.Value = 3

Advantages of Using Object Variables

So what is the advantage of using object variables? There are two:

1. Your code will be easier to understand and write
2. Your code might run slightly faster

After all, typing:

  Dependents.Value

is much faster than typing:

  Workbooks("Taxbook").Worksheets("Sheet1").Range("A5").Value

And, you'll probably agree that the code is much more meaningful. The speed advantage of using object variables may not be apparent, but if you perform timed tests you will find that working with object variables is almost always a bit faster than working with the objects themselves. One rule of thumb is to eliminate the number of "dots" processed. The first statement below contains three dot operators. The second statement contains only one dot.

Workbooks("Taxbook").Worksheets("Sheet1").Range("A5").Value = 3
Dependents.Value = 3

Referring To Ranges In Your VBA Code

Category: General VBA | [Item URL]

Newcomers to VBA are often confused about how to refer to ranges on a worksheet. This confusion is somewhat justified, because VBA offers several different ways to refer to ranges. In this document I provide an overview of these techniques:

• Referring to ranges directly
• Referring to ranges by using the Cells method
• Referring to ranges by using the Offset method

Referring to ranges directly

Perhaps the most common way to refer to a range on a worksheet is to specify the range directly. Here's an example that assigns the value 1 to range A1:C12 on Sheet1 in a workbook named MyBook:

  Workbooks("MyBook").Sheets("Sheet1").Range("A1:C12").Value = 1

Notice that this is a fully qualified reference. This statement will work regardless of which sheet is active. If Sheet1 is the active sheet, the statement can be simplified as:

  Range("A1:C12").Value = 1

If the range has a name, you can use the name in your statement:

  Range("myrange").Value = 1

You can also refer to a range by specifying the upper left and the lower right cell. Here's an example that assigns a value to all cells in the range A1:D12 on the active worksheet.

  Range(Range("A1"), Range("D12")).Value = 99

Referring to ranges by using the Cells method

In Excel, the Range object has a method called Cells. Note that Cells is a method - not an object. When the Cells method is evaluated, it returns an object (specifically, a Range object).

The Cells method takes two arguments: the row and the column. The following statement assigns the value 1 to cell C2 on Sheet1:

  Worksheets("Sheet1").Cells(2,3).Value = 1

You can also use the Cells method to refer to a larger range. The following statement assigns the value 1 to A1:J12 on the active worksheet:

  Range(Cells(1,1), Cells(12,10)).Value = 1

In the preceding examples, the arguments for Cells were actual numbers. The advantage of using the Cells method becomes apparent when you use variables as the arguments. The subroutine below fills a 10X10 range (rowwise) with consecutive numbers from 1 to 100.

Sub FillRange()
    Num = 1
    For Row = 1 To 10
        For Col = 1 To 10
            Sheets("Sheet1").Cells(Row, Col).Value = Num
            Num = Num + 1
        Next Col
     Next Row
End Sub

Referring to ranges by using the Offset method

The Offset method is another useful way to refer to ranges. The Offset method returns a Range object, and takes two arguments. The first argument represents the number of rows to offset; the second represents the number of columns to offset.

The following statement assigns the value 1 to the cell that is one row below cell C2 and two cells to the right of C2 (i.e., cell E3):

  Range("C2").Offset(1,2).Value = 1

The Offset method is most useful when the arguments are variables, rather than numbers. The subroutine below fills a 10X10 range (rowwise) with consecutive numbers from 1 to 100.

Sub FillRange2()
      Num = 1
      For Row = 0 To 9
          For Col = 0 To 9
              Sheets("Sheet1").Range("A1").Offset(Row,Col).Value = Num
              Num = Num + 1
          Next Col
      Next Row
End Sub 

Understanding Object Parents

Category: General VBA | [Item URL]

When working in VBA, it's important to understand the object model for the application you're using. More specifically, a good knowledge of how various objects relate to each other can often simplify your coding.

Excel's object model is a hierarchy - objects are contained in other objects. At the top of the hierarchy is the application objects (Excel itself). Excel contains other objects, and these objects contain other objects, and so on. The following depicts how a Range objects fits into this scheme.

Application Object (Excel)
    Workbook Object
        Worksheet Object
            Range Object

In the lingo of object-oriented programming, a Range object's parent is the Worksheet object that contains it. A Worksheet object's parent is the Workbook that contains the worksheet. And, a Workbook object's parent is the Application object.

The SheetName Function

How can this information be put to use? Examine the VBA function below. This function, which can be used in a worksheet formula, accepts a single argument (a range) and returns the name of the worksheet that contains the range. It uses the Parent property of the Range object. The Parent property returns an object -- the object that contains the range object.

Function SheetName(ref) As String
    SheetName = ref.Parent.Name
End Function

The WorkbookName Function

The next function, WorkbookName, returns the name of the workbook. Notice that is uses the Parent property twice - the first Parent property returns a Worksheet object; the second Parent property returns a Workbook object.

Function WorkbookName(ref) As String
    WorkbookName = ref.Parent.Parent.Name
End Function

The AppName Function

The function below carries this to the next logical level, accessing the Parent property three times. This function returns the name of the Application object.

Function AppName(ref) As String
   AppName = ref.Parent.Parent.Parent.Name
End Function

Learning More

I've come to the conclusion that a thorough understanding of Excel's object model is absolutely essential for anyone who needs to write non-trivial Excel macros. Study the online help, study VBA code that others have written, and explore the Object Browser. No doubt about it, things can be very confusing - but eventually the pieces fall into place.

And there's an excellent side benefit: Once you figure out how objects work, you'll be able to apply your knowledge very easily to other applications that support VBA. All you need to do is spend some time getting acquainted with the apps's object model.

Working With Variable-Size Ranges

Category: General VBA | [Item URL]

A companion file is available: Click here to download

In many situations, your VBA procedure needs to work with a range that can vary in size. For example, you may have a worksheet that holds weekly sales data. Every week you add a new row to it. Creating a VBA procedure to manipulate the data can be tricky, since the size of the range will vary from week to week. This document describes a number of VBA techniques that are useful when working with ranges that have an unknown size.

The CurrentRegion Property

If you need to work with an entire range of cells (such as a worksheet database, or list), the CurrentRegion property will be useful. This property returns a range object. To understand how this works, experiment with the Current Region option in the Go To Special dialog box (press F5, then click Special).

The statement below selects the current region for cell A1.

Range("A1").CurrentRegion.Select 

The End Property

You are probably familiar with Excel's keystrokes that let you move or select to the end of a row or column. For example, to move the cell pointer to the last nonblank cell in a column, you press Ctrl+Down Arrow (or, End followed by Down Arrow). To select cells from the active cell down to the first blank cell in the column, you press Ctrl+Shift+Down Arrow (or, End followed by Shift+Down Arrow).

If you record a VBA macro using these keystrokes, you may be surprised that the result isn't what you expect. Rather the record code to simulate these key combinations, the recorder simply records the actual cell addresses. However, VBA contains a useful property (End) that provides the equivalent of this type of action. The statement below selects cells from the active cell down to the last non-empty cell in the column.

Range(ActiveCell, ActiveCell.End(xlDown)).Select 

In this case, xlDown is a built-in constant. As you may expect, there are three other constants to simulate key combinations in the other directions: xlUp, xlToLeft and xlToRight.

VBA Code

Following are VBA procedures that perform a wide variety of cell selections.

Sub SelectDown()
'   Like Ctrl+Shift+Down
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Sub SelectUp()
 '   Like Ctrl+Shift+Up
    Range(ActiveCell, ActiveCell.End(xlUp)).Select
End Sub

Sub SelectToRight() 
 '   Like Ctrl+Shift+Right
    Range(ActiveCell, ActiveCell.End(xlToRight)).Select
End Sub

Sub SelectToLeft() 
 '   Like Ctrl+Shift+Left
    Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
End Sub

Sub SelectCurrentRegion() 
 '   Like Ctrl+Shift+*
    ActiveCell.CurrentRegion.Select
End Sub

Sub SelectActiveArea() 
 '   Like End, Home, Ctrl+Shift+Home
    Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
End Sub

Sub SelectActiveColumn() 
 '   Contiguous Cells in ActiveCell's Column 
    If IsEmpty(ActiveCell) Then Exit Sub
'   ignore error if activecell is in Row 1
    On Error Resume Next
    If IsEmpty(ActiveCell.Offset(-1, 0)) Then
        Set TopCell = ActiveCell
    Else
        Set TopCell = ActiveCell.End(xlUp)
    End If
    If IsEmpty(ActiveCell.Offset(1, 0)) Then
        Set BottomCell = ActiveCell
    Else
        Set BottomCell = ActiveCell.End(xlDown)
    End If
    Range(TopCell, BottomCell).Select

End Sub

Sub SelectActiveRow() 
 '   Contiguous Cells in ActiveCell's Row 
    If IsEmpty(ActiveCell) Then Exit Sub
'   ignore error if activecell is in Column A
    On Error Resume Next
    If IsEmpty(ActiveCell.Offset(0, -1)) Then
        Set LeftCell = ActiveCell
    Else
        Set LeftCell = ActiveCell.End(xlToLeft)
    End If
    If IsEmpty(ActiveCell.Offset(0, 1)) Then
        Set RightCell = ActiveCell
    Else
        Set RightCell = ActiveCell.End(xlToRight)
    End If
    Range(LeftCell, RightCell).Select
End Sub

Sub SelectEntireColumn() 
 '   Like Ctrl+Spacebar
    Selection.EntireColumn.Select
End Sub

Sub SelectEntireRow() 
 '   Like Shift+Spacebar
    Selection.EntireRow.Select
End Sub

Sub SelectEntireSheet() 
 '   Like Ctrl+A
    Cells.Select
End Sub

Sub ActivateNextBlankDown() 
 '   Next Blank Cell Below  
     ActiveCell.Offset(1, 0).Select
    Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Sub ActivateNextBlankToRight() 
 '   Next Blank Cell To the Right
    ActiveCell.Offset(0, 1).Select
    Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(0, 1).Select
    Loop
End Sub

Sub SelectFirstToLastInRow() 
 '   Select From the First NonBlank to the Last Nonblank in the Row 
    Set LeftCell = Cells(ActiveCell.Row, 1)
    Set RightCell = Cells(ActiveCell.Row, Columns.Count)
    If IsEmpty(LeftCell) Then Set LeftCell = LeftCell.End(xlToRight)
    If IsEmpty(RightCell) Then Set RightCell = RightCell.End(xlToLeft)
    If LeftCell.Column = Columns.Count And RightCell.Column = 1 Then
        ActiveCell.Select
    Else
        Range(LeftCell, RightCell).Select
    End If
End Sub

Sub SelectFirstToLastInColumn() 
 '   Select From the First NonBlank to the Last Nonblank in the Column
    Set TopCell = Cells(1, ActiveCell.Column)
    Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
    If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
    If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
    If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
        ActiveCell.Select
    Else
        Range(TopCell, BottomCell).Select
    End If
End Sub

VBA Debugging Tips

Category: General VBA | [Item URL]

If you've worked with VBA for any length of time, you know that it's relatively easy to make errors. In fact, most VBA programmers can do so with very little effort on their part. The process of locating and correcting errors in your VBA code is known as debugging.

Types of Errors

There are basically three types of errors that can occur:

• Syntax errors:A variety of errors related to entering the code itself. These include incorrectly spelled keywords, mismatched parentheses, and a wide variety of other errors. Excel flags your syntax errors and you can't execute your code until they are correct.
• Run-time errors:
  These are the errors that occur while your code is executing. There are many, many types of run-time errors. For example, if your code refers to an object that doesn't exist, you'll get a run-time error. Excel displays a message when there is a run-time error.
• Logical errors:
  These are errors that occur through faulty programming. Logical errors may or may not cause a run-time error. In many cases they will simply produce incorrect results.

Debugging your code is the process of finding and correcting run-time errors and logical errors.

Eight Bug Reduction Tips

I can't help you completely eliminate bugs in your programs, but I can provide a few tips that will help you keep them to a minimum.

1. Use an Option Explicit at the beginning of your module. Doing so will require that you define the data type for every variable that you use. It's a bit more work, but you'll avoid the common error of misspelling a variable name.. And there's a nice side benefit: Your routines will often run faster.
2. Format your code with indentation. I've found that using indentation to delineate code segments is quite helpful. If you have several nested For...Next loops, for example, consistent indentation will make it much easier to keep track of them all.
3. Be careful with On Error Resume Next. This statement causes Excel to ignore any errors and continue. In some cases, using this statement will cause Excel to ignore errors that shouldn't be ignored. Your may have bugs and not even realize it.
4. Use lots of comments. Nothing is more frustrating than revisiting code that you wrote six months ago - and not having a clue as to how it works. Adding a few comments to describe your logic can save you lots of time down the road.
5. Keep your subroutines and functions simple. Writing your code in smaller modules, each of which has a single, well-defined purpose, makes it much easier to debug them.
6. Use the macro recorder to help you identify properties and methods. If I can't remember the name or syntax of a property or method, I've found that it's often quicker to simply record a macro and look at the recorded code.
7. Consider a different approach. If you're having trouble getting a particular routine to work correctly, you might want to scrap the idea and try something completely different. In most cases, Excel offers several alternative methods of accomplishing the same thing.
8. Understand Excel's debugger. Although it can be a bit daunting at first, you'll find that Excel's debugger is an excellent tool. Invest some time and get to know it. I used VBA for quite a while before I took the time to learn how the debugger works (it's well documented in the on-line help). I spent about an hour learning the details, and I estimate that it has saved me dozens of hours in wasted time.

Page 2 of 6 pages
[Previous page]   [Next page]