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

These projects were originally created using Microsoft Excel 2000, updated for the NAG C Library Mark 8 DLLs using Excel 2003, and tested on Excel 2003 and Excel 2007. 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 skeletons page. These files can be used in both Visual Basic 6 (and earlier) and VBA code. Separate VB.NET skeletons are available.

For each user-callable NAG C Library function there is a file called <function name>_skeleton.bas which contains the VB declaration of the C function and all Enum and Type declarations necessary for the parameters (mandatory and optional). This file can be imported into your VB project. In most of the Excel example projects, this file is imported as the "Declarations" module. If more than one NAG C Library function is called, more than one skeleton may be imported, but multiple declarations must be deleted. In the case of closely related functions it may be easier to import one skeleton in its entirety and only paste the declaration of the second and subsequent functions.

The skeleton files are based on the stand-alone version of the NAG C Library DLL (CLDLL084Z_nag.dll); to specify the version of the DLL which uses the MKL BLAS/LAPACK instead (CLDLL084Z_mkl.dll), replace CLDLL084Z_nag.dll by CLDLL084Z_mkl.dll in the Function or Sub declaration.

The support file nag_errlist.bas contains VB code setting the value of all NAG C Library error codes. This file can be imported into your VB project. In most of the Excel example projects, this file is imported as the "Errcodes" module.

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

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, 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 "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. 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). 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\CL08\cldll084zl\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 (CLDLL084Z_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.

Two Very Simple Examples

a02dcc

This simple example is implemented as a macro and consists of one VBA module containing the skeleton for a02dcc and 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 skeleton.

s10aac

This simple example is implemented as a function. There is just one module containing the skeleton for s10acc. 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).

Techniques Used in the Examples

Arrays

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(... x As Double, ...

Call e04ccc(... x(1), ...

(Note that the declarations in the skeleton files specify "ByRef" explicitly for clarity, especially since this is not the default for VB.NET.)

Call-back Functions

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.
E.g.

Declare Sub d01sjc Lib "CLDLL084Z_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

The supplied files contain skeletons for 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)

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 Longs. 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)

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.

Strings

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. For consistency all C strings are declared as Byte arrays in the VB skeletons. VB strings need to be copied to and from these 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
For i = 1 To NAG_ERROR_BUF_LEN
  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.

At Mark 8 of the NAG C Library DLLs, the console window is implemented differently from in previous Marks of the Library. 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. (Unlike the console window in earlier Marks of the NAG C Library DLLs, closing this window will not shut down Excel and the use of FreeConsole to dismiss the window is no longer necessary.) There are also cut, copy and paste facilities available via the Edit menu.

The e04ucc example uses this feature.

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

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

Introduction

Two Very Simple Examples

Techniques Used in the Examples

See Also