I can imagine some of you are still out there having to maintain a project built for Microsoft Access, and having all sorts of fancy features written in Visual Basic for Applications. I am one of you.
So since Access is a nightmare to do proper debugging and error reporting once you’re used to modern tools, and I find I rely too much on my client to give me proper feedback, I decided to see if I can make life easier.
As a little use case, we’ll use the following code:
My goal is to have each error reported automatically in Sentry, and have a screenshot attached to get a view of what might have been happening at the time of the error. There should also pop-up a message at the client’s end saying an error occured, and that all power has been diverted to solving it.
Taking screenshots might involve storing information which should not be stored outside the client’s systems, and perhaps should never be seen by anyone. Make sure you take that into account.
Getting the error
There are various ways to get the location of the error, some more precise than others, but they all involve some clunky and awful mechanism. You can get the line number generating the error, and parse that using the
Erl object, by prepending each line (or perhaps just the lines that you expect to go wrong someday), but… No.
So since I’m not looking forward to, next to the code itself, start maintaining line numbers, I look for different ways to get the error location. I’ve written a global
Public Sub ErrorHandler(), which I’ll explain below. We’re going to call this whenever an error happens. But that still leaves us wondering where the error took place, since the
Err object only contains info about the error, but nothing about its origin.
For that we need to add some context. The next best thing is to provide a name to our
ErrorHandler(callerName As String). In each call to
ErrorHandler, we’re going to add the name of the caller, since that will be globally unique. There’s no way to get the name of the function programmatically, so to still do this relatively easily when you have many code blocks I recommend using the template function in MZ-Tools.
The template is:
Which for our case would result in:
Now that we have our error handling in place, and a name to have a hint to the source of the error, we can add to the knowledge about the error by getting a screenshot.
Getting a screenshot
Since asking for proper screenshots isn’t always doable for a client (getting blurry photos of a screen, missing the information you need, isn’t helpful). So we’ll be writing a function that takes a screenshot, and prepares it for sending across the internet.
There are various ways to get a screenshot. You can have a screenshot stored in the clipboard or write it to a file directly. You can use shell functions to make that happen or just send keys as if the user is doing it. But man… Finding out how that works pops up examples which are clunky, unreadable, and contain way too much information. So I don’t have a clue what the code is actually doing, and why. What’s worse, most of them assume early binding, which in my case always seems to cause more problems than they solve. So this example only uses late binding and has no external dependencies.
We’re going to use a relatively new screenshot function in Windows, by having it written directly to a file. This may not work on all Windows flavours. This way we don’t have to interact with the clipboard, which I found to be unneccessarily complex.
First off, we need to declare we’re going to use keyboard events in our code, and tell the code how that would work. For this we declare the following above any code blocks.
Options for clarity.
We’re going to use 2 keys, and 2 directions.
VK_SNAPSHOTis the PrtSc key,
VK_WINis the key, and
KEYEVENTF_KEYUPis letting go of the key. The key down event doesn’t need a
Now we take a screenshot inside our code block.
Simple as that. It will be stored inside a standard location -
%USERPROFILE%\Pictures\Screenshots. Let’s just hope your client hasn’t changed that for some reason. This location is language independent, so you can use this code on any locale.
Sending the screenshot
My challenge here was to get a binary across the internet using only VBA. For this I decided to encode the image as base64, and send that as an HTTP payload.
First we get the latest
.png from the screenshots directory.
We assume a screenshot was taken. If no images were found, you can test that using
MostRecentFile = "", and act accordingly.
Now that we have the full path to the latest file in
MostRecentFile, we will encode it to base64. First we load it into an array of bytes.
Now we can encode it to base64.
And send it along. At
https://example.com/upload_image I have a Flask server waiting to receive a
POST, to process the image further. It sends a response with a unique URL where the screenshot can be found. We can use that in our error reporting code in the next section.
Sending the error
Now it’s time to send our error somewhere. I decided to send it to my own instance of Sentry. You can of course also send it to for example the GitLab API, whatever works for you.
Public Sub ErrorHandler(callerName As String)? We’ll be using that to send our stuff to.
Sentry expects a JSON containing the error information. So we’re going to build that based on the information we have. This has some quirks, since VBA
Strings only allow double quotes around it, and JSON really wants to have double quotes as well. We will solve that using a simple
Replace(data, "'", """").
There are various things going on here:
Err.Number- This will give you the unique VBA error number so you know exactly what kind of error was triggered.
Err.Description- This is the error description by VBA. It may contain single quotes, that is why we use backticks when building the JSON.
callerName- The name we set to reference the code block calling.
TempVarI use to store the Windows username of the account which encountered the error.
Err.Source- The name of the Access Database application.
image_url- The URL of where I stored the screenshot we stored earlier.
TempVarI use to store the version of the application, as the short Git commit SHA.
Now that we have a beautiful JSON string, it needs to become proper JSON so we can send it. For this we need to make
"s out of the
`s. We do this with a simple
Now we can send our payload. First I set the url and then use an
MSXML2.XMLHTTP object using late binding. This should be quite self explanatory.
sentry_clientstring can be anything. It is used to identify which Sentry SDK sent the payload.
And now your error pops up in Sentry. Give it a try!