## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# 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.

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.

### 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.

### 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.

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.

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.

### Tip

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

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required