Excel Programmer : Fix Misteakes

10/10/2010 9:41:04 AM
Mistakes are a fact of life, and Visual Basic is fairly intolerant of them. If you followed along with the preceding lab, you probably already encountered that fact. Sometimes it's pretty easy to tell what you've done wrong, and sometimes it's almost impossible—even for experienced programmers! What marks the difference between beginning and expert programmers is how they go about solving those problems.

To help you understand fixing mistakes, you need to know that there are four different kinds of errors that are generally identified by where or why they happen:

Syntax errors

Occur when you mistype a statement, such as omitting a closing parenthesis or omitting some part of the statement that is required. Visual Basic detects these errors right away and highlights them in red as soon as you move to the next line of code.

Semantic errors

Are also often the result of a typo, but they appear valid to Visual Basic as you type. Examples of this kind of error include misspelling a method or property name or using a variable or procedure name that isn't defined yet. Visual Basic checks for these errors the moment you run your code (for instance, when you press F5). At that point, Visual Basic converts your code into a form that Excel understands (this is called compiling), and if any of the names you used aren't found, compiling stops and Visual Basic highlights the error. Semantic errors are sometimes called compile-time errors for that reason.

Logic errors

Can be the hardest to detect. These errors occur when your code simply doesn't do what you expected it to do. Infinite loops (lather, rinse, repeat...) are an example, as are unexpected results such as formatting code that doesn't format everything it should. Logic errors can sometimes halt your code while it is running, and for that reason they are often called runtime errors .

Expected errors

Aren't your fault, but you need to deal with them all the same. These are another type of runtime error, and they are usually the result of using resources outside of Excel, such as trying to get a file from disk or trying to connect to a database somewhere. In those cases, you need to anticipate the possibility of a problem using a technique called exception handling.

