VSTO: Using C # to develop Excel, Word "11"

Programmatic user-defined features
Excel can create user-defined functions that can be used in Excel formulas. Developers must create a special type of DLL called XLL. Excel also allows you to write custom functions in VBA that you can use in Excel formulas. Unfortunately, Excel does not support or recommend writing an XLL that uses managed code.

Build a managed Automation Add-in that provides user-defined functions
Fortunately, there is an easier way to create a user-defined function that does not need to create an XLL. Excel 2003 supports custom technologies called Automation Add-ons that can be easily created in C # or Visual Basic.

First, start Visual Studio and create a new C # class library project. Name the project Automationaddin. In the Class1.cs file that you created for you in the new project, enter the code shown in Listing 3-1. The code defines a class named Myfunctions, which implements a function called Multiplyntimes. We will use this function as a custom formula. Our classes also implement Registerfunction and Unregisterfunction, which are attributed to the ComRegisterFunction property and the ComUnregisterFunction attribute, respectively. When COM assemblies are registered as COM interop, Registerfunction is called. Unregisterfunction will be called when the assembly is not registered for COM interop. These features place a necessary key in the registry that allows Excel to know that the class can be used as an Automation add-in.

Listing 3-1 A C # class is called MyFunction, which exposes a user-defined function multiplication

usingSystem;usingSystem.Runtime.InteropServices;usingMicrosoft.Win32;namespaceautomationaddin{[ClassInterface (classinterfacetype.autodual)] Public classmyfunctions { Publicmyfunctions () {} Public DoubleMultiplyntimes (DoubleNumber1,DoubleNumber2,Doubletimestomultiply) {      Doubleresult =Number1;  for(Doublei =0; i < timestomultiply; i++) {result= result *number2; }      returnresult; } [ComRegisterFunctionAttribute] Public Static voidregisterfunction (Type type) {Registry.ClassesRoot.CreateSubKey (Getsubkeyname (type)); } [Comunregisterfunctionattribute] Public Static voidunregisterfunction (Type type) {Registry.ClassesRoot.DeleteSubKey (Getsubkeyname (type),false); }    Private Static stringgetsubkeyname (Type type) {System.Text.StringBuilder s=NewSystem.Text.StringBuilder (); S.append (@"clsid\{"); S.append (type. Guid. ToString ().      ToUpper ()); S.append (@"}\programmable"); returns.tostring (); }  }}

With this code, you need to modify the project to automatically register the class when building COM Interop. First, display the properties of the project by double-clicking the Properties node under the project node in Solution Explorer. In the property designer that appears, click the Build tab, and then select the Register COM Interop check box, as shown in 3-7. Then choose Build Solution from the Build menu to build the class library project. Your actions will cause your class library project to be built and registered as an Automation Add-in in the registry. Excel can now see your C # class and use it.

Figure 3-7 Setting build options to register COM interop

Using managed Automation Add-ons in Excel
Start Excel and choose Add-ons from the Tools menu to display the Add-ins dialog box. In the Add-Ins dialog box, click the Automation button. You can find the class that you created by locating automationaddin.myfunctions in the Automation server list, as shown in 3-8.

Figure 3-8 Selecting Automationaddin.myfunction from the Automation Server dialog box

Click OK in this dialog box, and you have added the Automationaddin.myfunctions class to the list of installed Automation Add-ins, as shown in 3-9.

Figure 3-9 Automationaddin.myfunction installed

Now, try using the function multiplyntimes in the Excel formula. Start by creating a simple spreadsheet that contains a number, a second number multiplied by the first number, and a third number that multiplies the first number by the number of the second. shown in spreadsheet 3-10.

Figure 3-10 A simple spreadsheet to test the custom formula

Click the empty cell in the workbook below the number, and then click the Insert Function button (the button with the FX tab) in the formula bar. From the Available Formulas dialog box, drop down or select a Category drop-down box, and then select Automationaddin.myfunction. Then click the "Multiplyntimes" feature, shown in 3-11.

Figure 3-11 Choosing the multiplier from the Insert Function dialog box

When you click the OK button, Excel pops up a dialog box to help you select a function parameter from a cell in the spreadsheet, as shown in 3-12.

Figure 3-12 Setting function parameters

After selecting the feature parameter from the corresponding cell, click OK to create the custom formula in cell C5, which is the final spreadsheet, shown in 3-13.

Figure 3-13 the final spreadsheet

Some other user-defined functions
You can try additional features that you can use in Excel formulas. For example, listing 3-2 shows several other functions that you can add to the Myfunctions class. To use listing 3-2, you must add a reference to the Excel 11.0 Object Library and add the code to the top of the class file using Excel = Microsoft.off-ice.interop.excel. Note that when you declare a parameter as an object, Excel passes a Range object. Also note that the AddNumbers function supports optional parameters. When a parameter is omitted, System.Type.Missing is passed as the value of the parameter.

