What is VBA Telemetry?

Let’s say that you’ve created a killing Microsoft Excel Workbook. Then you deliver this Excel Workbook to your users.

But how do you know if your users are getting any errors or what parts of your Workbook is used the most, which function, routine or part of the code is executed faster or slower on your user’s computers, or is it used at all?

Now you can, with help from VBA Telemetry and Microsoft Azure Application Insights.

In one sentence: If you are familiar with Facebook Pixel or Google Analytics, then you know what I’m talking about, this is similar but for your Office documents (Excel Workbooks, Access projects, Word documents, Powerpoint presentations,…).

1. Create a FREE Account in Microsoft Azure

Application Insights offers two pricing options, Basic and Enterprise. With Basic, you pay based on the volume of telemetry your application sends, with a 1 GB free allowance per month. This free data allowance gives you a great way to try out Application Insights as you get started, and it also allows you to use Application Insights for free on an ongoing basis for debugging and for low-volume applications.

2. Download VBA Telemetry Client (it is FREE)

To be able to send the data you want to track you need to install a small utility on computers that you want to track.

Find the modules on your computer (where did you download them) and click Open.

Save your Workbook.

4. Create a new Application Insights resource in your Azure portal

Login into your Microsoft Azure Portal.

Now we need to create a new Application Insights resource that will be home for our data.

In Microsoft Azure Portal click on New button (upper left corner).

Search for Application Insights, and then click on Application Insights.

Now click on Create button (down left corner) to create a new Application Insights resource.

It will open a section where you can enter data about your Microsoft Azure Application Insights resource.

I will explain basics of this fields. You can get more details in Microsoft Azure Application Insights documentation.

Name: This will be the name of your Application Insights resource. My tip is to name your resource with the same name you have named your Excel Workbook.

Application Type: Here you can select what default charts you want to see. My Tip is to select General because you don’t need the charts that will be blank like the ones if you select ASP.NET web application. But you can experiment with this by creating new Application Insights resources.

Subscription: This is the subscription that you have with your Microsoft Azure Account.

Resource Group: If you don’t have one, create one.

From Azure documentation:

A resource group is a container that holds related resources for an Azure solution. The resource group can include all the resources for the solution, or only those resources that you want to manage as a group. You decide how you want to allocate resources to resource groups based on what makes the most sense for your organization. Generally, add resources that share the same lifecycle to the same resource group so you can easily deploy, update, and delete them as a group.

Location: Choose the location where most of your users will come from. Basically, you choose the location of the server where your Application Insights resource will be located.

Pin to dashboard: If you want that this Application Insights resource be available from within your main dashboard then tick this box.

Not only you can Track Errors and add custom events, you can also use other functions from this module (modVBATelemetryFunctions.bas) to send data to your Azure portal Application Insights resource.

Functions that we can use:

TrackError(strErrorMessage As String, lngErrorNumber As Long, strErrorStack As String)

We can use TrackError, to track all the errors that are raised in our code. We can see the error description, error number and the function where this error occurred. With some more code we can also get the full Call Stack. How to do this I will explain in another article on Advanced Error Handling.

A simple sample how to use TrackError function can be found in our Download section in “TrackError Simple Sample.xlsm” Excel Workbook.

With some more code we can also get the full Call Stack. How to do this I will explain in another article on Advanced Error Handling.

TrackEvent(strEvent As String)

We can use TrackEvents, for example, to track events our users are triggering.

A simple sample how to use TrackEvents function can be found in our Download section in “TrackEvent Simple Sample.xlsm” Excel Workbook.

TrackPageView(strPageView As String)

We can use TrackPageView, for example, to track what Userforms or Sheets our users are opening.TrackMetric(strMetric As String, dblValue As Double)

We can use this to track the duration of our code blocks or to pass some metrics with the name and value to our Application Insights resource in Azure.

Flush

We need to add this Flush to the Workbook_BeforeClose event so that the VBA Telemetry client & Application Insights client can flush (or send) the rest of the data while we exit the workbook.

Because the VBA Telemetry Client and Application Insights Client are not sending data to Microsoft Azure immediately when it happens, but it stores this data in a buffer and from time to time it sends it in chunks. This is why we need to tell VBA Telemetry Client that we are done and that it can send (flush) the rest of the data to Azure.

Because VBA Telemetry Client and Application Insights Client don’t send the data immediately when it happens, but it stores this data in a buffer and from time to time it sends it in chunks, we need to give Microsft Azure Portal some time to show us our tracking data. In a minute or few minutes refresh your Application Insights resource and you can see your tracking data.

Some of the available reports in Azure Application Insights for Tracking Errors

Azure Application Insights Reports Tracking Errors

Azure Application Insights Reports Tracking Errors -Error Description

Azure Application Insights Reports Tracking Errors -Error Details

Some of the available reports in Azure Application Insights for Tracking Events

Click on the Events Tab and you can see the 2 Events that we have made with opening and closing our Excel Workbook for few times.

You can see our events “WorkbookBeforeClose” & “WorkbookOpen”

Here you can see users and their computer names. Because I’m the only one running this sample now, here is only my data. I’m from Croatia and my account on this computer is called WinPIS.

You can also open the chart in Analytics by clicking a little icon next to the chart.

This will open the Analytics.

In this query, I have added also, from the left pane the fields appName and application_Version to show it. And then press Go.

Here you can analyze the data more deeply and create your own queries on the data and show it in Table, Chart or even export it to Excel, Power BI Query,….

More on analyzing data that is in your Application Insights resource we will talk in next articles.

Have some questions? Let’s talk.

If you have any questions, can’t get something to work? Don’t waste your time,