The real name for expected errors is exceptions . (Since you expect them, they aren't really errors, are they?)

For now, let's look at fixing the errors that are your fault.

1. Fix Syntax Errors

Visual Basic can detect many kinds of typos as you move from line to line in the code window. This is the most common type of error you'll make as you learn programming. Fortunately, Visual Basic can generally tell what you did wrong, as shown in Figure 1.

Figure 1. Visual Basic stops you when you make a syntax error

If you click OK but don't fix the error, Visual Basic leaves the line colored red as a reminder that you should fix it. If you look at the SyntaxErrors sample, you'll see that it looks like a Christmas tree with all the green comments and red errors that I've deliberately included to illustrate the different ways errors in syntax can occur.

If you don't understand the error dialog box Visual Basic displays, you can either click Help to get more information about the error or click OK, select the item you have a question about, and press F1 as shown in Figure 2.

Figure 2. Select If and press F1 to find out about it

Help often tells you what you need to know about a specific Visual Basic statement. Sometimes it's less helpful about Excel methods, but it's always a good first place to look since it's only a key press away. Another good, easy way to figure things out is by using Visual Basic's Auto Complete feature. By default, Visual Basic displays lists of items that could complete statements as you type, as shown in Figure 3.

Figure 3. Visual Basic lists items that could complete a statement as you type it

To insert one of the items from the list, use the arrow keys or mouse to select the item and press the spacebar to insert the item in your code. A similar thing happens when you add a statement that takes arguments, as shown in Figure 4. (Arguments are additional pieces of information that a statement needs to accomplish its task.)

Figure 4. Visual Basic also lists the arguments that a statement takes

In this case, the arguments are shown in italics with the current one shown in bold. Arguments are always separated by commas and once you type a comma, the next argument becomes bold. Square brackets mean that an argument can be omitted.

Visual Basic's automatic syntax checking and Auto Complete features can help you learn the language, but some programmers find the error dialogs and pop-up text annoying in some situations. Visual Basic lets you turn off these features by choosing Tools → Options and selecting the Editor tab as shown in Figure 5.

Figure 5. You can change Code Settings to turn off Visual Basic's syntax checking and Auto Complete features

Don't do it! Syntax checking and Auto Complete are incredibly useful if you are learning the language.

2. Fix Compile-Time Errors

In some cases, statements look correct to Visual Basic as you are writing them, but they don't make sense when Visual Basic tries to compile them into a program. This occurs because there are some things Visual Basic has to ignore as you are writing the code but can't ignore when you try to run it.

A simple example is when your code calls a procedure that you haven't written yet. Visual Basic doesn't flag that statement as a syntax error, because it assumes you'll get around to writing the procedure. If you forget to do that, Visual Basic reminds you when you try to run the code (Figure 6).

Figure 6. Visual Basic couldn't find ChangeSheets, so it displays an error during compilation

Visual Basic alerts you to compile-time errors one at a time, so if you fix the one shown in Figure 6 and then try to run again, another error will pop up on the SyntaxError line—that's a case of a simple misspelling, it should be SyntaxErrors.

Visual Basic is strict about spelling and doesn't guess at what you meant to write. It would be cool if it were that intuitive, but it would cause bigger problems if it guessed wrong!

Sometimes compile-time errors are similar to syntax errors, such as when you omit a required argument or don't terminate a statement that spans multiple lines, such as a loop or a decision statement. In those cases, Visual Basic flags the End Sub or End Function statement because it searched to the end of the procedure without finding the end of the previous block (Figure 7).

The missing End If is pretty obvious in Figure 7 because the procedure is not very long, but it can be much harder to locate where the End If should go in longer passages of code. For that reason, programmers usually indent blocks of code that are logically related, for example:

    ' Activate the next worksheet or chart, depending on
' what type of sheet is currently active. Return to
' first sheet when the end is reached.
Sub ChangeSheets( )
Select Case TypeName(ActiveSheet)
Case "Worksheet"
If ActiveSheet.Index < Worksheets.Count Then
Worksheets(ActiveSheet.Index + 1).Activate
End If
Case "Chart"
If ActiveSheet.Index < Charts.Count Then
Charts(ActiveSheet.Index + 1).Activate
End If
Case Else
Debug.Print TypeName(ActiveSheet), ActiveSheet.Name
End Select
End Sub

Figure 7. Visual Basic flags End Sub because it couldn't find End If before it

In this case, indents make it easier for you to match the begin and end statements for various blocks of code (seen here with all the details removed):

Select Case
' task
' task
End If
' task
' task
End If
Case Else
' task
End Select
End Sub

Indenting is a standard practice that helps you avoid errors by making it easier to read and interpret logically related pieces of your code. It is not required by Visual Basic, and adding or omitting indents does not affect how your code runs.

3. Fix Runtime Errors

Boy, it seems like a lot of things can go wrong! However, most of these problems are pretty obvious and easy to fix. That's not so true for errors that occur when your program is running. Unlike other types of errors, Visual Basic can't detect these until the program actually tries to execute the statement. That makes it harder to tell where the error occurred and why it happened. For example, Figure 8 shows a procedure with a runtime error.

Figure 8. Runtime error displayed after pressing F5; doesn't highlight the line where the error occurred

You don't know which line the error occurred on, although you might guess it was the Worksheets("Resource").Activate statement because I said so in my comments. If you want to confirm that's the error, press F8 instead of F5 to step through the procedure (Figure 9).

In this case, you have to ask yourself why Excel couldn't find the Resource worksheet. Well, it's because the worksheet is actually named Resources. I don't mean to beat you over the head with this, but spelling is important!

Figure 9. Press F8 to run the procedure one line at a time to locate runtime errors

Runtime errors occur for a variety of reasons. For instance, there is a limit to how big a number can be in Visual Basic and 100 ^ 100 ^ 100 exceeds that limit (Visual Basic calls that an overflow ). Other errors are harder to find with F8, for example the EasyRTErrors statement calls itself over and over again indefinitely. That's similar to an infinite loop, but since it's calling itself, it's referred to as infinite recursion instead. If you try F8 on that line, you'll see that you can execute it more than 5000 times without an error. In that case, you just need to remember that an Out of stack space error usually means you've got an infinite recursion.

Another type of runtime error that's very common but difficult to find is misspelled variable names . For example, the following code displays a dialog box, but never says "Howdy" no matter what the user clicks:

    Sub SubtleRTErrors( )
' I misspelled answer, you never hear Howdy:
answer = MsgBox("Click OK to hear Howdy.")
If aswer = vbOK Then Application.Speech.Speak "Howdy"
End Sub

The Speech object was added to Excel in 2002. If you have an earlier version, use MsgBox instead of Application.Speech.Speak for this sample.

There's nothing technically wrong with the code, other than the fact that it doesn't work! This problem occurs because Visual Basic lets you create variables without ever declaring them. That makes life easier in the beginning (about 30 minutes) but adds a tremendous burden later on trying to locate and fix this type of subtle error. Fortunately, there's a fix: turn off automatic variables by choosing Tools → Options and selecting the Editor tab, then selecting Require Variable Declaration as shown in Figure 10.

Figure 10. Require Variable Declaration will avoid subtle runtime errors

When you select Require Variable Declaration, Visual Basic adds an Option Explicit statement any time it creates a new class or module. If you wrote code before changing that option, you need to add Option Explicit yourself. The Option Explicit statement causes a compile-time error whenever it encounters an undefined variable, as shown in Figure 11.

Figure 11. Option Explicit helps identify misspelled variable names

Using Option Explicit creates a little more work writing code, but it saves a lot of work fixing code later.

  •  Excel Programmer : Change Recorded Code
  •  Excel Programmer : Record and Read Code
  •  Configuring Server Roles in Windows 2008 : New Roles in 2008
  •  Windows Server 2003 : Creating and Configuring Application Directory Partitions
  •  Windows Server 2003 : Configuring Forest and Domain Functional Levels
  •  Windows Server 2003 : Installing and Configuring Domain Controllers
  •  Manage Server Core
  •  Configure Server Core Postinstallation
  •  Install Server Core
  •  Determine Your Need for Server Core
  •  Install Windows Server 2008
  •  Windows Server 2008 : Configure NAP
  •  Incorporate Server Core Changes in Windows Server 2008 R2
  •  Decide What Edition of Windows Server 2008 to Install
  •  Perform Other Pre-Installation Tasks
  •  Developing Windows Azure Services that Use SQL Azure
  •  Creating Windows with Mixed Content
  •  Mixing Windows and Forms
  •  Exploring an Assembly Using ildasm.exe
  •  The Assembly/Namespace/Type Distinction
    Top 10
    DirectX 10 Game Programming : 3D Primer - Primitive Types
    DirectX 10 Game Programming : 3D Primer - Matrix Rotation, Multiply Matrices
    DirectX 10 Game Programming : 3D Primer - Matrix Scaling, Matrix Translation
    Microsoft ASP.NET 3.5 : Caching Application Data (part 5) - SQL Server Cache Dependency
    Microsoft ASP.NET 3.5 : Caching Application Data (part 4) - Designing a Custom Dependency, A Cache Dependency for XML Data
    Microsoft ASP.NET 3.5 : Caching Application Data (part 3) - Practical Issues
    Microsoft ASP.NET 3.5 : Caching Application Data (part 2) - Working with the ASP.NET Cache
    Microsoft ASP.NET 3.5 : Caching Application Data (part 1) - The Cache Class
    Twitter On Trial
    How To Buy…A Media Streaming Device (Part 2)
    Most View
    Maintaining Windows 7 with Backup and Restore (part 2) - Using Advanced Backup Options & Using System Protection
    RuggedKey – Awesome Flash Drive
    ASUS Asus Maximus V Extreme vs EVGA Z77 FTW (Part 2)
    How did Webs put the world on maps? (Part 1)
    Designing a Windows Server 2008 R2 Active Directory : Understanding AD DS Domain Design
    Anatomy of Utrabooks (Part 4) - DELL XPS 13
    Oracle Database 11g : Database Fundamentals - Work with Object and System Privileges, Introduce Yourself to the Grid
    Microsoft Sued Comet For Making 94,000 Copies Of Counterfeit Windows
    Photoupz, Xara Photo, Mouse Without Borders
    Adobe Premiere CS6 : Time Is Money
    Livescribe Sky 4GB Wi-Fi Smartpen
    Anatomy of Utrabooks (Part 2) - Acer Aspire S3
    Find Yourself With Geolocation Technology (Part 1)
    Logisys Dracula VGA Cooler
    The Future Of Apple: Chip Off The Block (Part 1)
    Optimisation Utility: Ashampoo Winoptimizer 9.0
    Programming WCF Services : Queued Services - Transactions
    System Center Configuration Manager 2007 : Certificate Requirements Planning, Windows Server 2008 Planning
    Programming with DirectX : Sound in DirectX - XACT3 (part 2) - XACT3 Demo
    Adobe Illustrator CS5 : Proofing and Printing Documents - Finding and Changing Fonts