Sample Microsoft Excel projects illustrating how to call the NAG C Library Mark 26.1

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

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 26.1 DLLs using Excel 2010, and tested on Excel 2016. 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. The information here applies to 32-bit versions of Excel only.

Support files for Visual Basic for Applications (VBA) may be found under the VBA headers page. These files can be used in 32-bit VBA code. Separate VB.NET 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 (or "Declarations<chapter letter>Long" for the long name variants), 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 (CLW32261E_nag.dll); to specify the version of the DLL which uses the MKL BLAS/LAPACK instead (CLW32261E_mkl.dll), replace CLW32261E_nag.dll by CLW32261E_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 

The instructions here are for Excel 2010, Excel 2013 and Excel 2016.

After opening the worksheet with Excel, you can view the Visual Basic for Applications code that is part of the spreadsheet. Select the "Developer" tab and then click on the "Visual Basic" icon in the "Code" panel. (If the "Developer" tab is not visible, 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 select the "Developer" tab and then click on "Macros" in the "Code" panel. 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\CL26\clw32261el\bin (for example), then your PATH environment variable must contain this folder before starting Excel. You will also need the folder containing the Intel run-time libraries on your path (unless these are already present). If you are using the MKL-based version of the NAG C Library (CLW32261E_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\CL26\clw32261el\bin;
C:\Program Files\NAG\CL26\clw32261el\rtl\bin;
C:\Program Files\NAG\CL26\clw32261el\mkl_ia32_2017.0.3\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 e04cbc Lib "CLW32261E_nag.dll" (..., ByRef x As Double, ...)

Call e04cbc(..., 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 "CLW32261E_nag.dll" (ByVal f As Long, ...)

Call d01sjc(AddressOf fun1, ...)

Please see the d01sjc, e04cbc, e04dgc 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 e04cbc, e04dgc 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 e04dgc.



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 = "e04dgce.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.

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

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.