How 1 VBA Error can Destroy your and your user’s Company

//How 1 VBA Error can Destroy your and your user’s Company

How 1 VBA Error can Destroy your and your user’s Company

We programmers do not pay enough attention to error handling, but did you know that only 1 typo in your code can cause incredible damage.

 

“Houston, we have a Typo”

One of the most expensive code error in history, the cost in today’s dollars: $135 million, was when it led to the destruction of the Mariner 1 spacecraft in 1962. Mariner 1 was the first spacecraft in the American Mariner program. It was launched on July 22, 1962 from Cape Canaveral by NASA, the mission was to collect a variety of scientific data about Venus during a flyby.

Just 293 seconds after launch, a range safety officer ordered a destructive abort when it veered off course after an unscheduled yaw-lift maneuver. The destruct command was sent six seconds before separation, after which it could not have been destroyed.

A review board later determined that the omission of a hyphen in coded computer instructions allowed the transmission of incorrect guidance signals to the spacecraft, and the Code Error was called “The most expensive hyphen in history”.

 

Failed conversion from English units to metric

In 1998 the Climate Orbiter spacecraft was ultimately lost in space, on its mission to Mars. Although the failure bemused engineers for some time it was revealed that a sub contractor on the engineering team failed to make a simple conversion from English units to metric. The cost of this error was $125 million.

Just the tip of a very large iceberg

These are just a few examples, but the truth is that these are just the tip of a very large iceberg. Every year, software errors cause massive amounts of problems all over the world.

In the year 2002. the National Institute of Standards and Technology commissioned a study where the results have shown that software errors cost the US economy $59.5 billion every year. And this is only for the US.

 

My Story

I’m in software development business almost for 2 decades now. And in this years I learned that creating a killer application is only the first part of software development. The next very important one is the support.

Having angry customers is the most painful thing that your company will eventually experience. It is only a matter of time when this will happen.

An error in your code can not only angry your customer, but it can also cause great problems for both, him and you (No matter how good your EULA is, or how many lawyers you have).

Error handling is a very important thing in any developers code. Testing your code, logging the errors to a file, displaying the error message, this are all good approaches. But……

If you are already in software development business then you know how a conversation with a user is going to be when he/she is trying to explain what’s going on when an error occurs.

I know, there are programmers that are saying that their code works without errors (“Yes of course”).

 

The Past and the Present

In the past I have used tons of methods to deal with errors, from logging to a file, taking the auto screenshots, sending auto emails,…..

Microsoft Azure team has created this thing called Application Insights (there are thousands of similar solutions on the Internet, but Microsoft is, for now, the solution that I prefer). It is a Microsoft Azure cloud application that enables us to track what is going on in our mobile, web, server or client applications.

Because in the last years my company concentrates on building solutions in VBA, so I wanted to bring this to VBA users.

Out of the box Application Insights (and the Hockeyapp team) they support many of platforms, but not the VBA, I contacted with them, and they don’t support this platform out of the box.

 

The Birth of VBA telemetry client

So we created a small tool that connects the VBA with the Application Insights client located in the .NET framework.

We call it VBA Telemetry client.

This is a small dll that enables us to send some data to the Microsoft Azure – Application Insights resource (to the cloud), right from VBA.

What does it do

When you install this small dll (called VBA telemetry client) on your or your user’s machine you can use the functions that this dll provides.

There are (for now) 5 main functions or subs that we can use:
TrackError, TrackEvent, TrackMetric, TrackPageView and Flush

If we put TrackError into our error handler code, and when this sub is triggered, it sends the error data to our Azure portal. We can see this error data almost in real time in our Application Insights resource.

In our Azure portal TrackError sends the Error number, Error description, and a small description, for example the procedure name.
Example:

Code:
TrackError Err.Description, Err.Number, "CommandButton1_Click"

If we put TrackEvent, for example at the beginning of our subs, or functions we not only can see how many times the procedure has been triggered and from what user (it sends a lot of data about the user machine by default computer name, country, town, IP, OS version, ….), but also when the error is triggered we can follow the full stack or the path before the error has happened.
Example:

Code:
TrackEvent "CommandButton1_Click"

We can use TrackMetric to, for example, to track the duration of a code block execution, or to pass some metrics with the name and value.
Example:

Code:
TrackMetric "Loop1Duration", 100

The TrackPageView we can use to track the form load events.
Example:

Code:
TrackPageView "FormCalculationOpen"

And at the end there is a Flush function. Because the AI does not send the data immediately, but it stores it in memory and sends them from time to time (from few seconds, to a minute or two). We need to add this Flush before we exit our app so that the VBATelemetry client & Application Insight client can flush (or send) the rest of the data before we exit.

 

Help modules

But to make it even easier for developers to use VBA Telemetry client in their VBA projects, there are 2 help modules.
One module that holds the variables and one that simplifies the usage and calling the functions.

 

First module: modVBATelemetryFunctions

In this module, there are functions that developers can use in their code.

So when some changes to this functions are made or when a new functions or subs are added, you only need to replace this module.
Also if you take a look into the subs and functions in this module (modVBATelemetryFunctions.mod) you will see that the initialization of the dll is done everytime from the beginning right inside the function or sub. This is because in VBA the user can switch between Design and Runtime mode, and when he does that, all the object get destroyed.

And I use late binding, in those modules, so that if on this machine the VBA telemetry client (the dll) is not installed there are no resource errors raised.

 

Second module: modVBATelemetryVariables

In this module are placed the variables that we need to setup to be able to use VBA Telemetry client in our VBA project.

We can have a new resource (new project) in our portal Application Insights, for every application we want to track.
To identify the different projects (or resources) in Application Insights, the Application Insights gives us the “Instrumentation Key”, we simply copy it from Application Insights to this module. And our app now knows to which Application Insights resource to send the data to.

Get VBA Telemetry client for FREE

Do you have any experience with angry customers? Or some tips on Error Handling in VBA?

  • 10
  •  
  •  
  •  
2017-08-07T19:51:56+00:00 Tips & Tricks|1 Comment

One Comment

  1. Davor Geci 08/05/2017 at 14:31 - Reply

    Hey Everyone,
    I forgot to mention that I recently released a full guide on how to setup VBA Telemetry & Azure Application Insights.

    Here is the link:
    https://www.vbatelemetry.com/full-guide-setup-vba-telemetry-azure-application-insights-track-usage-excel-workbooks/

    Let me know what you think. 🙂

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.