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.

Because NMath was not designed from the ground up to interoperate with the aging COM standard, many types will not be useable from VBA. All types lacking an empty (default) constructor, generic classes, static methods, and all static classes are not directly usable from VBA. However, any of these incompatible classes or methods can be wrapped in VB and then made available to Excel via COM. We understand many Excel users are not C#/.NET experts and so we are happy to help wrapping any NMath classes or NMath Stats classes you may need for enhancing and accelerating your Excel models.

Porting Excel Models to C# using NMath

As models grow in Excel, they commonly devolve into a bizantine workbook that becomes slow, opaque, and difficult to version, debug and manage. Usually, it is at this point that users start looking at porting the complex portions to another compatible platform, typically VB. These workbooks tend to be business critical applications so the port must be done carefully and in a piecewise fashion. This conservative strategy can be acheived using the following steps.

1. Identify the computationally demanding and complex portion of the spreadsheet carefully selecting a separation point where the interface is thin between the workbook and the new VB class.
2. Identify all of the input and output cells of this computation.
3. Create build a VB wrapper class in a (class library) VB project that wraps the functionality necessary for the port. This class should include methods for loading and returning results in types compatible with VBA.
4. Build the library and generate the COM type library and add this new type library to the workbook’s VBA.
5. Test the new model in parallel with the existing model.
6. Remove the duplicated Excel computation and enjoy the new faster model.

Porting functionality from an Excel model to a VB type library can be facilitated by NMath due to the large crossover in functionality between Excel and NMath Stats. Also, due to NMath’s high performance, once ports are complete, considerable performance gains can be expected. Below is a table of Excel functions and their supporting classes in either NMath Stats or native .NET.

Happy Computing,

-Paul

Resources

• Microsoft ported the Excel financial functions to F#, making them accessible to any VB or C# project. Follow this link to download this library and read the libraries’ documentation and limitations.
• Microsoft’s landing page on the assembly registration tool regasm.exe for building the COM type libraries.
• A little dated but clear and complete article on accessing .NET assemblies from Excel.

Excel Functions Supported in NMath and .NET

Between the NMath and NMath Stats numeric libraries and the .NET framework many of the Excel functions are covered for a port to C#. If you have a math function that you need which is not covered or is not in this list, let us know and we can probably add it to NMath or NMath Stats. Note resources above if financial functions are needed.

Tags: calling C# from VBA, calling excel from .NET assembly, COM .NET interop, Excel functions, Excel math functions, Excel port c#

This entry was posted on Monday, February 15th, 2010 at 4:24 am and is filed under .NET, Support. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.