How to call the NAG Libraries from Excel using VBA

September 4th, 2008 | Categories: applications, math software, NAG Library, programming | Tags:

Part of my job is to look after Manchester University‘s site license for the NAG libraries. If you have never heard of the NAG (Numerical Algorithms Group) libraries before, and if your work involves any kind of numerical computation, then I highly recommend that you check them out as they are very good at what they do. One senior researcher at Manchester referred to them as ‘The gold standard of numerical computing.’ High praise indeed and praise that I completely agree with.

The NAG libraries are written in Fortran but you don’t have to be coding in Fortran in order to use them. With a bit of effort you can call them from many different programming environments such as Python, Visual Basic, C (in fact there are C-specific versions of the libraries) and MATLAB (via the NAG Toolbox for MATLAB).

A few months ago I had a visit from some very worried looking students who needed to call the NAG libraries from Excel using Visual Basic for Applications (VBA) and they had no idea where to start. Sure, NAG have some VBA examples on their website but they assume that the reader already knows a fair amount about both the NAG libraries and VBA – knowledge that these students simply didn’t have.

I took a look at what they wanted to do and said that if they came to meet me in a couple of days time then I would put together a simple piece of code that would push them in the right direction. The code would be well commented, I told them, and would cover all of the concepts that they would need in order to put together their application. They looked very grateful and relieved.

I didn’t have the heart to tell them that I had never written a single piece of VBA code in my life!

So, off I went, learning just enough VBA to work with the NAG libraries. The staff at NAG helped me out when I got stuck and, I’m happy to say, I had just what these students needed by the time of our next meeting. I’d like to stress that I didn’t do their work for them – not even close! They told me what NAG functions they wanted to use and all I did was code up example VBA scripts that called those functions for various sample problems. This was all the help I gave them as I felt that it fell within my remit of ‘supporting the NAG libraries at Manchester’ without crossing the line of actually doing their work for them.

I looked at the pile of hand written notes that had been made while I was learning VBA and thought that they could do with being typed up. After all, I would probably have forgotten most of it by the time I was next visited by some students.

To cut a long story short, these notes ended up becoming a technical report that was published on NAG’s website today. So, if you find yourself needing to call the NAG libraries from within Excel 2003 then you might find them useful. As always, feedback is welcomed.

Thanks to all of the Staff at NAG who helped me clean up the mess that was the first draft – I have really enjoyed working with you all and hope to do so again soon.

If you enjoyed this article, feel free to click here to subscribe to my RSS Feed.

Update (3rd March 2009): The article referred to in this article has been updatedclick here for details.

  1. September 8th, 2008 at 17:26
    Reply | Quote | #1

    Ah, you obviously have a stronger stomach than me. If I worked for the sort of university where students think they need to call NAG routines from Excel using VBA then I would immediately dust off and nuke it from orbit. It’s the only way to be sure.