MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to Keep Tabs of Your Customers Use of Your VBA Workbooks


February 25, 2018 - by Davor Geci

How to Keep Tabs of Your Customers Use of Your VBA Workbooks

You probably know or have heard of Google Analytics. A tool that tracks and reports website traffic. Similar to Google Analytics there is a Microsoft Application Insights.

From Microsoft:

Application Insights is an extensible Application Performance Management (APM) service for web developers on multiple platforms. It includes powerful analytics tools to help you diagnose issues and to understand what users actually do with your app. It’s designed to help you continuously improve performance and usability.

from Microsoft

But now with help from VBA Telemetry client, we can connect our desktop applications from Microsft Office product family (Microsoft Access, Microsoft Excel, Microsoft Word, Microsoft Powerpoint,...) to Application Insights.


So, what is VBA Telemetry, in one sentence, please?

VBA Telemetry enables you to Track Events, Errors & Metrics from your VBA projects (Excel workbooks, Access applications) in Real-Time by connecting it to Microsoft Azure Application Insights.

How to Track Events with one line of VBA code

You can track events with one line of VBA code:

You can track events with one line of VBA code
You can track events with one line of VBA code

After those lines of code been executed we have in our Microsoft Azure Application Insights resource:

Application Insights Usage
Application Insights Usage
Application Insights Users
Application Insights Users
Application Insights Events
Application Insights Events

Here is a short Youtube video (45 seconds) on how to Track Events within your VBA project by sending telemetry data to Microsoft Azure Application Insights resource using VBA Telemetry client:

How to Track Errors with one line of VBA code

If we want to track Errors we would insert one line of code in our error procedure, example:

Insert One Line of Code in the Error Procedure to Track Errors
Insert One Line of Code in the Error Procedure to Track Errors

After those lines of code been executed we have in our Microsoft Azure Application Insights resource:

Azure Application Insights Reports Tracking Errors -Error Description
Azure Application Insights Reports Tracking Errors -Error Description
Azure Application Insights Reports Tracking Errors -Error Details
Azure Application Insights Reports Tracking Errors -Error Details

Here is a short Youtube video (50 seconds) on how to Track Errors within your VBA project by sending telemetry data to Microsoft Azure Application Insights resource using VBA Telemetry client:

How to Log some custom Metrics with one line of VBA code

If we want to log some custom metrics, for example, to track loop or procedures durations we can do it with using the TrackMetrics function and passing the Metric name and the Metric value, like this:

TrackMetric Function
TrackMetric Function

After those lines of code been executed we have in our data in Microsoft Azure Application Insights resource.

But to be able to see and query this data we need to go into the Advanced Analytics. In your resource Overview click on Analytics:

Overview Analytics
Overview Analytics

This will open a new application “Application Insights Analytics” where you can query all your data in this resource.

Application Insights Analytics
Application Insights Analytics

In Application Insights Analytics you can write your own queries using the Analytics Query language and represent them in various visualization forms (table, charts, export to csv, to Power BI (M Query),...)

Here is the query for data collected in last 63 minutes in our example represented in a timechart and ordered by timestamp:

Custom Query
Custom Query

Just a preview of translations of the most common idioms between SQL and Analytics Query:

SQL Query vs Analytics Query
SQL Query vs Analytics Query

Here is the link to the Cheat Sheet: https://aka.ms/sql-analytics.

Here is the link to Microsoft Analytics in Application Insights documentation.

Want to find out how to connect VBA with Azure?

Full course on how to open a FREE Microsoft Azure account, create your Azure Application Insights resource, connect your VBA project (Excel workbook, Access application) with Azure cloud using VBA Telemetry client and collect your telemetry data you can find in my Udemy course “VBA Application & Usage Monitoring Online with Azure cloud”. (This link is with 90% OFF discount code for MrExcel community.)

In less than 24 hours after the course was published on Udemy, there were more than 2.700+ people enrolled!

VBA Application & Usage Monitoring Online with Azure Cloud
VBA Application & Usage Monitoring Online with Azure Cloud

Want to see VBA Telemetry LIVE in action?

First ever Live presentation of VBA Telemetry & Azure Application Insights on Access DevCon Vienna.

When: Sat+Sun April 7+8, 2018

Where: Vienna

Who: Karl Donaubauer, Access MVP

Find out more here: Access DevCon Vienna

Title Photo: freeGraphicToday / Pixabay