## Calling External .NET Libraries from Excel

Wednesday, December 8th, 2010There are many circumstances where you may need to access an external library of functions or routines from Excel. For example, if you need a complex function such as fitting data to a surface, or portfolio optimization, that is not natively available in Excel. There also may be a need to protect proprietary calculations by using user defined functions to process algorithms in a black box manner. I was looking for a way to rapidly prototype some calculations without setting up a complex development environment.

Harking back to the old rule of development projects of “two out of three”, when the three metrics are fast, cheap, and quality. On any time limited project you only can plan to achieve two metrics, never all three. Initially I like to dive in and start with fast and cheap and work my way towards quality as necessary. So, we’ll start with the quick and dirty approach to calling external libraries from Excel.

### Project Setup

You must have a version of .NET Framework of 2.0 or greater. The latest version is free from Microsoft at this link.

http://www.microsoft.com/net/download.aspx.

You’ll also need:

- Excel 97 or later.
- External library assemblies that you need to access from Excel. In our case we will use Centerspace’s
**NMath.dll**and**NMathStats.dll**. - A freeware product called ExcelDNA written by Govert van Drimmelen that can be downloaded at http://exceldna.codeplex.com/ .

The first order of business is to unpack the downloaded file, **ExcelDNA.zip**, into a working directory. For our example, we will use *CenterSpaceExcel* as our directory name. After unpacking you should have two folders *Distribution* and *Source* in our *CenterSpaceExcel* directory. Inside the *Distribution* folder locate the file **ExcelDNA.xll** and rename it to **NMathExcel.xll**.

We now need to locate in the same directory the file **ExcelDna.dna** and rename it to **NMathExcel.dna**. Then using notepad, or your favorite code editor, and open the file **NMathExcel.dna**.

You should see the following code:

<DnaLibrary> <![CDATA[ Public Module Module1 Function AddThem(x, y) AddThem = x + y End Function End Module ]]> </DnaLibrary> |

<DnaLibrary> <Reference Name="CenterSpace.NMath.Core" /> <Reference Path="C:\Program Files\CenterSpace\NMath 4.1\Assemblies\NMath.dll" /> <Reference Name="CenterSpace.NMath.Stats" /> <Reference Path="C:\Program Files\CenterSpace\NMath Stats 3.2\Assemblies\NMathStats.dll" /> <![CDATA[ Imports NMath = CenterSpace.NMath.Core Imports Stats = CenterSpace.NMath.Stats Public Module NMathExcel <ExcelFunction(Description:="Returns x to the y power")> _ Function NPower(x as double, y As double) As double NPower = NMath.NMathFunctions.PowFunction(x, y) End Function <ExcelFunction(IsMacroType:=True, IsVolatile:=True)> _ Function NRand() As double dim rand As New NMath.RandGenMTwist NRand = rand.Next53BitRes() End Function <ExcelFunction(Description:="Binomial Distribution: Number of Successes, Trials, Probability, Cumulative is True or False")> _ Function NBinomDist(NSuccess As Int32, NTrials As Int32, Prob As double, Cumul As Boolean) As double dim nbin As New Stats.BinomialDistribution nbin.N = NTrials nbin.P = Prob IF Cumul NBinomDist = nbin.CDF(NSuccess) Else NBinomDist = nbin.PDF(NSuccess) End If End Function Function NDoubleMatrixRand(rsize As integer, csize As integer, RandLBx As integer, RandUBy As integer) As Object(,) dim rng As New NMath.RandGenUniform(RandLBx,RandUBy) Rng.Reset(&H124) dim TempA As New NMath.DoubleMatrix(rsize, csize, Rng) NDoubleMatrixRand = NCopyArray(TempA, rsize, csize) End Function Function NCopyArray(IMatrix As Object, rsize As integer, csize As integer) As Object(,) dim i As Integer dim j As Integer dim OArray(rsize, csize) As Object for i = 0 to rsize - 1 for j = 0 to csize - 1 OArray(i,j) = IMatrix(i,j) next j next i NCopyArray = OArray End Function End Module ]]> |

We now have created the VB code to call our CenterSpace Math and Statistics libraries with the following five functions.

- The first function shows a simple math library call to the Power function which takes a number x and raises it to the y power and returns the value.
- The second function shows a call to obtain a fast random number from the math library. Since we want a new number each time the spreadsheet is re-calculated we have made the function
`volatile`

