This is a detailed guide for beginners and advanced users. Connect Excel, Access (or any other Office document that uses VBA) & Microsoft Azure using VBA Telemetry.
It describes the steps you need to follow to be able to use the Azure Application Insights from within your VBA Projects (it also works with old VB6 projects).
If you want to skip most of this steps, you can use one of our samples.
Or if you want to follow the instructions in video format, you can take the Udemy course (90% OFF for limited time):
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,…).
Steps you need to follow to make it work:
6. Insert, into your VBA Editor, the lines of code to track Errors, Events, track Page Views (or in our cases Forms and Sheets), track Metrics (you can use this to track the duration of your code blocks or to pass some metrics with the name and value)
Let’s dive into details
1. Create a FREE Account in Microsoft Azure
As I write this article, on Microsoft Azure – Application Insights Pricing page they are saying that you can create a FREE account and with this FREE account you will get (quote):
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.
To do so, go to https://www.vbatelemetry.com/downloads/ download VBA Telemetry Client and install it.
You can freely redistribute this setup to your user’s computers. Because this is needed to be able to send the data to your Microsoft Azure – Application Insights resource.
Note: VBA Telemetry Client needs Microsoft .NET Framework 4.6.1 (x86 or x64)
So VBA Telemetry Client can be installed only on Windows that support this .NET Framework.
3. Download the 2 modules: modVBATelemetryFunctions.mod and modVBATelemetryVariables.mod and import (or copy paste) them into your Workbook VBA
You can skip this step if you are using Samples from our Download section (the sample files already contain this modules).
If you want to start from scratch with new Excel Workbook, or want to incorporate tracking into your existing Excel Workbook, you need to download these 2 modules from our Download section.
And import them into your VBA Editor of your Excel Workbook.
Go into VBA Editor by clicking on Developer Tab and then on Visual Basic button.
If you don’t see the Developer Tab you can simply use the shortcut keys Alt + F11
Or follow the instructions on how to show the Developer Tab here.
Then in VBA Editor click on File > Import File…
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.
Now click on Create.
Give Microsoft Azure a few seconds to create your new Microsoft Azure Application Insights resource.
Now your new Microsoft Azure – Application Insights resource is ready!! The name of the resource we created here is MyApp1.
5. Copy & paste the Instrumentation Key (from Azure portal) into your Workbook VBA modVBATelemetryVariables module
Now that you have created your Application Insights resource, you are ready to connect Microsoft Azure & your Excel Workbook!
(Soon you will be able to lay down and watch how your usage data flows into your Microsoft Azure portal)
Just a few steps we need to do.
In your Application Insights resource, that we named MyApp1, scroll down and click on Properties.
Here you can see the Instrumentation Key for this resource (MyApp1). Every Application Insights resource is having it’s own Instrumentation Key.
Copy this Instrumentation Key (you can click on a little button next to the textbox with the Instrumentation Key).
Now open your Excel Workbook where you imported the module (modVBATelemetryVariables) and go into the VBA Editor and open the module (by double-clicking it).
On top of this module you will see this line of code:Public Const m_InstrumentationKey As String = “”
Insert the Instrumentation Key from Azure Application Insights resource right between double quotes.
Save the Excel Workbook.
Your Excel Workbook & your Microsoft Azure Application Insights resource are now connected!!!!!
You can reopen your Excel Workbook.
6. Insert, into your VBA Editor, the lines of code to track Errors, Events, track Page Views or track Metrics
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.
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.
7. Watch how your tracking data flows into Microsoft Azure Application Insights resource, ready to be viewed and analyzed
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
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,
… drop us an email: [email protected]
… contact us via our Contact Us Form
… are you currently on Facebook? Send us a direct message on our Facebook page
76 48 15