Monday, 22 August 2011

How do you use NAG in Excel?

For years NAG has considered Excel users as a group with certain needs for good numerical algorithms. Nowadays Excel is the industry-standard for spreadsheets, but it is not a statistical package nor is it a numerical analysis tool. That’s why Excel users come to NAG and ask how our accurate, robust and efficient NAG Library can be utilised in Excel.

There are a few ways in which the Library can be called in Excel:

  • xls/xlsm spreadsheet and VBA

The most popular, widely used way: NAG and Excel page describes how to do it with the help of technical papers and downloadable examples. Basically you create a new spreadsheet and add VBA code to it. NAG has created VBA Declaration Statements which provide the bridge between NAG and Excel- the user just has to add the declaration file to a VBA module and the NAG DLL becomes visible to Excel & VBA. From this moment on you can create macros that will, for example, read data from an external data file, process it in VBA and then display the results on the spreadsheet. Or you can create a UDF (User Defined Function) that is called straight from the spreadsheet, but inside uses NAG to compute some statistics, find a zero of a function, or find a value of a European call option using Black-Scholes Model or Heston’s Stochastic Volatility Model. This is the great thing about using NAG in conjunction with Excel- you can do it in many ways!

  • xla/xlam add-in and VBA

Very similar to the previous point. The difference is that the VBA code for the macros and UDFs is in an Excel add-in. As a result the user’s data and the user’s code may be in two separate files. So the user needs to load the xla add-in while using his xls file to have the functions available. http://www.cpearson.com/excel/createaddin.aspx nicely describes how an xla add-in works and how to create it.

  • xll add-in

An xll is a different type of add-in, it actually allows you to use your C/C++, Fortran, or VB code from Excel. This is actually a Dynamic Link Library (DLL) file that is compiled and accessible via Excel API. The benefit of having an xll is that it’s compiled and will be faster than using VBA. However it requires very cautious programming due to memory issues and is generally harder to program.

  • VSTO

Visual Studio Tools for Office is a set of development tools that allows creating Visual Studio projects in C# or VB.NET in the form of Excel add-ins that extend Excel’s features or customise the user interface. Since this is a .NET environment, it’s advisable to use the NAG Library for .NET.

We look forward to hearing from our users and anyone that is interested in using Excel in conjunction with NAG. How do you use Excel? What example programs do you think we should have on our website? Currently we have many examples on using NAG via VBA, but not many on the other methods.

Tell us what you would find useful!

No comments:

Post a Comment

NAG moderates all replies and reserves the right to not publish posts that are deemed inappropriate.