We’ve had several customers ask about porting their Excel model to a .NET language in order to leverage the performance and functionality of NMath or NMath Stats. NMath does have good crossover functionality with Excel making this porting job easier. It is also possible to accelerate your Excel models by calling the NMath .NET assemblies directly from a VBA macro in Excel . This post provides some guidance for porting all or just a portion of your Excel model to C# and NMath.

Excel is designed to interoperate with external assemblies from VBA using COM. Type libraries built in .NET are not directly COM compatible, however all .NET class libraries including NMath and NMath Stats can be made to present a COM interface making Excel interop possible. This interop is acheived by building the COM type library directly from the assembly – no recompiling needed – using a tool shipped with the .NET framework, and then adding this type library as a reference to an Excel sheets’ VBA macro. Because of the many differences between the C# language and VBA, only a small portion of NMath will be accessable from Excel using this procedure, however a simple remedy will be outlined below that can expand the available functionality to all of NMath.

1. To build the type library interface to the NMath.dll use the regasm.exe utility shipped with the .NET framework

    >regasm.exe NMath.dll /tlb:NMathCom.tlb

   The COM compatible type library now resides in the NMathCom.tlb file. You will see some warning messages regarding incompatibilities between COM and NMath.

2. Open a spreadsheet, right click on a sheet tab and choose View Code to open the VBA development environment.
3. In the Tools menu select References… and browse to the location of the new NMath type library.

Now the COM compatible portions of NMath are now available for use from VBA. At this point we can code up simple example for generating random numbers in VBA to test out the NMath interoperability

Private Sub TestNMath()
  Dim rand As New NMath.RandGenLogNormal
  rand.Mean = 50
  rand.Variance = 10
 
  Dim i As Integer
  For i = 1 To 10
    Cells(i, 1) = rand.Next()
  Next
End Sub

This simple VBA script populates cells A1:A10 with LogNormal distributed random numbers with a mean of 50 and a variance of 10. This is not so easily achieved within Excel natively.
(more…)