Interop Between VBA and Visual Studio Office Solutions (VSTO)

Interop Between VBA and Visual Studio Office Solutions (VSTO)

Rate This
  • Comments 8

Couple weeks ago when I was in Holland speaking at SDC an attendee asked me how he could call methods in an Office solution (VSTO) from VBA functions defined in a document and vice versa. I thought I’d follow up with a post on how to do this, but first a little background on why this architecture would make sense.

There are many reasons why you would build an Office solution using Visual Studio (VSTO) as opposed to a pure VBA solution. Andri Yadi, VSTO MVP, wrote a great piece on his blog a while back explaining the benefits of VSTO compared to VBA. He broke it down into 10 areas, of which the main benefits are the tools and designers you have available in Visual Studio as well as the entire .NET framework and modern languages at your disposal.

However, there are probably many VBA assets that people in your company have already written, like complex algorithms or other business logic that you really don’t want to rewrite. Or maybe you still want to allow users to customize these functions in the VBA editor but it’s necessary for you to call them from your .NET code.

Likewise, you may want to develop a customization that takes advantage of WCF services or a WPF UI, modern language features, or any other feature of the .NET framework that would be difficult or impossible to do in VBA, and you want the user to be able to access these methods from their VBA functions. The attendee at SDC didn’t go into much detail on what his Office customization was doing exactly but he wanted to make some of his public methods available to his VBA users and this makes sense in a lot of situations. Luckily Visual Studio makes this very easy to do.

Creating an Excel Document Customization

For this example I’ll create an Excel document customization that accesses data through a WCF service and does some calculations on that data. The calculations, however, will be in VBA. To access the remote data over the internet I’ll create an ADO.NET Data Service. I want to pull up data in a Northwind view called Sales Totals By Amount. I’ve shown how to create an ADO.NET Data Service many times before so I won’t go into too much detail here. Please refer to the steps shown in the Using ADO.NET Data Services article. The only difference in this case is I selected the View Sales Totals By Amount into my Entity Framework model when I performed that step.

I have an Excel macro-enabled workbook that already has a simple VBA function that Sums all the columns below the first row. The function is sitting in a module called MyFunctions.

image

To create the new  Excel workbook customization I’m going to add a new project to my solution and select Office 2007 Excel Workbook. Next it will ask if you want to create a new document or use an existing one, here’s where I’ll specify the macro-enabled workbook I already have above.

image