Listing 3-2 additional user-defined functions that can be added to the Myfunctions class

 Public stringGetstars (DoubleNumber ) {System.Text.StringBuilder s=NewSystem.Text.StringBuilder (); S.append ('*', number); returns.tostring ();} Public DoubleAddNumbers (DoubleNumber1, [Optional]ObjectNumber2, [Optional]ObjectNumber3) {  Doubleresult =Number1; if(Number2! =System.Type.Missing) {excel.range R2= Number2 asExcel.Range; DoubleD2 =convert.todouble (R2.    Value2); Result+=D2; }  if(Number3! =System.Type.Missing) {excel.range R3= Number3 asExcel.Range; DoubleD3 =convert.todouble (R3.    Value2); Result+=D3; }  returnresult;} Public DoubleCalculateArea (Objectrange) {Excel.Range R= Range asExcel.Range; returnConvert.todouble (r.width) +convert.todouble (r.height);} Public DoubleNumberofcells (Objectrange) {Excel.Range R= Range asExcel.Range; returnR.cells.count;} Public stringtoUpperCase (stringinput) {  returninput. ToUpper ();}

Debug user-defined functions in an administrative automation add-in
You can debug a C # class library project as an Automation Add-in by setting Excel as a program that your class library project starts when you debug. Display the properties of the project by double-clicking the Properties node under the project node in Solution Explorer. In the property designer that appears, click the Debug tab, and in the Start External program text box, type the full path of Excel.exe, as shown in 3-14. Now, set breakpoints on one of your user features, press F5, and then use the features in the spreadsheet. The debugger stops the user function that executes the breakpoint setting.

Figure 3-14 Setting debug options to start Excel

Deploy managed Automation Add-ons
To deploy an Automation Add-in, right-click the solution in Solution Explorer and choose New Project from the Add menu. From the Add New Project dialog box, in the Project types tree, select Setup Project from other project types \ Install and deploy.

Right-click the installation project that you added in Solution Explorer, and then select Project Output from the Add menu. From the Add Project Output Group dialog box, select the Automationaddin project, and select Primary output, as shown in 3-15.

Figure 3-15 Adding the main output of the Automation AddIn project to the Setup project

Because we tell the project to register our managed objects for COM interop, the Setup project should already be set up correctly so that COM interop managed objects are registered at installation time. To verify this, click the main output of the Automationaddin node in the Setup project. In the main output's Properties window (our C#dll), make sure that register is set to vsdrpCOM.

Introduction to the Excel object model
Whether you choose to integrate code with Excel, you eventually need to communicate with the Excel object model to complete the task. It is not possible to fully describe the Excel object model in this book, but we try to familiarize you with the most important objects in the Excel object model and display the most commonly used methods, properties, and events on those objects.

Object hierarchy
The first step in learning the Excel object model is to get the basic structure of the object model hierarchy. Figure 3-16 shows the most critical objects and their hierarchical relationships in the Excel object model.

Figure 3-16 Basic hierarchy of the Excel object model

A Workbook object has a collection named sheets. The Tables collection can contain objects of the sheet or chart type. A chart is sometimes called a chart because it covers the entire area that the worksheet will cover. You can insert a worksheet into a workbook by right-clicking the sheet tab in the lower-left corner of the Excel workbook and selecting Insert. Figure 3-17 Shows the dialog box that appears. Note that there are two additional objects in the Sheets collection: the MS Excel 4.0 macro table and the MS Excel 5.0 dialog box. If you insert a macro form or dialog box into an Excel workbook, you treat it as a special type of worksheet instead of a special object model type that corresponds to a macro table or dialog box.

Figure 3-17 Inserting various worksheets into an Excel workbook

Because workbooks can contain these various objects, Excel provides multiple collections from the Workbook object. The Worksheets collection contains only worksheet objects in the workbook. The chart collection contains only the charts in the workbook. The Sheets collection is a mixed collection of both. The Sheets collection returns the members of the collection as type objects, and you must convert the returned objects to worksheets or charts. In this book, when we talk about an object that might be a worksheet or a chart, we call it a worksheet.

Figure 3-18 shows a more complete hierarchical tree whose main objects are associated with the objects in Figure 3-16. This will begin to give you an idea of the broad hierarchy of objects in the Excel object model, especially if you realize that the graph shows less than half of the available objects. Objects that appear in gray come from the Microsoft.Office.Core namespace, which is associated with the Microsoft Office 11.0 PIA (Office.dll). These objects are shared by all Office applications.

Figure 3-18 Detailed hierarchy of some major objects in the Excel object model

Figure 3-19 shows the object hierarchy associated with range, which is an important object in Excel that represents the range of cells you want to work with in your code. We have used the Rangxe object in Listing 3-2.

Figure 3-19 A more detailed hierarchy of objects associated with a range in the Excel object model

Figure 3-20 shows the object hierarchy associated with Shapea shape, which represents something that floats on a worksheet that is not a cell, such as an embedded button, a graphic, a comment bubble, and so on.

Figure 3-20 More detailed hierarchy of the objects associated with shape in the Excel object model

Conclusion
This chapter describes the various ways to integrate code into Excel. This chapter describes how to build an Automation add-in to create user-defined functions for Excel. You also learned the basic hierarchy of the Excel object model. The 4th chapter, "Using Excel Events," discusses events in the Excel object model. The 5th chapter, "Using Excel Objects", covers the most important objects in the Excel object model.

VSTO: Using C # to develop Excel, Word "11"