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.
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:
Private keyword hides functions from the formula bar).
Return a value (that is, it must be a
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.
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:
Display the value of a simple variable or property when you move the cursor over the item after stopping at a breakpoint.
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.
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.
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.
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.