Category Archives: Excel

UDF to replace cell references with values

Posted on 30/08/2017 by dougaj4

I recently posted an update to the Eval2 spreadsheet with a subroutine to display a cell formula as a text string, with the cell references replaced by the associated values. I have now added three user defined functions (UDFs) to do … Continue reading →

Posted in Excel, UDFs, VBA | Tagged , , , , | Leave a comment

Evaluating text and calling Python functions from Excel with xlwings

Posted on 23/08/2017 by dougaj4

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 … Continue reading →

Posted in AlgLib, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , , , | 1 Comment

Converting a formula to values

Posted on 17/08/2017 by dougaj4

Excel has a built-in function (FormulaText) to display a formula as cell references, rather than the result value.  It would be useful to be able to display the value of each cell reference as well, but there is no built-in … Continue reading →

Posted in Excel, UDFs, VBA | Tagged , , , | 2 Comments

Excel to Alglib via xlwings

Posted on 11/08/2017 by dougaj4

The Alglib library provides many valuable numerical analysis routines, but the VBA version is no longer actively maintained.  The latest versions are available in C++, C#, and Python, of which the Python version (in conjunction with xlwings) provides the easiest interface … Continue reading →

Posted in AlgLib, Curve fitting, Excel, Link to Python, Maths, Newton, Numerical integration, UDFs, VBA, xlwings | Tagged , , , , , , , , , | 12 Comments

Installing xlwings 0.11.4

Posted on 30/07/2017 by dougaj4

The 0.11 versions of xlwings have some major changes to the arrangement of the required files, which will make installation and updating much easier, but for some reason Anaconda is very slow to upload the new versions, and the pdf … Continue reading →

Posted in Excel, Link to Python, VBA, xlwings | Tagged , , | 1 Comment