All the routines available in the NAG Library may be called from Microsoft Office. Usually, this means Excel since this is a computational engine. We will therefore describe the mechanism, drawing our illustrations from this application. Using the NAG Library from the other applications (such as Visual Basic .NET or C#) is similar, but this document describes how to call the NAG Library FL Interfaces from Excel.
Please contact NAG if you want to use the NAG Library with Access. The principles are very similar but differ in detail.
The Basic Principle
The basic principle is to include a statement of the form
Declare Ptrsafe Function Euler Lib "NLW6IxxxE_nag.dll" Alias "X01ABF" ( _
ByRef x As double _
) As double
at the top of a VBA7 module.
This allows the NAG routine x01abf to be used on an Excel worksheet using the name Euler. The routine returns Euler's constant.
Breaking this statement down into its components:
Ptrsafe is new to VBA7 and was originally introduced to accommodate 64-bit Excel with 64-bit addressing. We discuss this a little more under Matching the Library Routine Arguments to VBA Types.
Euler is the name by which we will call the function on the worksheet.
NLW6IxxxE_nag.dll is the name of a NAG dynamic link library that contains the requisite code. In this instance, it refers to a 64-bit dynamic link library where xxx represents the Mark number.
Alias "X01ABF" indicates that the requisite code is called X01ABF in the dynamic link library.
NAG provides these Declare statements (see Obtaining the Header Files below), so you should use these rather than attempt to write your own.
Choosing an Appropriate Library
The precise form of the Declare statement will differ, depending on whether you have a 32-bit or a 64-bit version of Excel and which NAG Library you have. Please note that, even if you have a 64-bit computer, it is possible that you have a 32-bit version of Excel and thus need a 32-bit version of the NAG Library.
The download below will help you decide on these issues. Simply download the workbook and run it using your version of Excel. The macro will tell you which version of Excel you have, which compatible NAG Libraries you have and which NAG Library you might consider using.
Obtaining the Header Files
The Declare statements are provided as sets of header files which can be downloaded from:
nag_vba7_32bit_fl_headers_latest.zip – for use with NLW32xxxEL and 32-bit Excel
and
nag_vba7_64bit_fl_headers_latest.zip – for use with NLW6IxxxEL and 64-bit Excel
These are arranged by Library chapter letter. Note that the f routine declarations are split over two files, and there are a further two files with versions of these declarations using BLAS and LAPACK names rather than NAG names.
Matching the Library Routine Arguments to VBA Types
For simple functions like x01abf, it was not necessary to write VBA wrapper code around the NAG routine to make use of it. In general, the NAG routine will be at the heart of more complicated code that you have written, be it a function or a macro.
The NAG-supplied Declare statement will tell you what VBA type the routine is expecting as an actual argument. For scalars, VBA double replaces the Fortran DOUBLE PRECISION scalar argument and VBA long replaces the Fortran INTEGER type.
It is important to note that the VBA variant type may not be used in this context; the argument must have an explicit type. For this reason, we recommend the use of the VBA Option Explicit statement.
64-bit integer quantities are held in a VBA type LongLong whilst addresses and handles use LongPtr.
Declare statements that include PtrSafe work correctly in the VBA7 development environment on both 32-bit and 64-bit platforms and indicate the provided types are correct for that environment.
When a Fortran compiler forms the library it sometimes inserts "hidden" extra arguments that are implicit in a Fortran context, but which need to be explicit when used in VBA. Typically these are associated with Fortran CHARACTER types which are mapped onto the VBA string type together with an extra parameter reflecting the length of the string. The precise form and location of this second parameter, which will either be Long or LongLong, is given in the Declare statement that NAG provides with each implementation.
These principles are encapsulated in the Declare statement provided for the 64-bit Library NLW6IxxxEL. This has 32-bit Fortran integer types, but note that the length of the character string is 64-bit.
Declare Ptrsafe Sub X04ACF Lib "NLW6IxxxE_nag.dll" ( _
ByRef iounit As long, _
ByVal file As string, _
ByRef mode As long, _
ByRef ifail As long, _
ByVal FILELength As LongLong _
)
Supplying array arguments
Many of the NAG routines take array arguments, both as input and output arguments. To use these we make use of the identical storage mechanism for VBA arrays and Fortran arrays. Specifically, a Fortran array has elements stored contiguously in memory and with elements in the same column and only one row apart adjacent. For example:
DOUBLE PRECISION :: A(2,2)
stores the elements in the order
A(1,1), A(2,1), A(1,2), A(2,2)
VBA does likewise, so we can supply the first element of a VBA array and rely upon storage association to correctly map the Fortran memory to the correct VBA array element.
By default, VBA7 will interpret an array declaration to start from element 0 so
Dim a(2)
would reserve elements a(0), a(1) and a(2). We find it convenient to use the
Option Base 1
statement to alter this behaviour to align with the Fortran convention, of declaring just a(1) and a(2).
These principles are encapsulated in the example below.
Supplying function or subroutine arguments
Many powerful routines in the NAG libraries require functions or subroutines to define the user problems. This class of problems includes root-finding, numerical integration, ordinary and partial differential equations, integral equations and minimizations.
Sometimes this challenge can be met by using a reverse-communication routine. There is nothing new from a VBA point of view, but the example below illustrates this powerful technique.
When a function needs to be provided then the worksheet itself may be used to define and evaluate the function. Internally a VBA function is written to access this information and is passed to the NAG routine using the AddressOf function.
This is illustrated by the following example:
More complicated routines, typically from the optimization chapter, may need to resort to the Windows API to use the routines correctly and the latest optimization routines make use of a C pointer to hold information. This requires the LongPtr type as illustrated in the following example, which also demonstrates the use of the relevant Windows API:
A zip file containing all these examples may also be downloaded. These work with the latest (64-bit) NAG DLLs. (Alternatively, download the zip file containing examples for use with the most recent 32-bit NAG DLLs instead.)
