Newton Excel Bach, not (just) an Excel Blog

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: