MATLAB / C# / VBA Interop

I work at an actuarial outsourcing company in Argyle, TX called The Scruggs Group (or TSG for short). I live in Grand Prairie, TX and primarily hang out in Arlington, TX so I drive around quite a lot. For those that don't know, Argyle is about an hour away from Arlington, whereas Arlington is about 15 minutes away from Grand Prairie. Thankfully, due to my house's location, I'm still only an hour away from work, since I live right off of the major highway, 360. I just mention all of this because I heard on the radio this morning that gas is finally dropping below $3 a gallon in certain places in North Texas, and I hope that extends further down south.

Anyways, this seems like a very random topic, and it is to some extent, but I was tasked with creating a proof-of-concept and figuring out the pipeline to allow a developer to produce a managed excel document using Visual Studio 2008 and the latest VSTO 3.1 runtime while working with the MATLAB Compiler Runtime 7.8 to wrap around and access MATLAB routines for use in the managed excel document. The secondary condition is that we wanted to turn around and maintain access to the MATLAB Compiler Runtime with VBA directly, and also indirectly through managed C#. The reasoning behind this secondary condition is that it allows the actuaries I work with to write temporary VBA routines against the Excel document that feature their own MATLAB routines, and also to allow for more formal and permanent features to be written in via managed C# directed through me and published out to the network via ClickOnce Deployment. There are of course serious performance benefits to utilizing C# to handle file I/O and SQL Server access and data manipulation versus utilizing VBA to do similar operations, so things of a performance-heavy nature, especially if they also incorporate MATLAB, will be written in a MatlabLibrary class and wrapped for use both in the managed excel file via VSTO and if necessary (for whatever bizarre reasons) accessible to calls via VBA.

There are a few ways you can do this, and I was able to locate some good articles online about the simplest of them: creating a dynamic linked library (DLL) in C# in which you reference the MathWorks libraries and the MATLAB exported routines (via MATLAB® Builder™ NE for the Microsoft® .NET Framework) and generating wrapper classes and functions for those routines, ensuring that they adhere to an interface. Then, you take and build this DLL and run regasm against the library with the \tlb option to create a TLB file (a type library) for use in an excel file. You open up or create the excel file in question, hit Alt+F11 to go into the Microsoft Visual Basic Editor, get to the module you'll be utilizing the C# code in, and add in a reference (Tools > References) to the TLB you just generated via regasm from the C# DLL. You can now use simplified code (see below) to make calls against the C# functionality.


Assumptions: MatlabTest is the solution name. MatlabLibrary is the class name. You've generated the necessary interface (say, IMatlabLibrary, which doesn't matter in the VBA code) that exposes the functionality you want, and you've followed the above steps to build your DLL, tranlated it to a type library, and referenced it in your VBA code.

Simplified Code:

Public matlib As MatlabTest.MatlabLibrary

Public Function VectorSum(arrA as Excel.Range, arrB as Excel.Range) as Double()
Set matlib = New MatlabTest.MatlabLibrary
Dim
VectorSum = matlib.VectorSumDoubles(arrA, arrB)
End Sub

Also, you have to be aware of the way MATLAB and/or C# return the results to you - in the above example, I wanted to put the returned results of the array function vertically in 5 columns (I used 2 5-column inputs as the arguments) and needed to perform a TRANSPOSE on top of the =VectorSum(A2:A6, B2:B6) formula against the C2:C6 range.

In either case, this serves just fine for regular usage of Excel, but what if you're not using a regular Excel file, but one that is generated via VSTO and managed C#? You want to have the embedded managed functionality be accessible via VBA as well, right? And you want to ensure that every time you publish an update via ClickOnce, that the new functionality you generated is still accessible via VBA without the users having to go through a complicated process (or yourself for that matter) of updates.

This more sophisticated method allows me to write new functionality against my managed excel file in VS2008 and hit publish and everything "just works" - due to steadfast preparation, of course.

Okay, so how do we get an excel workbook with C# functionality and Matlab access that we can have interact directly or indirectly with VBA?

