Sample Microsoft Excel Projects Illustrating How to Call the NAG C Library Mark 9

The information here is applicable to the DLLs supplied with CLW3209DAL.

This web page contains links to several zip files, each containing a Microsoft Excel worksheet showing how to call a NAG C Library function.

These projects were originally created using Microsoft Excel 2000, updated for the NAG C Library Mark 9 DLLs using Excel 2003, and tested on Excel 2007 and Excel 2010. They may not work with other versions of Excel. In particular, the Visual Basic code which is attached to the worksheet uses the AddressOf operator which may not work in older versions of Excel.

Support files for Visual Basic for Applications (VBA) may be found under the VB6 headers page. These files can be used in both Visual Basic 6 (and earlier) and VBA code. Separate VB.NET headers and VBA7 headers are available.

For each NAG C Library chapter letter there is a file called clvb6-<chapter letter>.bas which contains the VB declarations of the C functions from all the chapters beginning with this letter. For example, clvb6-e.bas contains the declarations of all the functions from chapters e01, e02, e04 and e05. There is also a corresponding set of files called clvb6-<chapter letter>-long.bas using NAG C Library long names. The supplied files also contain commented out skeletons for call-back functions. All Enum and Type declarations necessary for the parameters (mandatory and optional) can be found in the file clvb6-types.bas. The file clvb6-utilities.bas contains declarations of the Windows kernel function RtlMoveMemory and its aliases. This function is used to copy memory between C pointers and VB variables or arrays. clvb6-*.bas files can be imported into your VB project. In the example projects above, clvb6-<chapter letter>.bas files are imported as the "Declarations<chapter letter>" modules, clvb6-types.bas as the "Types" module and clvb6-utilities.bas as the "Utilities" module.

The header files are based on the stand-alone version of the NAG C Library DLL (CLW3209DA_nag.dll); to specify the version of the DLL which uses the MKL BLAS/LAPACK instead (CLW3209DA_mkl.dll), replace CLW3209DA_nag.dll by CLW3209DA_mkl.dll in the Function and Sub declarations.

These support files use the VB option to base arrays at one, i.e.

Option Base 1

All the examples use this convention.

Note: Regardless of this option, arrays within User Defined Types (UDT) have a base of 0, unless explicitly declared to be 1, e.g.

Dim X(1 To 5) As Double

After opening the worksheet with Excel, you can view the Visual Basic for Applications code that is part of the spreadsheet. Under Excel 2003 or earlier, this can be done by going to the "Tools" menu and selecting "Macro" and then "Visual Basic Editor". Under Excel 2007 or Excel 2010, select the "Developer" tab and then click on the "Visual Basic" icon in the "Code" panel. (If the "Developer" tab is not visible, in Excel 2007 click on the "Office Button" in the top left-hand corner of Excel, then click on the "Excel Options" button, then make sure that "Popular" is selected in the left-hand column, and check the box to enable the "Show Developer tab in the Ribbon" option. In Excel 2010 click on the "File" button, then click on the "Options" button, then select "Customize Ribbon" in the left-hand column, and check the box for "Developer" tab in the "Main Tabs" list on the right.) This VBA code is used to interface to the NAG C Library.

In general, on opening the spreadsheet the results of the example will already be displayed. Some examples are implemented as macros and to rerun these go to the "Tools" menu, select "Macro" and then "Macros..." (under Excel 2003 or earlier), or select the "Developer" tab and then click on "Macros" in the "Code" panel (under Excel 2007 or Excel 2010). To rerun a function call go to the Formula Bar and type return (or Control-Shift-Return for multi-valued functions).

Remember also that to be able to use the NAG C Library DLL, its location will need to appear somewhere in your current path. If the DLL is in C:\Program Files\NAG\CL09\clw3209dal\bin (for example), then your PATH environment variable must contain this folder before starting Excel. If you are using the MKL-based version of the NAG C Library (CLW3209DA_mkl.dll), then the folder containing the MKL DLLs should also be on your path, but should appear later in the path than the bin folder for the NAG C Library DLLs, e.g.

C:\Program Files\NAG\CL09\clw3209dal\bin;C:\Program Files\NAG\CL09\
clw3209dal\MKL_ia32_10.2\bin;<rest of path>


