Iterative solvers and arrays

A recent thread at Eng-Tips was looking for a solution to the problem described  below:

The solutions to this problem described below can be downloaded from:


The ItSolve.xlsb spreadsheet contains a user defined function (UDF) to solve iterative problems using Brent’s method, so the first approach was to modify this to return an array of results, with the input variables defined by a column and row of data, solving a function entered as text on the spreadsheet:

This works, but it is quite slow, taking about 10 seconds to return results for a 21 x 21 array.   I then modified the UDF to work as a subroutine, solving a short VBA function, rather than the text string on the spreadsheet:

Function TempFunc(Coefficients As Variant, T As Double)
Dim G_1 As Double, G_2 As Double, G_3 As Double, G_4 As Double, G_5 As Double, epsilon As Double, alpha As Double

G_1 = Coefficients(1, 1)
G_2 = Coefficients(2, 1)
G_3 = Coefficients(3, 1)
G_4 = Coefficients(4, 1)
G_5 = Coefficients(5, 1)
epsilon = Coefficients(6, 1)
alpha = Coefficients(7, 1)

    TempFunc = G_1 * epsilon * T ^ 4 + G_2 * (T - 320) ^ 1.25 - G_3 * alpha - G_4 - G_5

End Function

Input and results are shown below:

A 3D contour plot can now be generated quickly using Excel’s “surface chart” option:

This reduced the computation time for the 21×21 matrix down to about 0.25 seconds.

The xlwings/Python spreadsheet xlwScipy3.xlsb also has a function using Brent’s method (linking to the Scipy  brentq function).  I have modified this to return results as a 2D array, with input of a single column and row of data:

Input for the Eng-Tips problem is shown below:

The xl_BrentA function will work on either a function entered as text on the spreadsheet, or link to a Python function.  The screen-shot below shows results calling the Python function “TempFunc” (included in the download files).

Exactly the same results are generated using the text function on the spreadsheet:

Both options complete the calculation of the 21×21 array in about 5 milliseconds, about 50 times faster than the solution using a VBA function, or 2000 times faster than solving the string function entered on the spreadsheet!

This entry was posted in Arrays, Charts, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings and tagged , , , , , , , . Bookmark the permalink.

2 Responses to Iterative solvers and arrays

  1. Pingback: More iterative solvers | Newton Excel Bach, not (just) an Excel Blog

  2. Pingback: Solving the Lagrangian Point equation for the Moon | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.