Sample Microsoft Excel 64-bit Projects illustrating how to call the NAG C Library Mark 26

The information here is applicable to the DLLs supplied with CLW6I26DEL. For the latest information on calling the NAG C Library DLLs from various environments, or for sample projects for a specific Mark of the NAG C Library, please go to the main NAG C Library DLL support index page.

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 created for the 64-bit NAG C Library Mark 26 DLLs using 64-bit Microsoft Excel 2010. They will not work with older versions of Excel or with 32-bit versions of Excel. They have also been tested on Excel 2013 and Excel 2016. The information here applies to 64-bit versions of Excel only.

Support files for Visual Basic for Applications 7.0 (VBA7) may be found under the VBA7 headers page. These files can be used in 64-bit VBA7 code. Separate 64-bit VB.NET headers are available.

For each NAG C Library chapter letter there is a file called clvba764-<chapter letter>.bas which contains the VB declarations of the C functions from all the chapters beginning with this letter. For example, clvba764-e.bas contains the declarations of all the functions from the chapters e01, e02, e04 and e05. There is also a corresponding set of files called clvba764-<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 clvba764-types.bas. The file clvba764-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. clvba764-*.bas files can be imported into your VBA project. In the example projects above, clvba764-<chapter letter>.bas files are imported as the "Declarations<chapter letter>" modules (or "Declarations<chapter letter>Long" for the long name variants), clvba764-types.bas as the "Types" module and clvba764-utilities.bas as the "Utilities" module.

The header files are based on the stand-alone version of the NAG C Library DLL (CLW6I26DE_nag.dll); to specify the version of the DLL which uses the MKL BLAS/LAPACK instead (CLW6I26DE_mkl.dll), replace CLW6I26DE_nag.dll by CLW6I26DE_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. This can be done by selecting the "Developer" tab and then clicking 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\clw6i26del\bin (for example), then your PATH environment variable must contain this folder before starting Excel. You will also need the folder containing the Intel (and possibly other) run-time libraries on your path (unless these are already present). If you are using the MKL-based version of the NAG C Library (CLW6I26DE_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\clw6i26del\bin;
C:\Program Files\NAG\CL26\clw6i26del\rtl\bin;
C:\Program Files\NAG\CL26\clw6i26del\mkl_intel64_11.3.3\bin;
<rest of path>



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 PtrSafe Sub e04cbc Lib "CLW6I26DE_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 clvba764-<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 LongPtr. These are pointers to the functions and a LongPtr is used to contain this pointer. The pointer to the actual function is passed using the VB AddressOf operator, e.g.

Declare PtrSafe Sub d01sjc Lib "CLW6I26DE_nag.dll" (ByVal f As LongPtr, ...)

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 PtrSafe Sub RtlMoveMemory Lib "kernel32" (ByRef hpvDest As Any, _
                                  ByVal hpvSource As Any, _
                                  ByVal cbCopy As LongPtr)

Sometimes Alias is used to rename declarations requiring different types, e.g.

Declare Ptrsafe Sub CopyMemFromPtr Lib "kernel32" Alias "RtlMoveMemory" ( _
                       ByRef hpvDest As Any, ByVal hpvSource As LongPtr, _
                       ByVal cbCopy As LongPtr)

Declare Ptrsafe Sub CopyMemToPtr Lib "kernel32" Alias "RtlMoveMemory" ( _
                       ByVal hpvDest As LongPtr, ByRef hpvSource As Any, _
                       ByVal cbCopy As LongPtr)

These declarations are included in the supplied file clvba764-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 LongPtr, ...
' 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 LongPtr. 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 LongPtr, 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 LongPtr.

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 LongPtr 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 LongPtr to RtlMoveMemory which hence receives the required pointer.

Two different declarations are used, to copy to and from the local array, e.g.

Declare Ptrsafe Sub CopyMemFromPtr Lib "kernel32" Alias "RtlMoveMemory" ( _
                       ByRef hpvDest As Any, ByVal hpvSource As LongPtr, _
                       ByVal cbCopy As LongPtr)

Declare Ptrsafe Sub CopyMemToPtr Lib "kernel32" Alias "RtlMoveMemory" ( _
                       ByVal hpvDest As LongPtr, ByRef hpvSource As Any, _
                       ByVal cbCopy As LongPtr)

These declarations are included in the supplied file clvba764-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 LongPtr.

Sub set_print_fun(ByVal ptr_fun As LongPtr, 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

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.