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.

All Professional Developers Are Self-Taught

One of the many surprising things that I realized in my first year of being a Software Developer is that it is no special thing to be self-taught. The fact is that all of my co-workers are similarly self-taught in all of the concepts we currently use.

The difference between me and them is that most of them have a degree in a field that is semi-related to what we do today. Until very recently, version control systems, design patterns, and proper debug techniques were not taught in college. All of these things were used in the field. Yet, these three topics form the core of the profession.

I was lucky in that the self-help resources I used to learn also incorporated these aspects as I went along. I had a strong understanding of VCS and debug long before officially joining the team. I knew several patterns based on the applications I learned to build.

I was humbled when I learned that the feature I believed set me apart actually included me. My inclination to research new terms and strive to understand new concepts is mirrored across much of the team. The really good developers all do it. I'm not one of the 'really good ones', but I've become a relied on resource for some tasks. Also, I continue to meet individuals with computer science degrees who have done nothing with it after earning it. The degree, for them, became a spring board into something else - most frequently management. I have no issue with this - in fact I find it encouraging - as though there is a viable exit strategy if this all gets old.

Some say that they pursue additional knowledge in software development out of love for it. Personally, in my interactions with fellow developers, I see more pain than love. Yes, many of us develop because of pain. The most common reason to do something is because someone says "God, that was awful. Lets try to automate/document/fix this so we don't have to do this ever again." What my team loves is when everything runs well and no intervention is required. What we love best is when the people who rely on the system also trust the system - and trust us.

So it makes sense that we try to stay abreast of new technologies. I constantly look for that new concept that can change the way I look at problems. More experienced devs look for entirely new paradigms. Some skip from new truth to new truth like mad philosophers. So far, I've fought to understand my current technology more deeply. I'm told that this understanding comes more easily with time. I can hope for that.

When knowledge takes root within me I feel like I can begin to understand how the world grows around me in a whole new way. The applications that previously seemed to function by some sorcery now follow a discernible pattern. The majority of the plumbing for most applications is discovered in the field. The university classroom cannot teach much of this - particularly since a developer tends to become specialized well after they receive their diploma.

Those who have only recently graduated and are joining their first team will find that what they were not taught will astonish them. The landscape for software development sometimes seems to resemble a perpetual classroom with more ongoing education than any medical doctor - or similar profession.

Trained to Feel Joy Part 2

The orange frog training completed on Tuesday. I know I said that I would write a blog post for you immediately afterwards. However, that was impossible. I could not decide if it was a good or bad experience.

I’m still confused, but here are my thoughts.

The good:

1.       It was nice to break from routine.
2.        It was fun joking around and talking to my co-workers. 
3.       .     I got to know a couple of managers a bit better.
4.            Some of the training techniques seemed like they could be useful
1.       .   Such as listing different things you are thankful for daily.
2.       Also, writing a small paragraph of something good that happens to you daily.

      5.       The breakfast and lunch for both days was excellent and free.

The bad:

      1.   All day training two days before quarter end just overloaded my list of critical things to get                         done on 9/30.

      2.   The training was either rehashed Agile principles or “in an ideal world” type scenarios.

1.      I un-ironically got to use the “you miss 100% of the shots you don’t take” line during training. I will now credit Michael Scott who quoted Wayne Gretski.

       3.  The complete lack of seriousness with which we treated the training seriously limited its                              effectiveness.

             4.  It revealed the animosity that some people in our build lines have toward the company’s                              direction.

             5.       Everyone, not just me, resented the way the training was introduced.

It was clear that had we only been given a choice and clear incentive to join the training then it would have been more effective. As it was, it became a platform for complaining about the state of our application and its inevitable replacement by the next shiny thing.

One thing the training got right was the element of choice. “You get out of this what you put into it.” They said. This was absolutely true. I picked out the pieces that I thought were useful – but there was an element of incredulity that nullified a significant chunk of the training.

How can we trust a management team that seems to never acknowledge the problems we face together? When our legitimate concerns about keeping the promises of our company are met with big smiles and helpless shrugs? And now we are told “Just be positive.” It’s an attitude of “More woohoo less boohoo” within a management team that I have witnessed before.

When I was a financial analyst on the annuity side of our business we were facing a serious crises in morale as a result of outsourcing and the general shaking up of our teams as well as a blending of departments. We were greeted with the same sorts of managerial indecision. At the time, I was not very charitable and I labeled it as cowardice.

I was wrong. Management acted that way because they were helpless to do otherwise. Positive changes were being promoted, but change occurs slowly. Across the department, everything improved. People grew and that growth was recognized.

So now, we have Orange Frog. Be positive, because we don’t know what will happen. We can face this more easily with smiles than by mourning for something that must change. I find it difficult to argue with that message.

It says a lot about a company, when they invest such a large chunk of resources toward trying to promote happiness within their employees. Make no mistake, this was a very costly training. The lost production, the speaker fees and the course fees probably resulted in a total expenditure in the tens of thousands.

Our executives, at least some of them, appear to really care about what we think and whether we are happy. I find that, at least, encouraging. 

Trained to Feel Joy

I received an email a few months ago. It went like this:

***

CC: My manager

Re:Orange Frog

James, why have you not signed up for this mandatory training yet? Training is on Sept 28 and 29. Please respond.

***

I responded right away that I had not received the meeting invite. It was extended and I accepted. It was mandatory after all. I had no idea what it was about though,

Later, I was presented with a soft cover book with illustrations reminiscent of a child’s story book – and prose to match. I felt resentful that I being provided with literature that seemed more appropriate for small children. Prior to the email addressed directly to me, I had not noticed any invitations. I had not seen any communication about the event. This was all a surprise, but I chose not to challenge it. 

