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:
- Python, including Numpy and Scipy
- xlwings ver 0.11.04 or later
- Alglib Python version 3.11 or later
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:
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:
… 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.