Next Add a Service Reference to the ADO.NET Data Service (which I called NorthwindReportService) just like I showed here and copy the URI into your clipboard. Then create a setting to store the URI, just double-click on My Project (Properties in C#) and select the Settings tab and enter an application scope property called ServiceURI.

image

When you add the service reference this generates client-side proxy types that you can use. I’m going to bind the data returned from Sales_Totals_by_Amount to an Excel ListObject. Open the Data Source window (Data –> Show Data Sources) and then add a new data source (Data –> Add New Data Source…). In the Data Source Connection Wizard select Object, then Next, then expand the types in your project’s NorthwindReportService namespace. Select Sales_Totals_by_Amount and then click Finish and you will see the type’s properties appear in the Data Sources Window:

image

Double-click on Sheet1 in the project and drag the Sales_Totals_by_Amount from the Data Sources window onto the second row of the sheet (our macro is going to sum into the first row so we want to place the data starting on the second row). This will automatically set up a BindingSource in the system tray that we will use to set our list of data coming from the service. If you are familiar with Winforms development this should seem very familiar. The ListObject is the main data object you work with in Excel solutions. For this example I’m going to select the OrderId column, right-click and then Delete. I’ll do the same to the ShippedDate column because I only want to display the CompanyName and SaleAmount for this example. Finally I’ll set the formatting (Home Tab on the Excel Designer) to Currency for the first cell.

image

Now we’re ready to write some code to load our data. Right-click on ThisWorkbook and select View Code. Here I’m going to create a Friend ReadOnly Property so we can easily access the service reference from anywhere in the project. I’m making this Friend so that it won’t be visible outside of the .NET assembly. I’m also creating a Public method that gets the data from our service and optionally accepts a Company Name. The results are then set to the DataSource of the ListObject’s BindingSource on Sheet1:

Imports VBATest.NorthwindReportService

Public Class ThisWorkbook

    Private _ReportService As New NorthwindEntities(New Uri(My.Settings.ServiceURI))
    Friend ReadOnly Property ReportService() As NorthwindEntities
        Get
            Return _ReportService
        End Get
    End Property

    Public Sub GetData(Optional ByVal companyName = "")
        Try
            If Globals.Sheet1 IsNot Nothing Then

                Dim results As IEnumerable(Of Sales_Totals_by_Amount)

                If companyName = "" Then
                    results = Me.ReportService.Sales_Totals_by_Amount
                Else
                    results = From s In Me.ReportService.Sales_Totals_by_Amount _
                              Where s.CompanyName.StartsWith(companyName)
                End If

                Globals.Sheet1.Sales_Totals_by_AmountBindingSource.DataSource = results.ToList()
            End If
        Catch ex As Exception
            'TODO: Error Handling
            MsgBox(ex.ToString())
        End Try
    End Sub

    Private Sub ThisWorkbook_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisWorkbook_Shutdown() Handles Me.Shutdown

    End Sub

End Class

Calling VBA from VSTO

Next I want to create a button on the ribbon that will first call the GetData method, then select the first cell in Sheet1, and finally call the VBA function SumBelow. In order to call a VBA method from VSTO you call Globals.ThisWorkbook.Application.Run passing it the full name to the VBA method. For this example that would be VBATest.xlsm!MyFunctions.SumBelow.

Add a New Item to the project and select Office, Ribbon (Visual Designer) and then drag a Button from the Office Ribbon Controls to the Group and Label it “Get Data”. I also specified an OfficeImageId to make it look pretty. (BTW, a nice way to browse the Office Images is to install the VSTO Power Tools and install the RibbonID Add-in like Ty shows in this video.)

image

Double-click on the Get Data button to add a click event handler and we’ll write the following code to load all the data and then call the VBA function. You need to make sure you set up proper error handling here because if the VBA function is removed or renamed the code here will fail. This code will also fail if the appropriate access is not granted to VBA macros in Excel. By default, VBA macros are not enabled but you can enable them on a per workbook basis (there’s a button at the top of the first sheet when you run it). This scenario assumes you have existing VBA code that has permission to run and you’re now calling those existing functions from VSTO.

Imports VBATest.NorthwindReportService
Imports Microsoft.Office.Tools.Ribbon

Public Class Ribbon1

    Private Sub Button1_Click() Handles Button1.Click
        'load the data from the service
        Globals.ThisWorkbook.GetData()
        Try
            'Make sure the first cell is selected
            Globals.Sheet1.Range("A1").Select()
            'Run the VBA function. This will result in a runtime error if the function 
            ' is removed or renamed or not allowed to run so make sure to provide 
' adequate error handling.
Globals.ThisWorkbook.Application.Run("VBATest.xlsm!MyFunctions.SumBelow") Catch ex As Exception 'Todo: Error handling MsgBox(ex.ToString()) End Try End Sub End Class

Hit F5 to run. If you see a Security Warning (the default) that Macros are disabled, then just click Options and select “Enable this content”. Select the Add-Ins tab on the Ribbon and click the GetData button to see the data get loaded from the service and then the SumBelow VBA function will be called which will auto-sum the SaleAmount field and show the total in the first row.

Calling VSTO methods from VBA

As you can see it’s really easy to call VBA code from your Office solution in Visual Studio (VSTO) but it’s also fragile because of the late-bound architecture and the requirement that macros be enabled for the Workbook. Like all late-bound code, you need to have adequate error handling to prevent crashes. Much less fragile is calling VSTO methods from VBA functions because these methods are compiled into your .NET assembly and exposed via COM-interop which makes them available to VBA.

If we go back to our project and double-click on ThisWorkbook and look in the Properties window, you should see a property called EnableVbaCallers. Setting that Property to True will expose all Public methods in the ThisWorkbook class via COM to VBA.

image

If you now go back into the code-behind for ThisWorkbook you will see some COM attributes added to the class:

<Microsoft.VisualBasic.ComClassAttribute()> _
<System.Runtime.InteropServices.ComVisibleAttribute(True)> _
Public Class ThisWorkbook
...

Now we can call the GetData method from VBA code. Hit F5 to run and enable the macros (if asked) on the Workbook again. Select the Developer tab and launch the Visual Basic editor. (If you don’t see a developer tab click the Office icon – the globe in the left-hand corner – select Excel Options, and then on the Popular tab check the “Show Developer tab in the Ribbon”.)

Double-click on the ThisWorkbook and you will see that Visual Studio added a property to our VBA code for us called CallVSTOAssembly. This allows us to call any public method we defined in the ThisWorkbook class back in Visual Studio. Let’s add another function to the MyFunctions module that collects input from the user on the company name to look up and then fetches the data by calling the GetData method in .NET.

image

Save your code here and close the VBA Editor. Now back on the Developer tab on the Ribbon select Macros and then you should see the one we just wrote called GetDataAndSumBelow, select it and click Run. It will prompt for a company name (just type ‘S’ for instance) and it will run the ADO.NET Data Service query via the call to the .NET GetData method and then will return and call the SumBelow VBA function. Cool!

BUT WAIT… DON’T CLOSE EXCEL YET!

Tips Editing VBA Code when Debugging

Because we wrote the second VBA function above while we were in debug mode in Visual Studio once we close Excel we will lose all the VBA code we wrote when we debug again. imageBecause of the way Visual Studio works with Office solutions, we aren’t actually editing the xlsm file in the project, we’re editing the running xlsm file in the \bin directory that has the VSTO solution attached. You cannot just copy this one in the \bin folder back into the project otherwise Visual Studio will report an error that a customization is already attached to the document when you compile again. So what do we do?

There’s probably other ways to do this but what I found the easiest was to open the Visual Basic editor again, select the MyFunctions module where all my code is stored and then right-click and select “Export File”. This will allow you to save the code outside the Workbook. Then when you debug again you can just import it by right-clicking again (delete the current one first).

When you’re finally satisfied with your VBA – VSTO code interop, close Visual Studio and open the .xlsm file in the project directory (not the \bin) and re-import your code again into that version. Then restart Visual Studio and it will be in there when you start debugging again. I find this easier than copying my code into the clipboard, closing VS, modifying the document, reopening VS every time. Just be aware of what version of the document you’re modifying when you tweak your VBA code and you should be OK.

I’ve uploaded the code for this example onto Code Gallery so have a look: http://code.msdn.microsoft.com/VBAVSTOInterop

More Resources

For more information on VBA – VSTO interop with Visual Studio please check out the following resources:

Enjoy!

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • Very good points, and nice informative piece - but I think MS is really missing it on this one.  I have many clients who ARE NOT programmers, but are very talented at VBA.  They are CPA's and Actuaries - bean counters if you like - and they are truly scared and sweating this change over.  They DONT want to take a few years to learn and stuggle through Visual Studio, and VSTO to them is simply learning VB.NET or C# and they simply dont have the time to "operate two careers" (as one client put it).  So far, all our clients in total are saying things like "if we have to stay with Office 2003 forever, we will" - many feel betrayed!  

    I like VSTO, understand the technology and also understand that things must "move on" - but I think MS needs to do a great deal more than it is doing because out in the field, VSTO is seen as a nightmare and dead-end path to those who know VBA through many years of working it, but see VS/VSTO as way too much to take on and still do their normal jobs.

    I hope MS has some plan of some kind to deal with this or Office is going to suffer greatly - at least in the financial and actuarial markets.  As it is no one wants to touch Office 2007, and when its successor comes out, I fear that will only be avoided all the more.

    MS really needs to get in touch with its marketplace and provide some sort of transition tool, or strategy that is really do-able.  Just forcing this on the market is not going to cut it.

  • I hope all VBA developers could enjoy this post as much as I did Beth. I give it 5/5.

  • Hi AndyF,

    I totally agree, and that's why the article focuses on interop. As a pro developer you can provide folks who are writing VBA code access to methods in the .NET framework.

    As Office and .NET evolve VBA interop is a must-have for migration even if we provide a new environment because there will be so many apps already built that need to be extended. And yes, there are folks here looking into this problem.

    Not sure what you mean about Office 2007, it still supports VBA as does VSTO. And we're not trying to "force" you, we're trying to provide businesses and programmers with options, VSTO being one of them.

    Thanks for the feedback, much appreciated!

    Cheers,

    -Beth

  • Hi Beth,

    Great article. Is it possible to call a method in a VSTO Application Level Add-In from VBA?  This article and all the others I have seen apply only to Document level add-ins.

    cheers

    Peter

  • Hi Peter,

    Here’s a walkthrough on how to expose methods in a VSTO Add-In to COM/Office apps:

    msdn.microsoft.com/.../bb608614.aspx

    HTH,

    -B

  • What do I need to use VSTO and do what you did in this article? I have Office 2010 Plus. Do I also need Visual Studio (Professional)to use VSTO? I know it used to be required, but is it still? Is Visual Studio Express required?

    Can you specify what I need to purchase, and what can be downloaded free of charge? This is one of the greatest obsticles I face, not knowing if I have all the required elements to be able to try something new, being so new to programming all around; it's all still pretty much a big cloud of mysteriously technical sounding words.

    Also, there's a lot of training available now for InfoPath, something I've been searching for forever! But nearly everywhere I see "InfoPath," I also see "SharePoint Server." :(  The company I work for does not have SharePoint and will not be getting it. Is there someplace I can go to see what I can and can't do with InfoPath without SharePoint Server?

    Thank you,

    Kristen

  • Hi Kristen,

    You need Visual Studio Professional or higher to get the Visual Studio Tools for Office (VSTO). You can purchase that here: www.microsoft.com/.../buy

    I'm not familiar with the capabilities of InfoPath but you can check their blog here:

    blogs.msdn.com/.../infopath

    You can also ask knowlegeable folks in the InfoPath forums on this site:

    http://www.infopathdev.com/

    HTH,

    -Beth

  • This is a great article. Good and important points to know.

    As far as this allows you to call VBA functions is there ANY WAY to do the opposite in a document-level add-in? By opposite I mean can I expose a C# class in a document-level add-in and create an instance of it in VBA?

    I am well aware that you can expose classes in an application-level add-in but is it somehow possible in a document-level one?

    If interested please see my question on Stack Overflow @ stackoverflow.com/.../how-to-expose-a-c-sharp-class-to-a-vba-module-in-a-document-level-add-in

Page 1 of 1 (8 items)