. - The third function call shows how to set values that need to be accessed by a function in our .NET assemble; in this case, the Binomial Distribution.
- The fourth function demonstrates the creation of a
`DoubleMatrix`

that is the filled with random uniformly distributed numbers. - The fifth function is a helper sub-routine to transfer data across the com interface.

### Test our setup in Excel

Open Excel and move your cursor the Tools menu item. Usually towards the bottom of the drop down menu you will find the selection Add-Ins. After selecting Add-Ins, you see the pop-up window with the option to select Microsoft supplied Add-ins. Choose the Browse option and go to the working directory we created at the beginning. In our case, this will be the *CenterSpaceExcel* directory. Next select the *Distribution* folder and you should see the renamed file: **NMathExcel.xll**. Select it and you should now see the following screen.

Make sure NMathExcel is checked and click OK. If you get an error and this point it is probably due to a typo in the DNA file, otherwise you will get the expected new sheet ready for entry.

Select an empty cell and then select from the menu bar Insert then from the pulldown Function. You should see the following pop-up.

At the bottom of the category pull down you should see our NMathExcel Functions; Select it and you should have these options.:

If we choose `NPower`

, we will get the next screen,

I arbitrarily typed the value of 3.2 for x and 3.327 for y. You can see the result of 47.9329301 before selecting OK.

Select OK and Excel will insert the value into the cell. Select another blank cell and this time choose our `NRand()`

function. You will notice there is no opportunity to enter values and finish by selecting OK. At this point you should see a number between 0 and 1 in the cell. Each time you press F9 (sheet recalc) a new random number will appear. If we had not made this function volatile the number would not change unless you edit the cell.

To test our Binomial Distribution function, again we will select a new cell and use the insert function option to insert the `NBinomDist`

function with the following values.

At this point we have made successful calls into both of CenterSpace’s NMath and NMath Stats .NET math libraries.

In our fourth example, we will see how Excel handles matrices and look at issues passing array arguments across the COM interface. Excel 2003 was limited to a maximum of 60,000 cells in an array, but Excel 2007 was expanded to handle 2 million. Excel has some quirky ways of displaying matrices, and I’ll cover the in’s and out’s of these quirks.

We have written the basic code to set up a function called `NDoubleMatrixRand`

for the purpose of creating a matrix with supplied dimensions and filled with uniform Random numbers over a specified distribution. We will select another blank cell and again go to insert function and this time choose `NDoubleMatrixRand`

. Suppose we want to create a 6×6 matrix filled with random numbers between -2 and 2. Our input will look like the following screen.

Notice the equal sign in the middle right of the above screen is equal to {-0.994818527251482,-0.08

Values inclosed in curly brackets that are separated by commas indicates that an matrix was actually created, but you can see the Formula result is only displaying a partial value due to display size. At this point when you select OK, you will have a cell with a single value. Here is where the fun begins. Start at the cell and drag a 6×6 range as shown in the following screen.

Now get your fingers limbered. Here is where it gets a bit obscure – do exactly as follows.

- Press the F2 key. (
*pressing F2 may be optional but is recommended by Excel as the cell leaves the edit mode*) - Press and hold the Ctrl key followed by
- pressing and holding the Shift key followed by
- pressing the Enter key

and presto chango! You should see a screen like this.

Notice that your cell’s formula is now enclosed in { }, indicating to Excel that the contained formula is an array function. This is the only way to get matrices displayed. Also, if you try to edit this cell you will get an error that changes are not allowed. If you want to change the dimensions simply reference the values from another cell when you create the function.

The fifth function `NCopyArray`

copies the library matrix across the COM bridge into an Excel array object. As I stated in the beginning this would be a quick and dirty approach and would leave room for improvement.

### Summary

In my next post, I will provide the above code in C# and add more function calls with some matrices with hopefully an improved approach to `NCopyArray`

. Future posts will include creating a packaged XLL and a more complex example such as curve fitting.

Since time is our most precious asset, being able to quickly access complex math functions with a general purpose tool like Excel should save time and money!

At CenterSpace, we are interested if this blog is helpful and if there is a need for more examples of how our libraries can be accessed by Excel. Let us know what areas are of interest to you.

Mike Magee

**Thanks and Resources **

Also, a special thanks to Govert van Drimmelen for writing a wonderful tool such as ExcelDNA.