Excel VBA – How it can speed up your work and ruin your reputation

The Hell of multiple instances and why you should really avoid to use it in production

(about) Five months have passed since my last post and now I’m back again to tell you a story I’ve gone through: this one particularly tells of Excel VBA invoking Outlook passing through multiple instances of EXCEL32*.

During the last two years I massively used Excel, PowerPoint and Outlook empowered by VBA during my daylife job: Excel VBA is really good when it comes to play with business data & market analytics or when you need to automate repetitive and time-consuming tasks. Outlook is the client that perfectly integrates to the MS Office suite and to the authentication platform in use.

Everything was going smoothly with Workbooks, Sheets, Pivot tables, VBA macros, OLAP queries and so on until the day it was decided to use Excel VBA to automate some reporting to the Managers by

• downloading data from an Internet Explorer 8 session
• placing something inside a temporary sheet used to do the dirty job
• sending every table and similar as images inside the body of an e-mail, not as attachments.

Well,  even if it could sound extremely insane -imho it definitely is- apparently good reasons exists.

I started writing down some ideas, focusing on

• Reusability of the code
• Regular Expressions (provided by Microsoft VBScript Regular Expression 5.5) to parse data coming from IE8
• Automation of Outlook using VBA and  MAPI
• HTML messages to easily play with images built on-the-fly and attached to the email message, i.e. somehow referenced/embedded inside the HTML message
• Configurable behavior (a Sheet or an external plain text file containing parameters)

A couple of days should have been dedicated to the study and the development but it came that “as there’s no time to spend on software development it’s safe to use “simpler” solutions” like a dirty macro doing something like

• coping the needed tables and graphical objects as images inside the Windows clipboard (using something like Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap )
• pasting from the clipboard inside the new outlook mail message using key combinations (kind of Application.SendKeys “^v”  )

O__O

WTH!

This approach –even if awful– properly works when you run your macro one shot avoiding at the same time any kind of interaction during the execution but, what happens when you need to run multiple instances of VBA Macros simultaneously?

The problem is not Excel by itself, of course but, abusing the clipboard and the SendKeys API could lead to a huge number of problems that will impact your work and your reputation after all.

It briefly comes that running two different process of EXCEL32 with two different macros – both interacting with Outlook and the Windows clipboard – led to something catastrophic where

• one of the processes started filling the clipboard with something and immediately get stuck due to a disconnection from the Outlook.Application
• the other one started pasting corrupted stuff inside the email body or -the worst case- inside the workbook itself and then sending it to the managers 🙁

It came that I could have mitigated those kind of errors by handling things at a lower level, i.e.

• using Application.CutCopyMode = False and Application.CutCopyMode = True to force a clipboard cleanup a
• implementing something like a semaphore where if one of the processes was still running/stuck the other should have kept waiting for its turn (maybe never coming) a

[OT] If it would have been granted to access the CSV download page through XMLHttp or better perl-LWP, passing through the proxy authentication NTLMv* it would have been way better.

You know, actually it’s never fault of a computer but, the intrinsic weakness of VBA erupted loudly.

UPDATE 12/30/2015

after the installation of a few system updates and passing from IE8 to Internet Explorer 10 things have gone even worse: outlook new message don’t get the right focus causing Ctrl+V to paste somewhere else, iexplore process brings old sessions back to life -thus multiple tabs- when invoked by the macro.

I miss something very usefull inside VBA:

• Exception handling capability (Try, Catch, Finally)
• Easy-to-use classes/tools to access data through proxy, to avoid the IExplorer mess
• More consistent object model
• VB Editor/IDE up2date (it’s almost 2016, c’mon)

Se funziona quanto basta, non toccarlo che si guasta

At the very end of this story we managed to make things work properly by running those macros and those processes with computers with a well-defined environement and specific versions of each application involved. o/