Ways to Automate Excel Tasks

Anything beats VBA, right? An examination of available options

If you’ve ever had to do any automation tasks in Microsoft Office applications, and specifically Excel, then you may have experienced the pain of dealing with a language that’s not quite complete – namely VBA.

VBA has documentation that’s spread across dozens of websites in which experts disseminate their hard-won knowledge. Microsoft has some documentation online, but it tends to be terse. The art of VBA coding is one of trial and error. The most common experience among many would-be VBA programmers is to copy and paste from online examples. As soon as they have something that approximates what they want then they cease all further work. Frequently, any knowledge gleaned from this is quickly forgotten.

And yet, the businesses I support love Excel. An outgrowth of that affection is the demand for automating tasks within that application. Most of the time this is fine, and the Excel macro recorder can take care of most user automations admirably.

Sometimes, however, these automations cross the line into application territory. For example, when Excel is performing database reads and writes. Or when Excel is sending emails on behalf of users. Or when an Excel workbook is copied across hundreds of users and goes beyond simple formatting and charting tasks.

Frequently we are constrained by perceptions of complexity, or time, or budget and must use Excel to accomplish these tasks. But is VBA the best tool to use for doing this? There are ways to perform tasks in Excel using external tools or even plugins.

I’ve got a couple of things I’ve found over there years for your perusal.

Consider using PowerShell for Excel automation tasks:

https://www.red-gate.com/simple-talk/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/

http://sqlmag.com/powershell/update-excel-spreadsheets-powershell

PowerShell is appealing because it’s made to be a scripting language that binds different Windows applications together. It has great documentation, even when offline, and it gives us the powerful ability to read in an Excel spreadsheet like a table using SQL syntax to filter the data and then we may do with that data as we please. In terms of speed and maintainability, where VBA will eventually start to chug when doing some tasks – PowerShell remains speedy.

And yes, you can call PowerShell scripts from VBA for a nice marriage of the two.

runSendreports = Shell("Powershell.exe Path/To/The/Script.ps1", vbNormalFocus)

The drawbacks you’ve probably already guessed at by now. The Execution Policy needs to be set correctly on each client PC. Also, the ODBC drivers in PowerShell have a few bugs (which the author of the above article goes into, including work arounds – which is nice). From a maintenance perspective – you know have a package of workbooks and PowerShell scripts that must be maintained and deployed together. There are likely good solutions to the deployment problem and I welcome feedback on that.

What about Python?

Did you know that there are plugins that allow you to write all your scripts in Python instead of VBA? Does that sound appealing? Check these out:

https://datanitro.com/ Get the full power of the Python ecosystem in your spreadsheet! For just a small licensing cost…

There is an open source equivalent:

https://www.xlwings.org/ With a 3-clause BSD license: https://tldrlegal.com/license/bsd-3-clause-license-(revised)

All you must do is learn Python and you’re good to go. If you have a choice to make between learning Python or VBA, well… Well. Python is certainly more broadly applicable than VBA.

These two options only scratch the surface – being that they provide the Python programming language to tackle Excel tasks I call them out by name.

However, there are many more niche options that tackle a specific problem within Excel. It’s certainly worth researching so you’re not stuck debugging VBA.

The drawbacks are the cost of the software and/or whether the license will be an impediment. These two drawbacks should not be understated. Even seemingly benign licenses might be rejected by an organization’s legal department and the reason for that objection will rarely be transparent. A drawback specific to Python is if you use a Python library then you’ll need to think of a way to keep that library bundled with your spreadsheet. I’m unsure if either of the linked plugins provide a solution for that problem.

Another option is to make your VBA writing experience more pleasant

Given that there is no way to write and run VBA outside of the Visual Basic for Application Editor (VBE). (You can technically copy and paste bode between a text editor and VBE if you enjoy that sort of thing)

Free
---------------
MZTools: www.mztools.com
VTools: www.skrol29.com/us/vtools.php
Indenter: http://www.accessui.com/FreeAccessTools/SmartIndenter/tabid/74/Default.aspx

Paid
---------------
Find and Replace: www.rickworld.com
Total Visual Code Tools: http://www.fmsinc.com/MicrosoftAccess/VBACodingTools.html

Hopefully this will direct you to some resources that can help you get up and running on automating your Excel tasks without forcing you to use VBA, or if you do use VBA hopefully you can make the experience marginally more pleasant.

Element Inline Styles vs Classes - Browser Rendering Speed

The Speed of Browser Rendering: Element inline styles vs classes

We had an interesting issue while supporting an application here at my client. We had a list of elements that we needed to filter based on text inputs. The way the code worked it was using jQuery to take up the collection and iterate through it based on the filter. It would then show or hide the element based on the filter criteria. The problem was that the entire process was slow and the business users were starting to find it burdensome.

Why was it slow? We spent several hours examining the legacy code to understand exactly what it was doing and why. The project was constrained to using jQuery to accomplish many of its UI tasks. Further, the original writer of the code seemed to not think much of classes because they were using inline element styles on all the elements.

We were unsure why the code did not perform quickly, but as an exercise in best practices we replaced the inline element style with a class (which we quickly defined in a style tag in the HTML). Immediately, the code started to perform five times better. So, it seems that simply switching to a class made the difference – but why?

The answer is that the browser has two separate pipelines for HTML and CSS which are compiled together and sent to the browser’s Rendering engine:

See the performance issue for yourself, check out this benchmark: https://jsperf.com/inline-style-vs-css-class/2 (credit to Sergey Ermakovich )

So how are inline element styles handled in this process?

Per https://www.html5rocks.com/tutorials/internals/howbrowserswork/#style , it turns out that inline styles are eventually translated into CSS rules for the element upon which it sits. This means that for JavaScript processes that are dynamically composing or changing large lists of elements each individual element has its own style rule for the browser’s rendering engine to consider.

This, in effect, meant that there were hundreds of style rules being created and changed for our application. Using a CSS class corrected this problem because it reduced the style rules from hundreds to one. Also, the browser can cache the CSS class for the benefit of the rendering engine.

Furthermore, the CSS style rule that element inline styles are eventually created into are one of the slowest types of CSS rules available. The inline style results in an extremely specific ‘Descendent’ selector. This type of selector is slow because CSS is read from right to left by the browser. So for example if we had this selector:

html body ul li a {}

Then the browser would first find all the <a> tags in the document, then it would find all of the <a> tags with a <li> parent and so on until the specific elements that match the criteria are resolved into a set and the rendering engine can apply the styling required. (reference https://css-tricks.com/efficiently-rendering-css/ )

Finally, the Google reference guide warns us that inline styles on HTML elements are blocked by default with Content Security Policy.

https://developers.google.com/speed/docs/insights/InlineCSS

https://www.w3.org/TR/CSP/#should-block-inline

But why should the CSP block inline styles? CSP doesn’t give us an explanation – but I found one for you:

https://www.owasp.org/index.php/XSS_(Cross_Site_Scripting)_Prevention_Cheat_Sheet#RULE_.234_-_CSS_Escape_And_Strictly_Validate_Before_Inserting_Untrusted_Data_into_HTML_Style_Property_Values

Before you even click, if you were thinking ‘I bet IE…’ the answer is ‘yes’.

So, element inline styles – simply put, you probably should not use them. If you do use them then be aware of the risks: they are slow and they are a potential security problem.