How to solve a NLLS problem using SQP method in Excel?

NLLS stands for nonlinear least squares and SQP is sequential quadratic programming. So essentially this is an optimization problem, and everyone knows that NAG Library Chapter E04 is the best place to look for optimization solvers. The appropriate NAG routine in our C Library is nag_opt_nlin_lsq (e04unc).

A few weeks ago one of our users contacted NAG and asked for an example program of using e04unc in Excel. The NAG and Excel page has quite a few examples and guidelines about using NAG Library in Excel, but we didn't have this particular one.

I wrote this example and it is available for download on the Excel page. I encourage readers of this blog to download it and play with it on your own. It wasn't difficult to create, but there was one issue that caused me a nasty headache. Some routines that have callback functions (just as e04unc does) where a vector or matrix is passed to/from a subroutine require usage of Windows API subroutine pRtlMoveMemory.

Since the underlying NAG Library is a C Library, array arguments are simply declared as the appropriate type. VBA passes arguments, by default, by reference (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. For more information please have a look at our NAG and Excel page.

Well, I guess it all sounds easy. Nevertheless I had some problems with RtlMoveMemory - I couldn't pass the data to and from a callback without some sort of memory violation error or if it didn't crash I kept on getting incorrect results.

The trick is not to use the default declaration of RtlMoveMemory, but actually have two versions of it: one for passing memory to a callback function via a pointer, and the second for copying memory from an array in the callback function back to a pointer. They differ slightly in declarations:

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)

OK, but why is this so important in this example? e04unc has two callback functions.

objfun, which returns the value of the objective function and its Jacobian.
confun, which returns the values of constraint functions and their respective Jacobians.

Both of them take a vector of variables x(n) on input. In practice it means that the actual input is a pointer x_rptr to x. The user then uses CopyMemFromPtr to fill the vector x(n) with values that x_rptr points to. We start copying memory to the first element of x, from pointer x_rptr, and the full amount of memory copied is the length of the vector times the number of bytes required to store a single variable. Here's how it looks in the code:

Call CopyMemFromPtr(x(1), x_rptr, n * Len(x(1)))

So at this point we have vector x with the input values, so we can calculate the value of the objective function (and optionally its Jacobian). Once we have done it we have a vector f(m) that contains the function values. In order to get these values back to the main function we need to use CopyMemToPtr.

Call CopyMemToPtr(f_rptr, f(1), m * Len(f(1)))

This call means that we focus on the first element of vector f, take a specific amount of memory and make sure that pointer f_rptr will point at this particular vector. A similar approach applies to constraint function and the Jacobians.

This is essentially how RtlMoveMemory Windows API function is used with NAG C Library routines.

I decided not to put the whole VBA code here in order to encourage you, dear reader of this blog, to download the mentioned example and check the code on your own. In order to run it you need the 32-bit Windows implementation of the NAG Library. You can obtain a trial licence key for the Library from support@nag.co.uk.

Please let us know if you found the example useful and if you would like us to create example programs for other NAG routines!

Author

NAG