I had worked for the company for nearly nine years, but this was my first year in the IT department. My previous 8 years were in the operations department, where I worked directly with customers and sales people. I was used to directors with strong personalities and managers with unapologetic hungry agendas. I was also used to a culture where people generally cared for each other, and took pains not to make poor impressions on one another.  The IT department was a bit of a shock since generating good first impressions was not a priority - or at least it depended on one's rank and seniority within the department. Perhaps, for me it reflected my social standing - which I must acknowledge could not  be very good, given that I was slow to recognize the culture differences and suffered for it.

I call that a learning experience.

Also, I considered my impressions for the training to be merely an offshoot of that. 

I am asked to deliver an Orange Frog book to another developer – which I do. He glanced at it and threw it disdainfully aside. I laughed like it was a joke, but I was the only one laughing. I wondered if he felt the same resentment I did. I wondered if he directed it toward me since I delivered the book.

Then I read the book and I watched Shawn Achor’s TED talk. I have got some idea of what the intent of this training is to be. I have heard of positive psychology before, but I found it distasteful. It felt like a lie.

Nevertheless, I am determined to keep my mind open. I don't want to shut out a good message based on my impressions of how the event was organized. And, as I said, I read the book, which wasn't too bad. 

Clearly, if anything of the above interaction reveals anything - we need some training in positive psychology. It feels that our management too often views us as teams of intractable kindergartners, while we ourselves resemble that in as much as we act like curmudgeons. 

So, prior to going to the event - here is my impression of the book:

It is a parable wherein the narrator imagines himself to be a naturalist in a strange place called “The Island”. The Island consists of four different ponds. Its main features are a large population of anthropomorphic frogs (at the beginning of the story they are green), a migratory population of heron - who are predators of the frogs, and an annual weather pattern of heavy rains – called “the deluge.”

The parable focuses on the doings of the frogs that inhabit three of the four ponds. The frogs self segregate into distinct social groups which are

1.         The workers, who industriously, sometimes blindly build.
2.     The slackers, who never work but are always lounging around.
3.     The despairing, who don’t work or play but instead despair for their situation.

In particular, the parable focuses on one frog, named Spark, who has started to turn orange. As a result of turning orange he becomes a pariah amongst the other frogs. They believe the “orangeness” is a survival disadvantage.

Furthermore, the naturalist narrator lets us know that he believes the other frogs used to be multi-colored but were infected with a disease called “the thrall” that turned them all green. The thrall also had the effect of causing the various behavioral effects listed above.

As Spark comes of age he, and others in his class, are pushed into the uninhabited fourth pond. This pond is uninhabited because it is a breeding ground for herons (who are mostly absent, being migratory).

Spark finds that as he participates in despair or fatalism then he becomes less orange. However, if he appreciates beauty, or feels gratitude then he becomes more orange.

So, being orange colored is a metaphor for being joyful, or positive – happy. Being green suggests a lack of joy or any positive emotion. Spark discovers how to teach other frogs to be joyful. As other frogs experience joy they also become orange. They then find ways to overcome various obstacles (herons, the deluge) as a result of either being orange or their new found attitude.

Are you with me so far?

Like most parables, there is a lot of symbolism and metaphor sprinkled throughout. Some are obvious, like the frog’s name “Spark”, or the herons, or the deluge, others are not so obvious – like the significance of the uninhabited pond.

So, some of the ideas within the book and my interpretation –

1. The Thrall. Negative (read not Orange) frogs are discussed to be under the influence of a disease called “the Thrall.” A thrall is a noun and describes a creature that has been enslaved http://www.merriam-webster.com/dictionary/thrall The suggestion is that frogs who are not joyful are slaves to their attitudes. In fact, the three camps – workers, slackers, and the despairing are shown in the story to be completely consumed by their defining attitude. It is their only defining characteristic.
2.  The main character, Spark, is born feeling gratitude. His name is suggests that he is the start of something (a fire?). His friends are similarly named.
3. The herons and the deluge represent bad times. They are the events that typically make people miserable. In this story these events are somewhat predictable.

I’m sure you get the idea. It’s a simple story and the message is clear. A positive attitude results in a better ability to face up to challenges, and even disaster. A positive attitude is infectious.

But there is more here, a positive attitude is smothered in an environment where most are negative. A positive attitude requires time to reflect in order to gain footing and eventually succeed. Consider the fact that had Spark been placed in any one of the ponds that were already inhabited, then his attitude would have been squashed. The story bears this out given how the frogs respond to his attitude, his “orangeness”. They are not welcoming. This is true in real life for people with a positive attitude. They are not welcomed in areas that have solidified their negative attitudes. A positive attitude needs space.

Furthermore, a positive attitude does not mean constantly happy. This probably runs contrary to Shawn’s views. It means that you can acknowledge that something awful has occurred and move past the grief and on to something constructive. Whether it is to prepare for a future disaster, or to recognize that what had been done before doesn’t work. Joy is an ever fluctuating state which is deepened by adversity.

It’s surprising to me how clear this message is, as it seems that it might run counter to the interests of my company.

The message is, if you are unhappy where you are – you must leave or change. You cannot be made happy by remaining static. Any joy you achieve will be destroyed by the people around you who do not share in it. The fact is, they will find it threatening.

I sincerely hope that I am able to take to heart this message. The more I reflect on it, the more I wonder if I've given myself enough space to find joy. 

What I fear is that this message will be subverted. I fear that my management will adopt the language of the training, but none of the underlying wisdom. We will be inundated with Orange Lists and platitudes. We will be able to pay $400.00 for special Orange training and become Orange Masters. I fear that this will all be made into a mere commodity, bench marked, tied to performance evaluations and eventually completely made meaningless, much like the Gallup Engagement surveys that precede it.

I will report how I feel directly after the training, and again several weeks later after some reflection.