Step Zero: However you obtain your Matlab routine (my actuary provided it to me using MATLAB Builder NE), ensure you have a deployed/exported Matlab routine (we're going to use VectorSum) and the necessary runtimes to develop with. These include:
Matlab Compiler Runtime (I used v7.8)
VSTO (I used 3.1)
Visual Studio (I used 2008 Professional Edition)
Microsoft Office (I used 2007 Professional Edition)

The exported routine we called PhilipTest.dll and it deployed with the PhilipTest.ctf file as well. It deployed with PhilipTest_pkg.exe which I used to install the "DLL" and the MCRInstaller runtime to my machine.

Step One: Create yourself a new solution (go ahead and backup your other solution or remove it) in VS2008, a managed C# Excel 2007 Workbook solution called MatlabTest, naming the actual workbook MatlabVectorSum, ensuring that it is a macro-enabled workbook, thus having the ".xlsm" extension. Add references to the MWArray.dll from where you installed the compiler runtime (should be in C:\Program Files (x86)\MATLAB\MATLAB Compiler Runtime\v78\toolbox\dotnetbuilder\bin\win32\v2.0, disregard the (x86) if you're not on a 64-bit OS) and the MATLAB DLL (PhilipTest.dll) file.

Step Two: Add a class and an interface to your Managed Excel project called MatlabLibrary [MatlabLibrary.cs and IMatlabLibrary.cs] and ensure the class contracts to that interface. Code in the functionality you want; I stuck with making sure my using directives were in place, to make typing simpler, and had Excel.Range as inputs to the function, converting them to MWArrays, calling the PhilipTest.VectorSum(arrA, arrb) function, and returning the result set as an array of values. Add into your ThisWorkbook.cs file the following code under the ThisWorkbook_Startup event:

matlib = new MatlabLibrary();
// this will start "CallbackReg" macro in the workbook using matlib
ThisApplication.Run("MatlabModule.CallbackReg", matlib, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing);

Step Three: Register your project for COM interop (Project Properties) and ensure that your platform is specific to x86, sign your ClickOnce manifests, include your prerequisites and setup your publish options, update the AssemblyInfo.cs file to make sure the COM visibility is true, and mark your MatlabLibrary class and IMatlabLibrary interface with the [System.Runtime.InteropServices.ComVisible(true)] directives.

Step Four: Go into your Excel view inside of Visual Studio 2008, click "View" on the Office Fluent Ribbon, Click "Macros" > "View Macros" and create a new Macro called CallbackReg (found this little gem on the internet as well). Update your module name to "MatlabModule" and erase all code you see in it. Now, add in at the least the following code:

Public matlib As MatlabTest.MatlabLibrary

Public Sub CallbackReg(callback As MatlabTest.MatlabLibrary)
Set matlib = callback
End Sub

Public Function PMTVectorSum(arrA As Excel.Range, arrB As Excel.Range) As Double()
PMTVectorSum = matlib.VectorSumDoubles(arrA, arrB)
End Function

Step Five: Test. I created a button control in the excel view inside of VS2008 through the Toolbox, generated an event handler, and wrote code in C# that utilized the MatlabLibrary class to make sure that C# itself could talk to MATLAB. I also used the above VBA function and an Office shape assigned to a macro that called the VBA function with specific arguments to prove that the VBA could call my C# function. And then I made a third button that proved VBA could talk directly to Matlab.

Step Six: Publish. This is where I had the biggest issues. ClickOnce typically works extremely well for me, but with this project I had issues. 2 files (the PhilipTest.ctf.deploy file and a CPPAPI.netmodule.deploy file) would not publish so every time the end user tried to install from the VSTO manifest or the setup file, it would fail. I placed them manually into the Application Files folder, under the v1.0.0.2 folder, and when the users tried installing it after that, it worked fine. However, I need to figure out how to get all the necessary files to publish. VS2008 reports the publish succeeds just fine, but as witnessed by my end users, this is not the case. If anyone finds a solution for this, please let me know!

I know the above tutorial doesn't show every little step and all of the code, but it's specific enough that a C# or VB.NET developer shouldn't have any real problems following along on it. The biggest steps are understanding what's going into the Matlab routines and what's coming out of them so that you can write your code efficiently and understand and document what's going on.

Good luck, folks.