This simple example is implemented as a macro and consists of two VBA modules, one containing the declarations for the a chapters and another with Enum and Type declarations, and of a subroutine that gets two numbers from the spreadsheet, passes these to a02dcc and returns the result to the spreadsheet. Note that the Complex data type is declared in the "Types" module.


This simple example is implemented as a function. There are just two modules, one containing declarations for the s chapter and another with Enum and Type declarations. This is all that is needed, because the s10acc function is so simple that a VB interface is unnecessary and the function can be called directly. The number of functions that fall into this category is quite small. The function return value and arguments must be double or Integer scalars and there must be no fail parameter. This technique might be useful for some special functions (s chapter) and random number functions (g05).


In the NAG C Library function declarations, arrays are declared only with their type. To pass a VB array to the C Library simply pass the first element and, by default, VBA passes its address.
Declare e04ccc Lib "CLW3209DA_nag.dll" (..., ByRef x As Double, ...)

Call e04ccc(... x(1), ...)
(Note that the declarations in the header files specify "ByRef" explicitly for clarity.)

Call-back Functions

The supplied clvb6-<chapter letter>.bas files contain commented out skeletons for call-back functions. They can be copied into your code and uncommented to form the basis of your call-back function.

In the VBA declarations of NAG C Library functions and subroutines, call back function dummy arguments are declared as Long. In fact, these are pointers to the functions and a Long is used to contain this pointer. The pointer to the actual function is passed using the VB AddressOf operator.

Declare Sub d01sjc Lib "CLW3209DA_nag.dll" (ByVal f As Long, ...)

Call d01sjc(AddressOf fun1, ...)

Please see the d01sjc, e04ccc and e04ucc examples for examples of call-back functions.

Array Arguments to Call-back Functions

Because the underlying library is written in C, array arguments are simply declared as the appropriate type. VBA passes arguments, by default, ByRef. Hence we have access to a pointer to the array. In the case of input array arguments, the appropriate amount of storage has to be copied to a VB array before it can be used. At the end of the function, output arrays must be copied back to the pointer.

In the examples, the Windows function RtlMoveMemory is used to do this copying, e.g.

Declare Sub RtlMoveMemory Lib "kernel32" (hpvDest As Any, _
                          hpvSource As Any, ByVal cbCopy As Long)
Sometimes Alias is used to rename declarations requiring different types, e.g.
Declare Sub CopyMemFromPtr Lib "kernel32" Alias "RtlMoveMemory" ( _
                       ByRef hpvDest As Any, ByVal hpvSource As Long, _
                       ByVal cbCopy As Long)
Declare Sub CopyMemToPtr Lib "kernel32" Alias "RtlMoveMemory" ( _
                       ByVal hpvDest As Long, ByRef hpvSource As Any, _
                       ByVal cbCopy As Long)
These declarations are included in the supplied file clvb6-utilities.bas.

Here is an example of how to do this in a user supplied function called objfun.

