Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Hybrid VBA/VSTO Solutions

If you want to convert an existing VBA application to a VSTO solution, the only supported way is to do it all in one go, as we did to create the Paste Special Bar VSTO add-in. For any nontrivial application, that is a huge undertaking and is certain to introduce bugs, with very little clearly identifiable benefit. If all you want to do is to make use of some VB.NET features (such as consuming Web services), a lower-risk and more manageable approach is to expose the .NET classes as COM objects and reference them from VBA.

If you still want to press ahead and convert your VBA application to VSTO, it can be done on a piecemeal basis by creating a hybrid VBA/VSTO solution. Hybrid solutions are officially unsupported by Microsoft, but we believe they provide by far the best approach to migrating existing VBA applications to VB.NET.

In a hybrid solution, a workbook both contains some VBA code and links to a VSTO assembly. In the VSTO assembly's Workbook_Open event, we use Application.Run to call a procedure in the VBA project, passing in a reference to a VB.NET class, which the VBA procedure stores in a global variable. Any procedures added to the VB.NET class can then be called directly from VBA, and any procedures added to VBA modules can be called from VB.NET using Application.Run. Listing 22-9 shows a simple example that displays messages to show where code is being executed. The sample can be found on the CD in the \Concepts\Ch22Using VB.NET and the Visual Studio Tools for Office\HybridWorkbook folder. When adding the CMigratedCode module to the project, we chose to add it as a COM Class. That enables us to add a reference to it in our VBA Project and make use of early binding and the IntelliSense lists.

Listing 22-9. A Hybrid VBA/VSTO Workbook

'
'VB.NET Module MGlobals
'
'Module containing declarations of global variables
Option Explicit On
Module MGlobals
'The Excel Application event-handler class
Friend ExcelApp As CExcelApp
'The workbook event-handler class
Friend ThisWorkbook As CThisWorkbook
'Hold open the MigratedCode class
Public gclsMigratedCode As CMigratedCode
End Module
'
'In the CThisWorkbook VB.NET Class
'
Private Sub Workbook_Open() Handles Workbook.Open
MsgBox("VSTO Workbook_Open Start")
'Create a new instance of the MigratedCode class
gclsMigratedCode = New CMigratedCode
'Call a VBA procedure, passing a reference to the
'class containing the migrated code
Try
ExcelApp.Application.Run("'" & Workbook.Name & _
"'!SetVSTOClass", gclsMigratedCode)
Catch ex As Exception
End Try
MsgBox("VSTO Workbook_Open End")
End Sub
'
'The CMigratedCode VB.NET Class
'
<ComClass(CMigratedCode.ClassId, _
CMigratedCode.InterfaceId, _
CMigratedCode.EventsId)> _
Public Class CMigratedCode
#Region "COM GUIDs"
' These  GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = _
"AB1B282E-F015-4FF4-B4C7-DAC57A318867"
Public Const InterfaceId As String = _
"CEBA2C5D-D286-43E0-B8D5-A5C4525C6301"
Public Const EventsId As String = _
"856AE29A-455C-4693-93CB-C51D5CBDA2CE"
#End Region
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
'Example procedure called from VBA
Public Sub VSTOProc(ByVal sText As String)
MsgBox("In VSTO Procedure. Message is:" & vbLf & sText)
End Sub
End Class

'
'In a standard VBA module, with a project reference set to
'HybridWorkbook
'
'Reference to the class containing the migrated code
Dim gclsMigrated As HybridWorkbook.CMigratedCode
'Called by VSTO, giving us the class containing the
'migrated code
Public Sub SetVSTOClass(clsMigrated As Object)
'Store a reference to the VSTO class
Set gclsMigrated = clsMigrated
MsgBox "Now in VBA!"
'Call a procedure in the VSTO class
gclsMigrated.VSTOProc "Passed from VBA"
MsgBox "Back in VBA!"
End Sub

When the workbook is opened, we get the following messages displayed:

VSTO Workbook_Open Start

Now in VBA!

In VSTO Procedure. Message is: Passed from VBA

Back in VBA!

VSTO Workbook_Open End

After the plumbing for a hybrid VBA/VSTO application has been set up using this mechanism, we can migrate our applications at our own pace, on a gradual procedure-by-procedure basis, starting with the simplest procedures and those with most to gain from being converted. As our experience and confidence increases, we can migrate larger and more complex procedures until our entire application is in managed code.