Programming in Excel

Many solutions developers use Microsoft Excel as a development platform because of its rich environment. You can take advantage of Excel's powerful calculation engine, charting capabilities, customizable task panes, and extensive object model. VSTO enables rapid application development of Excel solutions by enhancing many of Excel's objects, enabling quick development of smart tags and customized actions panes, supporting Windows Forms controls on the worksheet, and providing a robust programming environment: Visual Studio.

Excel solutions created with VSTO are document-level, and this means that the customization is available only within the document that the code is associated with. The second edition of VSTO (VSTO 2005 SE) enables you to create application-level add-ins for Excel. You'll learn about the features of VSTO 2005 SE in Chapter 14. The remainder of this chapter describes document-level customizations of Excel using VSTO.

In the same way that you access the objects in the Excel object model using VBA, you can access these objects using VSTO. However, because these objects are exposed through Excel's primary interop assembly (Microsoft.Office.Interop.Excel.dll), there are some differences in the way you reference them. For example, using VBA, you can access the Range object directly. Using VSTO, you must fully qualify the Range object using Excel.Range.

Listing 7.1 shows the difference between using VBA and Visual Basic 2005 to add a formula to a range of cells in Excel. The VSTO code assumes that you're writing the code in the Sheet1 class, and it uses the Me keyword to represent the Sheet1 class. This technique gives you access to all the objects on the worksheet.

Listing 7.1. Accessing a Range using VBA versus using VSTO ' VBA

Dim myRange As Range

Set myRange = Worksheets("Sheet1").Range("A1:B4") myRange.Formula = "=RAND()"

' Visual Basic 2005 Dim myRange As Excel.Range myRange = Me.Range("A1:B4") myRange.Formula = "=RAND()"

Many VBA objects return a Variant data type, which translates to an Object data type in Visual Basic 2005. Often, you must cast the object to the specific data type to view all its methods, properties, and events using the IntelliSense features in Visual Basic 2005. Other differences include the need to fully qualify the data type for variables, and improved error handling. For example, Listing 7.2 shows VBA code that can be used to add random values to a selected range of cells. This example uses Excel's Input box to enable users to select the range.

Listing 7.2. Using VBA to add values to selected cells ' VBA

Sub AddText()

Dim SelectedRange On Error GoTo CancelButton

Set SelectedRange = Application.InputBox( _

Prompt:="Select the cell or cells to " & _ "add random values.", _ Title:="Random Values", _ Default:=Selection.Address, Type:=8)

SelectedRange.Value = "=Rand()"

SelectedRange.BorderAround xlDashDotDot, xlMedium, _ xlColorIndexAutomatic

CancelButton:

' User canceled dialog box.

End Sub

To make the code work correctly in VSTO, you need to do the following:

• Use a Try Catch statement for handling errors instead of using an On Error GoTo statement. Although the latter type of error handling still works in VSTO, it is better to use Try Catch statements in managed code.

• Remove the Set statement, because it is not supported in Visual Basic 2005.

• Specify the data type for the declaration of the variable SelectedRange. You can put the declaration and assignment in the same statement.

• Qualify the Selection property with the Application object. To access the Address property of the returned Range object, you must cast the Selection to a Range.

• Add parentheses to the BorderAround method in order to pass parameters.

• Fully qualify the enumerations that are passed to the BorderAround method.

Listing 7.3 shows what this code would look like in Visual Basic 2005. To learn more about some of the language differences between VBA and Visual Basic 2005, see Chapter 4.

Listing 7.3. Using Visual Basic 2005 to add values to selected cells in a VSTO solution

Sub AddText() Try

Dim SelectedRange As Excel.Range = _ Application.InputBox(Prompt:= _

"Select the cell or cells to add random values.", _ Title:="Random Values", Default:=CType(Application. _ Selection, Excel.Range).Address, Type:=8)

SelectedRange.Value = "=Rand()"

SelectedRange.BorderAround(Excel.XlLineStyle.xlDashDotDot, _ Excel.XlBorderWeight.xlMedium, _ Excel.XlColorIndex.xlColorIndexAutomatic)

Catch ex As InvalidCastException

' Inputbox returned a Boolean (user canceled dialog box).

End Try

When you press F5 to run this code, an Excel Input box appears and lets you select a range of cells. As you select cells on the worksheet, the range appears in the Random Values dialog box, as shown in Figure 7.1.

A

B

C

D

E

F

G

H

I

J

K

L

r

1

2

3

4

5

6

7

B

9

10

x|

11

12

Select the cell or cells to add random vaines.

13

14

15

16

|ÎBt2:tFÎ14

17

18

I » I

ill

19

Cancel

20

21

22

23

24

25

26

Figure 7.1. Selecting the range of cells to hold the random values

Figure 7.1. Selecting the range of cells to hold the random values

When you click OK on the Input box, the random values are added to the selected cells, and a border is added around the range, as shown in Figure 7.2.

0 0

Post a comment