Programming Excel with VBA and .NET

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 1-13 shows a procedure with a runtime error.

Figure 1-13. 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 1-14).

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 Resource s. I don’t mean to beat you over the head with this, but spelling is important!

Figure 1-14. 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 ...