The AlgLib module ap.bas includes complex number functions which can be called from other VBA routines, but they cannot be used as User Defined Functions (UDFs) because they use the user defined data type “Complex”:
Public Type Complex X As Double Y As Double End Type
To call these functions from the spreadsheet an interface routine is required to convert the spreadsheet data into a complex data type, and for those functions that return a complex number, convert that back into a type that can be returned to the spreadsheet. One option would be to use the Excel Complex() function on the spreadsheet and convert the resulting string into the Alglib Complex data type. The use of strings to represent complex numbers seems to me to be cumbersome and inefficient, and for Excel versions before 2007 it requires the installation of the Analysis Tool Pack. For these reasons I have chosen to use ranges of two adjacent cells to represent complex numbers. The short routine below converts a single row, 2 cell, spreadsheet range (or a 1×2 array) into the AlgLib Complex data type:
Public Function Vara2Complex(ByVal Za As Variant) As Complex Dim Z As Complex If TypeName(Za) = "Range" Then Za = Za.Value2 Z.X = Za(1, 1) Z.Y = Za(1, 2) Vara2Complex = Z End Function
All the modified routines have AL_ inserted in front of the AlgLib function name.
A similar routine could be used to carry out the reverse process, but because all the complex number routines are very short I have simply modified them to return a variant array rather than a complex data type. An example is shown below:
Public Function AL_C_Mul(ByRef Z1a As Variant, ByRef Z2a As Variant) As Variant Dim Result(0 To 1) As Double Dim Z1 As Complex Dim Z2 As Complex Z1 = Vara2Complex(Z1a) Z2 = Vara2Complex(Z2a) Result(0) = Z1.X * Z2.X - Z1.Y * Z2.Y Result(1) = Z1.X * Z2.Y + Z1.Y * Z2.X AL_C_Mul = Result End Function
A spreadsheet including Excel UDF versions of all the AlgLib complex number functions (including full open source code) can be downloaded here: Al-Complex.xls
Examples of each function (and the equivalent Excel function, where available) are shown in the screenshot below (click to view full size).