Following my recent posts on some preliminary experimentation with linking Excel to Python, this will be the first of a more structured series, mainly as a reminder to myself of lessons learnt on the way, but also I hope of use to others starting out with Python.  The focus will be on connecting to Python from within Excel, using the PyXll add-in, combined with the numpy and scipy maths and scientific libraries.  See the link above for suggestions for installation of Python and PyXll.

This post will look at the procedure for transferring a VBA User Defined Function (UDF) to Python.  The basic procedure for creating a Python UDF in PyXll is as follows:

• Create a Python module to hold the Python code.  A Python module is analogous to a code module in the Visual Basic Editor, but consists of a text file with the extension .py.
• Add the Python code for the required function.
• Add an “xl_func” decorator at the top of the Python function code, to expose the function to Excel.
• Add the name of the Python module to the modules list in the file pyxll.cfg in the main PyXll folder.
• The Python function will now be available from any Excel file, so long as the PyXll add-in is active.

For the purposes of illustration of this procedure I have chosen to translate VBA functions to solve Quadratic and Cubic equations to Python.  In fact functions to solve polynomial equations can be found in the numpy library, but translating the code is a good exercise, and provides a function that can be easily checked.

The procedure I used to translate the VBA code to Python was:

• Ensure that the VBA code is properly indented.  In VBA indentation is solely for ease of reading, but in Python the indentation is essential to the logic of the code.  I use the excellent and free Smart Indenter, for automatic indentation of my VBA code.
• Copy and paste the VBA code to your text editor of choice.  I am currently using PyScripter, a free and open-source Integrated Development Environment.
• Edit the VBA code to follow Python requirements:
  • Remove all Dim statements.
  • Replace all VBA comment markers (‘) with the Python equivalent (#).
  • Create any global variables, lists or arrays required, and ensure that bracketed index values to arrays are in the correct format (more details in a later post).
  • Modify all If, Do, and similar loops to Python format.  Note that Select Case loops must be replaced with a series of if … elif statements.  Also note that Python is case sensitive, and that VBA statements in Proper Case must be converted to all lower case.
  • Where the VB editor has added a # to integer values this must be removed, since it is the comment character in Python.
  • Where integer fractions are required to be treated as floating point values (e.g. 1/3), at least one of the values must be entered as a decimal: 1.0/3.
  • Replace any VBA exponentiation symbols (^) with the Python equivalent (**).
  • Check the correct spelling for any built-in functions used, and remember to change initial upper case letters to lower case.
  • Comparison operators: <, <=, >, and >= may remain unchanged, but = must be replaced with == (when used for comparison, rather than assignment), and != is preferred to <>.
  • Revise any code segments where the methods used in VBA will not work in Python.
  • Create a return value or array in a format suitable for transfer to Excel.
  • Replace the VBA style return value statement:
    FunctionName  = x
    with the Python:
    return x
    and delete the End Function statement.
  • At the first line, replace VBA:
    Function FunctionName(Parameter as Type, …) as Type
    with Python:
    def FunctionName(Parameter):
  • Add the xl_func “decorator”:
    @xl_func(“type Parameter, … : return type”)

The two screen-shots below show a section of code as pasted from the VBA editor, and as translated to VBA format.  In the next post in this series I will look at this process in more detail, including some of the catches for the unwary.

VBA code pasted to Python editor (Dim statements deleted)

Final Python Code