In this blog it’s explained how you can wrap up C++ managed or unmanaged code and make the functionality available in Excel / VBA. The blog uses levmar as specific example, but most steps are generic. The levmar specific example is due to the fact that we are currently working on some short rate lattice models that need to be calibrated to the interests market. The calibration requires a robust solver for a nonlinear least squares problem. To share our models and their implementation with the wider finance community we choose Excel / VBA for the implementation. This allows us to easily share our ideas and algorithms. For most simple cases this worked wonderfully using a solver algorithm that was originally part of MINPACK developed by Jorge More, Burt Garbow, and Ken Hillstrom at Argonne National Laboratory. The algorithm we used was translated from Fortran to VBA by Vanna and shared on quantcode.com. This algorithm proved not to be stable enough for more complex problems. So we started looking for more stable implementations and found levmar by Lourakis. This package was written in ANSI C. Here we explain how we took this code and build xll add-in for Excel using:
In a previous blog it was explained how to compile your levmar setup into LevmarSharp. If you are interested in reproducing this set up, this blog will be very useful.
This blog uses Visual Studio 2010 and NuGet. If you don’t have NuGet setup it is still possible to make it work by downloading and configuring the packages manually. This approach can be a little painful, so if NuGet is an option use it.
To set up the two-way communication between the base code and Excel / VBA we use Excel-DNA and Net Office. Excel-DNA will pack the C++ and C# assemblies in an xll Excel Add-In and provide Com Server support. Net Office will allow us to call VBA function form C#, which we need to evaluate the error function of the solver. Create a new C# library project (LevmarExcel in our case) and install the required packages using the following command in the Package Manager Console:
Install-Package NetOffice.Excel
Install-Package Excel-DNA
To make the LevmarExcel assembly into an Excel Add-In with Com Server add the following class
namespace LevmarExcel
{
public class ExcelAddin : IExcelAddIn
{
public void AutoOpen()
{
ComServer.DllRegisterServer();
}
public void AutoClose()
{
ComServer.DllUnregisterServer();
}
}
}
Using NetOffice.Excel a VBA function can be called from C# with the following snippet
private void CallVbaFunctionFromStringName(string funcName, double[] p, double[] x, int m, int n)
{
NetOffice.ExcelApi.Application xlApp
= new NetOffice.ExcelApi.Application(null, ExcelDnaUtil.Application);
double[] xTemp = (double[]) xlApp.Run(funcName, p, x, m, n);
Array.Copy(xTemp, x, n);
}
The temporary array xTemp looks a little funny. This extra step is needed in this case. Calling a method in C# with object like double array will allow the target method to change the array. When passing a variable to VBA this is lost. This seems a good thing as it makes the code safer. In this case it posed a problem as the code we are writing is between two “libraries” for which the source can not be edited: Excel\VBA and levmar. Storing the changed variables in an object and rewriting the VBA code to use a function instead of subroutine that returns the result object works great. The result object of course needs to be “unpacked” in the C# code and copied to the variables. As an example to illustrate consider the LevmarSharp example Wood’s function:
private static void wood(double[] p, double[] x, int m, int n, IntPtr data)
{
int i;
for (i = 0; i < n; i += 6)
{
x[i] = 10.0 * (p[1] - p[0] * p[0]);
x[i + 1] = 1.0 - p[0];
x[i + 2] = Math.Sqrt(90.0) * (p[3] - p[2] * p[2]);
x[i + 3] = 1.0 - p[2];
x[i + 4] = Math.Sqrt(10.0) * (p[1] + p[3] - 2.0);
x[i + 5] = (p[1] - p[3]) / Math.Sqrt(10.0);
}
}
In first attempt Wood’s function was translated to VBA as follows:
Private Sub Wood(p() As Double, x() As Double, m As Integer, n As Integer)
Dim i As Integer
For i = 0 To n - 1 Step 6
x(i) = 10# * (p(1) - p(0) * p(0))
x(i + 1) = 1# - p(0)
x(i + 2) = Sqr(90#) * (p(3) - p(2) * p(2))
x(i + 3) = 1# - p(2)
x(i + 4) = Sqr(10#) * (p(1) + p(3) - 2#)
x(i + 5) = (p(1) - p(3)) / Sqr(10#)
Next i
End Sub
Using the following incorrect C# method to call the VBA code from the Excel Add-In does not work:
private object CallVbaFunctionFromStringName(string funcName, double[] p, double[] x, int m, int n)
{
NetOffice.ExcelApi.Application xlApp
= new NetOffice.ExcelApi.Application(null, ExcelDnaUtil.Application);
object result = xlApp.Run(funcName, p, x, m, n);
return result;
}
That didn’t work as levmar expects the x variable to change value as the Sub Wood gets evaluated. To solve this we change the Sub Wood to the function Wood as follows
Private Function Wood(p() As Double, x() As Double, m As Integer, n As Integer) As Double() Dim i As Integer For i = 0 To n - 1 Step 6 x(i) = 10# * (p(1) - p(0) * p(0)) x(i + 1) = 1# - p(0) x(i + 2) = Sqr(90#) * (p(3) - p(2) * p(2)) x(i + 3) = 1# - p(2) x(i + 4) = Sqr(10#) * (p(1) + p(3) - 2#) x(i + 5) = (p(1) - p(3)) / Sqr(10#) Next i Wood = x End Function
and at the Array.Copy(xTemp, x, n) step to the code in the C# Excel Add-In.
Next we are ready to pack the Add-In. To do this configure Excel-DNA using the .dna xml file to set the compile language, runtime version and include all the dependencies you need. Since we are planning to use the Add-In as Com Server make sure to include the ComServer = true flag.
Here is an examlpe .dna xml
<DnaLibrary Language="C#" Name="LevmarExcel Add-In" RuntimeVersion="v4.0"> <ExternalLibrary Path="LevmarExcel.dll" ComServer="true" LoadFromBytes="false" Pack="true" /> <Reference Path="ExcelApi.dll" Pack="true" /> <Reference Path="NetOffice.dll" Pack="true" /> <Reference Path="OfficeApi.dll" Pack="true" /> <Reference Path="VBIDEApi.dll" Pack="true" /> </DnaLibrary>
However, all is not well. Excel-DNA does not pack C++ assemblies. This leaves two strategies. Include the C++ assembly, LevmarSharp in this example, in the C# assembly as an embedded resource and unpack it at run time as needed. Or merge the two dll’s into one. The embed option is less elegant and can cause problems with write rights on the local disk. The preference is to merge the assemblies. The tool ILMerge is offered by Microsoft is the most well known solution, but it is very hard to setup. Instead we used Costura to pack the unmanaged code. This tool is simply awesome in doing the job out of the box in minutes. Install Costura using NuGet Package Manager Console by typing
Install-Package Costura.Fody
Costura is configured using the FodyWeavers.xml file. In this file simply add your unmanaged assemblies, in this case LevmarSharp. Remember not to add “.dll”. The resulting file should look something like this:
<?xml version="1.0" encoding="utf-8"?> <Weavers> <Costura> <Unmanaged32Assemblies> LevmarSharp </Unmanaged32Assemblies> </Costura> </Weavers>
That is it your Excel Add-In should be working now.
Source
Excel Add-in and sources are available here
References
More information on the open source projects used to build this Add-in and their source code can be found here:
Excel DNA - http://exceldna.codeplex.com/
Net Office - http://netoffice.codeplex.com/
LevmarSharp - https://github.com/AvengerDr/LevmarSharp
Costura - https://github.com/Fody/Costura
NuGet - https://www.nuget.org/
LevmarExcel - http://uglyduckling.nl/software/levmarexcel/
Previous blogs on Levmar
Some tips on how to compile the levmar C code using Visual Studio 2010 can be found here.
My notes on how to integrate the levmar C code into C# using LevmarSharp and compile/build the complete stack are available here.