NAG DLLs, Microsoft Excel and Microsoft Visual Basic for Applications

Microsoft Excel spreadsheet users can use the power of NAG DLLs to boost the capabilities of the Excel spreadsheet. The secret lies in inserting the appropriate Declare statements in a module Sheet of an Excel Workbook.

The Fortran-based DLLs are especially suited for use with Excel. The basic Fortran types of INTEGER, LOGICAL and DOUBLE PRECISION map directly on to the Excel types of Long, Long and Double respectively. In addition Fortran array storage is directly comparable to the array storage conventions used in Excel. So, in general, the NAG Fortran routines may be interfaced readily to Excel.

The C DLLs are slightly more complicated because of their more elaborate arguments, but NAG C routines too may be interfaced to Excel. To ease the process of mapping the C structures and data types onto Visual Basic structures and data types, information is provided, which can be downloaded from the web.

Programming Excel

If you were to open a fresh workbook in Excel, the worksheet displayed will, by default, be a standard worksheet consisting of cells into which you put values or formulae. To insert code into Excel it is necessary to open a Module sheet. In the later versions of Excel this is accomplished via the Tools/Macro/Visual Basic Editor menus. It is then necessary to use the Insert menu to insert an ordinary Module. (Experienced users may wish to use NAG routines from within a Class Module. This is quite possible, but beyond the scope of this article.)

To take a simple example, suppose we wish to use the NAG Fortran Library routine S14AAF using Excel. By using the NAG Browser or the vbheader.hlp file,

  • copy the Declare statement for S14AAF onto the Clipboard,
  • open the Excel Workbook and turn to a Module sheet,
  • paste the Declare statement from the Clipboard into the Module,
  • amend the Declare statement as necessary.

In particular the Lib component may need changing if the libraries have been installed to a directory not on the search path, or if the DLL names have been changed.

At this point it is worth making sure that the command 'Option Base 1' is at the top of the Module sheet if you are using a Fortran Library routine. This ensures that any VBA arrays declared start their indices at 1, making them compatible with the Fortran Library routines. We believe that an 'Option Explicit' statement is also worthwhile. It ensures that any VBA variable has to be declared. This prevents a variable being given a default type of Variant in VBA by accident. A Variant type is not compatible with any of the NAG types.

Unlike in C, VBA arrays store by column and are thus compatible with Fortran. Experienced VBA programmers may now use the Fortran Library routine as though it had been written in VBA, subject to the conventions contained in the Declare statement.

The simplest of the NAG Functions may be used directly. To see this, you would move to an ordinary worksheet in this workbook and select a cell before clicking upon the Function Wizard - fx. In the 'User Defined' function category, you would find the NAG Library routine S14AAF. Proceed as prompted by the Wizard, putting the dummy value 0 for IFAIL when finally prompted for this. If you have typed in valid input for the parameters, the function is now evaluated and placed in the cell selected. (You might wish to type in the value 1.25 for X, 0 for IFAIL and verify that the cell value is now 0.9064). Care has to be taken with string parameters in VB since these and the Fortran CHARACTER types are not entirely compatible. Note the importance of the ByVal qualifier in the Declare statement and the mandatory extra argument immediately following the string. The extra argument defines the length of the string and is of type Long. It too is a ByVal parameter. Fortunately NAG Library routines by and large use CHARACTER arguments for input rather than output, so the most awkward problem is avoided. COMPLEX arguments may be handled using the user-defined type:

      REAL_PART As Double
      IMAG_PART As Double
End Type

To use NAG Library routines with subroutine or function names in their arguments see the Excel/Visual Basic for Applications sections for the relevant mark of the Library.