Python to replace VB6 …

… well at least for me. As I discussed previously I’ve been seriously investigating using Python as my primary datasmithing scripting language, in effect a new VBA. I also currently use VBA’s compiled cousin, VB6, for certain tasks such as building Excel RTD servers. The problem with VB6 is it depends on Visual Studio 6, which is no longer supported by MS and is increasingly next-to-impossible to purchase. I have a copy which I picked up at a charity auction (for €50, which also included, Windows 2000, Visio and Office 2000 professional!) but I am aware any code I develop in VB6 is in effect tending towards”closed source” as far as many others are concerned as VS6 continues its journey into history. (I also use Visual C/C++ but such code is future-proof as the latest versions of VS continue to support C/C++ unaltered).

So what are my alternatives:

  • Do nothing, continue to use VS6 (I’ve already made an “off-site” copy in case the house burns down!).
  • Use .NET, ExcelDNA is an easy way to integrate Excel with .NET, but it doesn’t handle the creation of COM servers. In general, accessing COM from .NET is a total PITA.
  • Use Python.

I’m going down the Python route, especially now that I’ve figured out how to create in-process (DLL) COM servers (via pyInstaller) and how to manage sub-processes (via import subprocess). Not just because it’s future-proof, but because:

  • It’s easy to build against and to manipulate COM interfaces, nearly as easy as VB6, much, much easier than the .NET alternatives.
  • I can then use the same platform to handle general datasmithing (on Windows, MacOS and Linux), and do Excel integration “stuff” and also web front-ends (via Google Apps Engine and Bottle).
  • I really like Python and I find I’m much more productive with it than with any other language I have ever used (with the possible exception of MUMPS, but otherwise “Where were you Python, when I were a lad, down coding pit, slaving over hot Cobol …”).

Enough work, the sun is shining; the chiffchaff is, well, chiff-chaffing; spring has sprung, I’m off out to the garden…

Update:

I’ve now managed to embed Python in Excel via my xLite datasmithing tool http://www.gobansaor.com/microetl, another fine reason to use Python.

UPDATE: July 2011:

For another method of integrating Python (this time .NET’s IronPython) with Excel/VBA see http://blog.gobansaor.com/2011/07/18/vba-multithreading-net-integration-via-hammer/


Advertisements

4 responses to “Python to replace VB6 …

  1. Prashant Rane

    Funny, I came across this today along with your post.
    http://vb2py.sourceforge.net/

    Good match?

  2. Hi Prashant,

    I too came across the same site, after writing the post!

    I also came across two other potentially useful “conversion” tools, one to convert Java code to Python ..

    http://code.google.com/p/java2python/

    and why-the-lucky-stiff’s ruby to python convertor..

    http://github.com/why/unholy/tree/master

    Tom

  3. Hi,

    you might want to take a look at PyXLL (www.pyxll.com). It lets you do anything you previously would have done in VBA in python, including exposing python functions as worksheet functions (UDFs), menus items and macros.

    cheers,
    Tony.