This post will look in more detail at translating the VBA Quadratic function (described in Python for VBA users – 1) into a Python function, linked to Excel via PyXll. For the purposes of this exercise I created a new Python module called quadratic2.py, and copied the VBA code into the blank module, using the PyScripter text editor.
After the first pass through of the procedure given in the previous Python post the code looked like this:
@xl_func("numpy_array CoeffA: numpy_array")
def py_Quadratic2(CoeffA):
# Calculate the zeros of the quadratic a*z**2+b1*z+c.
# The quadratic formula, modified to avoid overflow, is used to find the
# larger zero if the zeros are real and both zeros are complex.
# The smaller real zero is found directly from the product of the zeros c/a.
if CoeffA.shape[0] == 1:
CoeffA = transpose(CoeffA)
A = CoeffA[0]
B1 = CoeffA[1]
C = CoeffA[2]
if (A == 0.):
NR = 1.
SR = 0.
if (B1 != 0):
SR = -C / B1
LR = 0.
SI = 0.
LI = 0.
elif (C == 0.):
NR = 1.
SR = 0.
LR = -B1 / A
SI = 0.
LI = 0.
else:
# Compute discriminant avoiding overflow
B = B1 / 2.
if (abs(B) >= abs(C)):
E = 1. - (A / B) * (C / B)
D = ((abs(E)) * abs(B))**0.5
else:
E = A
if (C < 0.):
E = -A
E = B * (B / abs(C)) - E
D = ((abs(E)) * (abs(C))**0.5)**0.5
if (E >= 0.):
# Real zeros
NR = 2
if (B >= 0.):
D = -D
LR = (-B + D) / A
SR = 0.
if (LR <> 0.):
SR = (C / LR) / A
SI = 0.
LI = 0.
else:
# complex conjugate zeros
SR = -B / A
LR = SR
SI = abs(D / A)
LI = -SI<br />
if NR == 1:
QuadA[1, 1] = SR
QuadA[3, 1] = NR
else:
if LR < SR:
QuadA[1, 1] = LR
QuadA[2, 1] = SR
QuadA[3, 1] = NR
if NR == 0:
QuadA[1, 2] = LI
QuadA[2, 2] = SI
QuadA[3, 2] = 2
else:
QuadA[1, 1] = SR
QuadA[2, 1] = LR
QuadA[3, 1] = NR
if NR == 0:
QuadA[1, 2] = SI
QuadA[2, 2] = LI
QuadA[3, 2] = 2
return QuadA
Note that the function argument is designed to accept a 3 cell range, which may be either a single row or a single column. The logic for dealing with this has been changed in the Python function, for reasons that will discussed in a later post. For now just observe that the contents of the three cell input range (CoeffA) are copied into variables A, B1 and C.
After saving this code, and re-loading PyXll in Excel (Using Add-ins, PyXll, Reload PyXll), I entered three quadratic coefficients (a, b and c in ax^2 + bx + c = 0) in adjacent cells, and the function in a blank cell:
=py_Quadratic2(G10:I10)
This returned a Name? message. Before a new module can be used it must be added to the pyxll.cfg file in the PyXll folder. After adding quadratic2 to the modules list in the cfg file, and re-loading PyXll in Excel I was still getting the Name? message.
The problem this time could be found in the PyXll log file, which showed:
- 2013-09-28 15:58:00,308 – ERROR : Error importing ‘quadratic2’: name ‘xl_func’ is not defined
The PyXll library must be imported at the top of any code module where it is used. In addition I would be using maths functions, and also numpy arrays and the transpose function from the numpy library, so the following lines were added to the top of the code module:
from pyxll import xl_func from math import * from numpy import *
Saving and re-loading the code, the function now displayed: #NUM! indicating progress, but not quite there yet.
Looking at the log file again, the last message was:
“2013-09-28 16:42:25,936 – ERROR : if NR == 1:
2013-09-28 16:42:25,936 – ERROR : UnboundLocalError: local variable ‘NR’ referenced before assignment.
Looking through the code, you will see that the variable NR is assigned a value 1 if either A or C are equal to zero, but if both are non-zero we arrive at the statement “if NR == 1:” before NR has been assigned any value. This can be corrected by simply inserting NR = 0 near the top of the code.
Saving and re-loading, the function still shows #NUM!, and the log file now records:
2013-09-28 16:51:23,957 – ERROR : QuadA[1, 1] = SR
2013-09-28 16:51:23,959 – ERROR : NameError: global name ‘QuadA’ is not defined
We need to create the array QuadA before we can assign values to it. We will look at the Python alternatives for storing arrays (or array-like objects) in a later post; for now we will just create a 3×2 array using the numpy “zeros” function:
QuadA = zeros((3,2))
Note the double brackets required for a 2D array, and that zeros uses () rather than []. Also remember that all Python arrays (and lists, etc.) are zero based, whereas our VBA arrays were dimensioned as base 1. All the array indices therefore should be reduced by 1:
if NR == 1:
QuadA[0, 0] = SR
QuadA[2, 0] = NR
else:
if LR < SR:
QuadA[0, 0] = LR
QuadA[1, 0] = SR
QuadA[2, 0] = NR
if NR == 0:
QuadA[0, 1] = LI
QuadA[1, 1] = SI
QuadA[2, 1] = 2
...
After making all the required changes, saving the module, and re-loading in Excel, we finally have a function that works:
Newton Excel Bach, not (just) an Excel Blog 
What a mess. And it is not open source so you don’t even have a chance to fix it.
LikeLike
Actually Python is open source.
What exactly do you consider to be a mess?
What would be easier if the translation was from VBA to C++, or any other language?
LikeLike
PyXll is not open source.
“After making all the required changes, saving the module, and re-loading in Excel, we finally have a function that works:” People like easy.
Perl was another one guy language. Where is that now? The Python “Benevolent Dictator for Life” refuses to deal with the GIL bottleneck. The smart C++ guys are solving some hard problems that will be important in the coming multicore world.
Whacked together https://xllgsl.codeplex.com/SourceControl/latest#trunk/quadratic.cpp just now. If you squint your eyes, VBA, C++, and most procedural languages look the same. You’re an engineer, use what works for you.
LikeLike
OK, you’ve totally missed the point of the post.
You can write a one-liner to call a pre-written routine to solve quadratic equations (in fact any polynomial equation) using Python as well (in fact I’ll be posting an example of exactly that quite shortly), but that wasn’t the point. The point was to translate from VBA code to the equivalent Python code with something a bit more complex than “Hello World”. If I had chosen to do it with C++ instead the order of difficulty would have been very much the same.
As for PyXll not being open source, neither is Excel, so what? It’s not a priority for me. It would be nice to do it entirely in Python and Gnumeric, but at the moment that’s not a practical proposition for me.
You say use what works for me, that’s exactly what I am doing.
p.s. I don’t know what a GIL bottleneck is.
LikeLike
Sorry, just trying to contribute something you and your readers might find useful.
Excel is supported by one of the largest companies in the world. If something in PyXll breaks you’re stuck. If you are just noodling around, no big deal. If you are doing production work you’re screwed.
“p.s. I don’t know what a GIL bottleneck is.” Most people don’t, until they run into it.
LikeLike
Keith – your posts often come across as negative and self-promotional, not that I have a problem with self-promotion, especially since your stuff is open-source and free (please correct me if I read that wrong). I see you now have a blog, which I’ll add to my links list. Is there any reason you don’t link to https://xll.codeplex.com/ from your blog?
Your links to GSL etc look interesting, but I’m a bit confused by the site layout. The Download tabs all seem to say nothing to download. Where’s the best place to go to download the best current version of any application?
As for the GIL bottleneck, I looked it up: https://wiki.python.org/moin/GlobalInterpreterLock
It looks like it won’t be a problem for me because ” potentially blocking or long-running operations, such as I/O, image processing, and NumPy number crunching, happen outside the GIL”, but if it does prove to be a problem in the future I’ll know where to look for an alternative.
LikeLike
Thanks for your honest feedback. Yes, xll.codeplex.com is as free as I know how to make it.
For some reason there is no reply link on your last post. I hope you don’t think that is a negative comment. WordPress is what it is.
You seem to be a bright and interesting guy and it is simply a mystery to me why you have such an aversion to C++. Some of the smartest people in the world have been putting in many man hours to make the subset you need more accessible. What do you think python is written in? It is easier than ever to cut out the middleman.
I’ve haven’t been able to get the code you write working on my computer and asked you for help on that. If you have any problem installing and using the xll library, please let me know. If that works for you, getting set up with GSL, or any other project, is pretty straightforward if you know how to use source code control. Hoping to get smart guys like you to contribute and that’s how it operates.
Added your site to my blogroll.
LikeLike
You should use docstring (not comment) for function here. Docstring can help you create documentation easy in the future. You can also use built-in help() function or special attribute __doc__ like this:
@xl_func(“numpy_array CoeffA: numpy_array”)
def py_Quadratic2(CoeffA):
“””Calculate the zeros of the quadratic a*z**2+b1*z+c.
The quadratic formula, modified to avoid overflow, is used to find the
larger zero if the zeros are real and both zeros are complex.
The smaller real zero is found directly from the product of the zeros c/a.
“””
…
How it works from IDLE:
>>> help(py_Quadratic2)
Help on function py_Quadratic2 in module __main__:
py_Quadratic2(CoeffA)
Calculate the zeros of the quadratic a*z**2+b1*z+c.
The quadratic formula, modified to avoid overflow, is used to find the
larger zero if the zeros are real and both zeros are complex.
The smaller real zero is found directly from the product of the zeros c/a.
Using __doc__ attribute:
>>> print(py_Quadratic2.__doc__)
Calculate the zeros of the quadratic a*z**2+b1*z+c.
The quadratic formula, modified to avoid overflow, is used to find the
larger zero if the zeros are real and both zeros are complex.
The smaller real zero is found directly from the product of the zeros c/a.
Good IDEs and editors will show docstring when you type `py_Quadratic2(`
LikeLike
Sorry. WordPress broke my code. Here is a correct example:
Docstring: http://dpaste.com/hold/1399834/
Docstring test: http://dpaste.com/hold/1399835/
LikeLike
Kros – thanks for the comments. I am planning to look at docstrings later. The plan is:
– Get the quadratic and a similar cubic function to the stage where they return an answer.
– Check that the answers are correct, and sort out the reasons if they aren’t (in fact the code given above has a couple of errors, which I’ll look at next time).
– Look at the various Python and numpy data types, and how they interact with Excel
– Look at how docstrings can be used to document the function when it is called from Excel.
– Look at the Python and numpy functions that do the same job, and how they can simplify the code.
But I don’t mean to imply that I know it all; I’m very much a beginner when it comes to Python, and any comments or suggestions for better ways to do things are welcome.
LikeLike
Pingback: Python for VBA users – 3 | Newton Excel Bach, not (just) an Excel Blog