The Alglib spreadsheet presented here includes functions to evaluate formulas entered as text on the spreadsheet, or to call specified Python functions. I have now added a user defined function (UDF) that will perform either of these operations (xl_CallFuncD), and added a number of examples of use in different situations. The updated spreadsheet (including full open-source code) can be downloaded from:
As before the following software is required:
The Anaconda Python package includes Numpy, Scipy and xlwings (now updated to ver 0.11.04). Alglib must be installed separately, and comes in free and commercial versions.
To deal with the need to pass any number of arguments, the function arguments are listed in a two (or three) column range; column 1 lists argument names, and column 2 either single values, or range addresses, entered as a text string. This data is read into a VBA variant array, then converted to a Python dictionary. Examples of the usage of xl_CallFuncD are shown in the screen-shots below:
A formula entered as text on the spreadsheet, with any number of listed variables. Note that exponents may be entered using either VBA notation (^) or Python/Fortran notation (**):
Function results may be used as input values for another formula:
On-sheet formulas may include any built-in Python function, or functions from the imported Numpy, Scipy or Alglib modules:
Both on-sheet formulas and external function calls may have array arguments. In this case the argument range must have three columns, with the third column specifying the number of dimensions for the array (1 or 2). The range address may be entered as text, but to ensure that the address is updated if the range is moved the UDF RngAddress should be used:
xlCallFunc will call user defined Python functions from the xlAlgLib module without any dedicated VBA interface function:
… or user defined functions from another module:
… or functions from other Python libraries. If the called function returns 2 or more array results the Out argument must be used to select the array to be returned. The Python dictionary of arguments passes arrays as tuples, which will cause problems with some functions. In this case enter True in the Convert argument, which will convert all passed tuples to lists, or lists of lists.
Note that argument names must be entered exactly, including correct case. To list all arguments for any function, use the get_argnames UDF:
