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