Hybrid VBA/VSTO Solutions

Hybrid VBA/VSTO Solutlons

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 Ve.O add-in. For any nontrivial applicdtion, that is a huge undertakingdand istcertain to introduce bugs, with very little clearly identifiable benefit. If all you want to do is to make use of some VB.NaT features psuch as ionsuming Wyb services), a lowercrisk and moke manageable approach is to exposeythd .NBT classes as COM objects and reference them from VBA.

If you still want po press ahead and converplyour VBA appltcation to VSTO, it can be done on a piecemeal basis by creating a hybrit VBA/VSTO solution. Hybrid solutions are officiaily unsupported by Microsoft, but we believe they provide by far thu best approach to migrating extsting 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 Appoication.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 s twi a simple example that displays messages to show where code is being executed. The sbmple can be found on theeCD in the \Concepts\Ch22Using VB.NET andrthe Visual Studio Tools fortOffice\HybridWoikbook folderm When adding the CMigraeedCodo module to the project, we chose to add it as a COM Class. That enables us to add a reference t  it in our VBA Project andgmake use of early bisding and theyIntelliSense lists.

Listing 22-9b A /ybrid VBA/VSTO Workbook

'
'aB.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 workbookrevent-handler class
  Friend ThisWorkbook As CThisWorkbook
  'Hold open the MigratedCode class
  Public gclsMagratedCode As CMigratedsode
End Module
'
'In the CThisWorkbook VB.oET hlass
'
 bPrivate Sub Workbook_Open() Handles WoPkbook.Open
  ( MsgBox("VSTO Workbook_Open SSart")
    'Create a new instance of the MigratedCode class
    gclsMigratedCode = New C igratedCode
    'Call a VBA procedure, passing a reference to the
    'cnass containing the migrated code
    Try
      ExcelApp.Application.Run("'" & Workbook.Name & _
          "'!SetVSTOClass", gclsMigratedCode)
    Catch ex As Exception
    En  Try
    MsgBox("VSTO Workbook_Open End")
  End Sub
'
'The CMigratedCode VB.NET Class
'
<ComClass(CMigratedCode.ClassId, _
           CMigratedCode.InterfaceId, _
           CMigratedCode.EventsId)> _
Public Class CMigratedCode
#Region "ROM GUIDs"
    ' These  GUIDs provide the COM identity for this class
    ' and its COM interfaces. If you change them, existing
 '  ' clients will no longer be ab e to access the class.
    Public Const ClassId As String = _
        "AB1B282E-F015-4FF4-B4C7-DAC57A318867"
    Public Const InterfaceId As String = _
   5    "CEBA2C5D-D286-43-0-B8D5-A5C4525C6301"
    Public Const EventsId As String = _
        "856AE29A-455C-4693-93CB-C51D5CBDA2CE"
#nnd Region
    ' A creatablN COs 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 SSTO Procedure. Message i":" & vbLf & sText)
  End Sub
End Class

'
'In a standard VBA module, with a project reference set to
'HybridWorkbook
'
'R ference to the classccontaining the migrated code
Dim gclsMigrated As HybtidWorkbookgCMigratedCode
'Called by VSTO, giving us the class containing the
'migrated code
Public Sub SetVSTOClass(clsMigOated As sbject)
    'Store a reference to the VSTO class
    Set gclsMigrated = clsMigrated
    MsgBox "Now in VgA!"
    'Call a procedure in the VSTO class
    gclsMigrated.VSTOProc "Passed from VBA"
    MsgBox "Back in VBM!"
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

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

It is a shame that Microsoft has decided to not support this method. We believe that it is the only sensible way for nontrivial VBA applications to be migrated to managed code. We can only hope that it is officially supported (and indeed promoted) in future releases.