Sub objfun(ByVal n As Long, ByVal ptr_x As Long, ...
' Declare x as an array
Dim x() As Double
' Re-dimension it the right size
ReDim x(n)
' Copy from the pointer ptr_x to our local array
Call CopyMemFromPtr(x(1), ptr_x, n * Len(x(1)))
' The array x can now be used.


' If x is updated, copy it back to the pointer ptr_x
Call CopyMemToPtr(ptr_x, x(1), n * Len(x(1)))

Examples of how to handle arrays in call-back functions can be found in e04ccc and e04ucc.

C Library Typedefs and VBA User Defined Types

Assigning pointers in VBA Types

Pointers in the VBA Types defined for the NAG C Library are declared as Long. In order to assign, for example, a VB Double array to such a pointer, some trickery is needed.

First we need a function that accepts the array pointer ByVal and assigns it to the pointer in the Type, in this case Nag_User.

Sub load_address_of_array(ByVal comm_array_ptr As Long, comm As Nag_User)
  comm.p = comm_array_ptr
End Sub

This subroutine is called using the VarPtr function to pass the address of the array x as a Long.

Call load_address_of_array(VarPtr(x(1)), comm)
To access the array pointer stored in the VB Type we again use RtlMoveMemory, but this time there is a slight change needed in its declaration. As the pointer is stored in a Long we need to trick RtlMoveMemory to accept this and the argument associated with this pointer is declared as being passed ByVal, which causes VB to pass the contents of the Long to RtlMoveMemory which hence receives the required pointer.

Two different declarations are used, to copy to and from the local array, for example

Declare Sub CopyMemFromPtr Lib "kernel32" Alias "RtlMoveMemory" ( _
                       ByRef hpvDest As Any, ByVal hpvSource As Long, _
                       ByVal cbCopy As Long)
Declare Sub CopyMemToPtr Lib "kernel32" Alias "RtlMoveMemory" ( _
                       ByVal hpvDest As Long, ByRef hpvSource As Any, _
                       ByVal cbCopy As Long)
These declarations are included in the supplied file clvb6-utilities.bas.

In, for example, a user supplied function this array may be accessed by first copying it to a VB array. If the array is updated it must then be copied back.

Dim x(4)
' Copy from pointer to x
Call CopyMemFromPtr(x(1), comm.p, 4 * Len(x(1)))
' Copy back from x to pointer
Call CopyMemToPtr(comm.p, x(1), 4 * Len(x(1)))

Use of such an array is illustrated in the d01sjc and e01bac examples.

Assigning function pointers in VBA Types

Again a function, declared to receive the pointer ByVal, is used to assign the pointer as a Long.
Sub set_print_fun(ByVal ptr_fun As Long, ByRef options As Nag_E04_Opt)
  options.print_fun = ptr_fun
End Sub
The call to this function uses the AddressOf function to pass the address of our function called monit.
Call set_print_fun(AddressOf monit, options)
An example of this can be found in e04ccc.


Internally VB uses Unicode ("wide") characters, whereas the NAG C Library uses ASCII. VB handles the conversion from Unicode to ASCII for function/subroutine arguments but not for strings in UDTs. Therefore, some C strings are declared as Byte arrays in the VB header files. These VB strings need to be copied to and from the Byte arrays.

To copy a C null terminated String to a VB String, e.g. the fail.message Byte array in the NAG error structure:

Dim message As String * NAG_ERROR_BUF_LEN, i As Long
  If (fail.message(i) = CByte(0)) Then Exit For
  Mid(message, i, 1) = Chr(fail.message(i))
Next i
Or, to copy a VB String to an optional Byte array parameter to a NAG C Library function:
Dim filename As String, i As Long
filename = "e04ccce.r" + Chr$(0)
For i = 1 To Len(filename)
  ' Copy VB String to Byte array.
  options.outfile(i) = CByte(Asc(Mid(filename, i, 1)))
Next i

Console Window

A number of NAG C Library functions, by default, create a console window to display intermediate results, etc.

How to use the console window depends on which version of the C Library you have; use the "Check NAG DLL Accessibility" feature from the Start Menu (as described in the Users' Note) to ascertain the Mark number, then follow the appropriate advice below.

Alternatively, this output may be redirected to a file, as may be seen in the e04ccc example, or suppressed entirely.

Using the console window from Mark 9.6 or later

(This information also applies to Mark 8 of the NAG C Library DLLs.)

The console window may be closed temporarily (until more output arrives) via File | Close or permanently (until the next time Excel is invoked) via File | Exit. Closing the console window from the close button ("X") in the top right hand corner or via Alt-F4 has the same effect as File | Close. There are also cut, copy and paste facilities available via the Edit menu.

The e04ucc example uses this feature.

Using the console window from an earlier version than Mark 9.6

We strongly recommend that you contact NAG to obtain the latest release or download it from the NAG website. If you have a version earlier than Mark 9.6 (excluding Mark 8 of the NAG C Library DLLs), and you are unable to upgrade to a more recent version, then the following information applies.

If a console window is created, it is important to note that closing this window by clicking the button marked "X" in the top right hand corner will immediately close Excel without saving any modified files. This is a documented feature of Excel. So some other way of dismissing the console window should be used. (This problem does not arise with the latest release of the NAG C Library.)

The Windows function FreeConsole may be used to dismiss the console window safely.

Declare Function FreeConsole Lib "kernel32" () As Long
Dim i as Long
i = FreeConsole()
Once the console window has been closed in this way, the NAG C Library will not re-open it.

If you require further advise on the use of FreeConsole, please contact NAG.

A number of Visual Basic 6 examples are also available. These examples provide further illustrations of the techniques described above.