Programming Excel with VBA and .NET : Expressions, Exceptions

1. Expressions

In programming languages, expressions are anything that produces a result. You use expressions to evaluate something, such as a math calculation or a true/false condition. In Visual Basic, the Immediate window functions as an expression evaluator, so it's a good place to try out different expressions, as shown in Figure 1.

Figure 1. Use the Immediate window to quickly evaluate expressions

Expressions can return values of any type, including arrays or references to objects. The Immediate window can't display those types, however. Within a program, expressions are usually found to the right of the equals sign:

    res = CubeRoot(42)
				

But they can also be used as part of any statement that takes a value:

    If IsArray(Selection) Then MsgBox("You selected multiple cells.")

Simple expressions can be combined to form complex ones using operators . Visual Basic provides different sets of operators depending on the type of the expression, as listed in Table 1.

Table 1. Visual Basic operators

Numeric operators	Comparison operators (return Boolean values)	Logical operators	String operators	Object operators
^	=	And	&	Is
−	<>	Eqv	Like	Set (assign)
*	<	Imp	= (assign)
/	>	Not
\	<=	Or
Mod	>=	Xor
+
= (assign)

The Like and Is operators in Table 2-9 return Boolean values, but I group them with the String and Object types because they operate on those types exclusively.

Expressions are often the result of a function call. There are many built-in functions for working with numbers, dates, times, strings, and objects in Visual Basic. 

2. Exceptions

Exceptions are runtime errors that you anticipate. Generally, exceptions occur as the result of using resources outside of Excel, such as trying to get a file from disk or trying to connect to a database. It's very difficult to anticipate all the things that could go wrong and unnecessarily complex to try to write code for situations that may occur only rarely. That's what exception handling is for.

In Visual Basic, you turn on exception handling using the On Error statement. On Error tells Visual Basic to watch for exceptions and provides instructions on what to do if one happens. For example, the following code starts Notepad from Excel; if Notepad isn't installed or can't be found, Excel displays a warning message:

    Sub ShowNotepad( )
        On Error Resume Next
        Shell "notepad.exe", vbNormalFocus
        If Err Then MsgBox "Notepad could not be found.", , "Warning"
    End Sub

This style of exception handling is called inline because On Error Resume Next tells Visual Basic to execute the rest of the procedure one line at a time even if a problem occurs. With this technique you typically test the value of Err after each line that you think might cause an exception.

Err returns an Error object if an error occurred. The Error object's default property is the error code number, and the preceding code simply tests if that value is not zero (False). In some cases you may want to test the value within a Select Case statement. On Error provides an alternate syntax that causes any error to jump to a generalized exception-handling routine, as shown here:

    Sub GetFile( )
        Dim str As String
        On Error GoTo errGetFile
        ' Open a file
        Open "a:\datafile.txt" For Input As #1
        Exit Sub
    ' Handle possible exceptions
    errGetFile:
        Select Case Err
            Case 53
                str = "File not found. Insert data disk in drive A."
            Case 55
                str = "File in use by another application. " & _
                  "Close the file and retry."
            Case 71
                str = "Insert disk in drive A."
            Case Else
                str = Err.Description
        End Select
        MsgBox str, , "Error"
    End Sub

If an exception occurs in the preceding code, execution jumps to the errGetFile label and the Select statement sets the message to display based on the error code. You must include an Exit statement before the label to prevent the procedure from displaying a message if no exception occurs.

This style of exception handling allows you provide specific responses to different types of exceptions, but as a practical matter it isn't as useful as inline exception handling since knowing the line where an exception occurred is usually more informative than the error code.

It can be difficult to debug procedures when exception handling is turned on, since exceptions don't immediately stop the code and display a message.

To turn off exception handling within a procedure, use this statement:

    On Error Goto 0

You should turn off exception handling after you've completed the statements you think might cause an exception. Visual Basic automatically turns off exception handling after the procedure completes, so you need to worry about this only if your procedure calls other procedures or if you have a very long procedure.