Programming Excel with VBA and .NET

View Results

There are a number of ways to display results from code in Excel. One common way that is used a lot in Help is to display a message box:

    Sub ShowMessage( )
        Dim x As Integer
        x = Sheets.Count
        MsgBox "This workbook contains " & x & " sheets."
    End Sub

This code displays the number of sheets in the workbook using a simple dialog box as shown in Figure 1-18.

Figure 1-18. It’s easy to display results using MsgBox

But that’s not the same as getting data into a worksheet, which is more commonly what you want to do. To do that, you set the value of a Range object. For example:

    Sub ChangeRange( )
        Dim x As Double
        x = InputBox("Enter a number.")
        Range("J5") = x ^ (1 / 3)
    End Sub

That code gets a number from the user and displays the cube root of that number in cell J5. As mentioned previously, it’s not a good idea to use range addresses in code so the following version uses a named range instead of an address:

    Sub ChangeRange( )
        Dim x As Double
        x = InputBox("Enter a number.")
        Range("targetRange") = x ^ (1 / 3)
    End Sub

To name a range in Excel, select the range (in this case cell J5) and type the name in the Name box as shown in Figure 1-19.

Figure 1-19. It’s better to use named ranges in code

Tip

To see all of the named ranges in a workbook, choose Insert → Name → Define.

You can even display results in a range using the formula bar if your procedure returns a value. The following code shows changes to make to repackage the cube root calculation for use in a formula:

    Public Function CubeRoot(x As Double) As Double
        CubeRoot = x ^ (1 / 3)
    End Function

In order to use a procedure in a formula, the procedure must:

• Not be Private (the Private keyword hides functions from the formula bar).

• Return a value (that is, it must be a Function)

• Be part of a module, not a class

If the procedure follows those rules, you can enter its name in the formula bar as shown in Figure 1-20.

Figure 1-20. You can use public functions in formulas

Tip

Visual Basic procedures that can be used in the formula bar are sometimes called user-defined functions, or UDFs for short.

In other cases, you might want to view a result, but not show that result to users. A good example of this is when you’re developing your code or when you’re making sure it works correctly. In that situation, you usually set a breakpoint in your code, then view the values in variables using watches . There are three kinds of watches in Visual Basic, and none of them go ticktock:

Automatic watches

Display the value of a simple variable or property when you move the cursor over the item after stopping at a breakpoint.

Quick watches

Display the value of a variable or property when you select the item and press Shift-F9. Quick watches can display returned values, such as TypeName(ActiveSheet), which automatic watches can’t.

Watch points

Display the value of a variable or property in the Watch window. Watch points can also stop code if an item reaches a certain value. In that way, they function as conditional breakpoints.

Figures 1-21 through 1-23 show the different types of watches in action.

Figure 1-21. Automatic watches display simple values

Figure 1-22. Select an item and press Shift-F9 to see a quick watch

Figure 1-23. Select an item and choose Debug → Add Watch to display the value of that item in the watch window

Watches are the best way to look at a value at one point in time, but when you need to track how a value changes, they are kind of limited. In those situations, it’s often best to display your results in the Immediate window using the Debug.Print statement. An easy way to illustrate this is to go back to the runtime error sample we showed earlier that causes an infinite recursion. I’ve made some changes (in bold) to show how to track how many levels deep the recursion goes before failing:

    Sub EasyRTErrors( )
        ' Previous code deleted for this example.
        Static i
        i = i + 1
        ' Show how many times recusion will run before error.
        Debug.Print i
        ' Infinite recursion, stack overflow:
        EasyRTErrors
    End Sub

Now, if you run this code, a stream of numbers will display in the Immediate window (Figure 1-24). If you don’t see the Immediate window in VBE, press Ctrl-G to redisplay it.

Figure 1-24. Use Debug.Print to display results in the Immediate window

You can also use the Immediate window to run procedures and perform quick calculations. In effect, it functions as a single-line Visual Basic interpreter as shown in Figure 1-25.

Figure 1-25. Type statements in the Immediate window to see their result, err...immediately

Tip

The ? character is a shortcut for Print in Visual Basic.