## Creating an Interop Excel Application

To create the sample Excel application, which calculates the distance between two points in two-dimensional space, you will use a user defined function written in VBA.

1. Open a new Excel document, and save it as CalculateDistance.xls to a location of your choice.

2. Create the spreadsheet shown in Figure 12.8.

 [Q Microsoft Extc I - CakulatcDtstanccjds -JO X Fie tit tfBfr Insert Fgfmat i J J J i J d JI "> - I®» Z - Ii Ü 100% ^ # B ^ New List ^ Get Work Items i^Pubtsh ■ Refresh Configure List D j M B DJ f* I 1 1 A G C □ E F 1 G T X Y z _1 2 Point 1 10 15 20 3 Pcinl 2 20 30 40 4 L _] 5 DisOnee2D 18.02776 6 DisOneeSD 26 32532 7 3 E •w In i > n\Eheetl/SheetS /Sheets / Li 1 'li Rwdy /a

Figure 12.8. Calculating the distance between two points

Figure 12.8. Calculating the distance between two points

3. Select cell B5 and enter the formula to call the UDF that you will create in the next section.

=VBA_CalculateDistance2D(B2,C2,B3,C3)

4. Select cell B6 and enter the formula to call the UDF that you will create in the next section.

=VSTO_CalculateDistance3D(B2,C2,D2,B3,C3,D3)

### Creating the User Defined Functions in VBA

Because Excel supports UDFs only in VBA (and automation DLLs, a topic outside the scope of this book), you must create your UDFs in a VBA module. You will also use this module to call your VSTO functions.

1. Press ALT+F11 to open the VBA Editor.

2. Right-click the Modules node under VBA Projects (CalculateDistance.xls), and choose Insert | Module from the context menu. This action creates a new module with the default name Module1.

3. Add the code in Listing 12.9 to Module1.

Listing 12.9. Creating UDFs and implementing VSTO interop in VBA Public Class MainForm

Dim VSTOFunctions As Object

' VSTO will call this function to connect to VBA. Public Sub RegisterVSTOFunctions(VSTOFunctionsReference _ As Object)

Set VSTOFunctions = VSTOFunctionsReference End Sub

Public Function VBA_CalculateDistance2D( _ X1 As Integer, Y1 As Integer, _ X2 As Integer, Y2 As Integer) As Double

Dim Distance2D As Double

Distance2D = Sqr((X2 - X1) A 2 + (Y2 - Y1) A 2) VBA_CalculateDistance2D = Distance2D End Function

' Calculate the 3D distance in VSTO. Public Function VSTO_CalculateDistance3D( _

X1 As Integer, Y1 As Integer, Z1 As Integer, _ X2 As Integer, Y2 As Integer, Z2 As Integer) _ As Double

Dim Distance3D As Double

Distance3D = VSTOFunctions.CalculateDistance3D( _ X1, Y1, Z1, X2, Y2, Z2)

VSTO_CalculateDistance3D = Distance3D

End Function

### End Class

The first thing we add to the module is a variable called VSTOFunctions, which will hold a reference to the VSTO object. It is passed when VSTO calls the RegisterVSTOFunctions subroutine during the document's Open event. When you have a reference to this object, you can call public methods on this object from VBA. Note that VBA uses late binding to call the methods, so you do not see IntelliSense for this object.

In this example, you have created two UDFs. The first of these, VBA_CalculateDistance2D, is implemented in VBA. The other UDF, VSTO_CalculateDistance3D, is a pass-through to the VSTO function CalculateDistance3D. These functions complete the VBA side of the interop.

Note that the VBA functions are wrappers around the VSTO functions, allowing you to create managed UDFs. Now let's look at the VSTO side.

### Creating a VSTO Excel Project

You now need to create a VSTO project that uses the CalculateDistance.xls file that you built earlier. Create a new Excel Workbook project. In the Create New Project Wizard, select Copy An Existing Document. Browse to and select CalculateDistance.xls.

Creating the VSTOFunctions Class

You will now create a class called VSTOFunctions. This class contains the functions that will be called by VBA.

VSTOFunctions.vb.

2. Add the code in Listing 12.10.

Listing 12.10. Creating the VSTOFunctions class

<System.Runtime.InteropServices.ComVisible(True)> _ Public Class VSTOFunctions

Public Function CalculateDistance3D( _ ByVal X1 As Integer, ByVal Y1 As Integer, _

ByVal Z1 As Integer, ByVal X2 As Integer, _ ByVal Y2 As Integer, ByVal Z2 As Integer) _ As Double

Dim Distance3D As Double

'This is 2D so call CalculateDistance2D (in VBA). Return CalculateDistance2D(X1, Y1, X2, Y2) End If

' Calculate the 3D distance.

Distance3D = Math.Sqrt((X2 - X1) A 2 + (Y2 - Y1) A 2 + _ (Z2 - Z1) A 2)

Return Distance3D

End Function

' Call the VBA function.

Public Function CalculateDistance2D( _

ByVal X1 As Integer, ByVal Y1 As Integer, _

ByVal X2 As Integer, ByVal Y2 As Integer) As Double

' Use the existing CalculateDistance2D written in VBA. Return Globals.ThisWorkbook.Application.Run( _ "VBA_CalculateDistance2D", X1, Y1, X2, Y2) End Function End Class

The first thing you add to the class is an attribute called ComVisible, whose value you set to True. This attribute allows you to pass this class to VBA. Next, you create two functions. The CalculateDistance3D function is implemented in Visual Basic 2005 and is called from the VBA VSTO_CalculateDistance3D function you created earlier. The CalculateDistance2D function calls VBA_CalculateDistance2D in VBA. The CalculateDistance2D function is called by the CalculateDistance3D function if the values of Z1 and Z2 are zero.

This example demonstrates calling VSTO from VBA, and calling VBA from VSTO. The final step is to connect VSTO and VBA when the workbook opens.

### Connecting VBA and VSTO When the Workbook Opens

When the workbook opens, you call the RegisterVSTOFunctions subroutine in VBA, passing a reference to an instance of the VSTOFunctions class you created earlier.

1. Add the code in Listing 12.11 to the ThisWorkbook.vb class.

Listing 12.11. Calling the RegisterVSTOFunctions subroutine in VBA when the workbook opens

Public Class ThisWorkbook

Private Sub ThisWorkbook_Open() Handles Me.Open ' Connect VSTO and VBA.

Me.Application.Run("RegisterVSTOFunctions", _ New VSTOFunctions)

Private Sub ThisWorkbook_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup

End Sub

Private Sub ThisWorkbook_Shutdown(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Shutdown

End Sub

End Class

2. Press F5 to run the VSTO solution.

3. When the workbook opens, type some values for the two points. You may need to press CTRL+ALT+F9 to get Excel to recalculate the formulas.

As you have seen, implementing VBA and VSTO interoperability is not very complicated. This technique can be a powerful part of your migration strategy because it lets you migrate individual functions to VSTO one at a time.

0 0