Where did it come from?
I’ve always hated trying to handle charting in Access. The Chart objects that Access provides are ancient and clunky and nobody really uses them because of that, so our next common option is to automate Excel for charting capabilities. This is much better than the native charting, but still has a number of drawbacks. For one, it requires Excel to be installed (usually not an issue, but sometimes can be), it requires a fair bit of automation code, setting up template Excel files, etc. If you want the charts shown within your Access forms or dashboards, you have to snap a picture of the chart object from excel, save it, and load the image into Access. Again, not a big deal – it’s a very feasible solution that’s been the practice of many for a long time, but still leaves something to be desired. Besides the slight flicker in loading the images, there’s no interactivity. Even if you output data to Excel and then open the file, Excel interactivity on charts isn’t the best either.
So, over the period of some months, I was able to not only come up with a way to do so, but also to make it stable and relatively easy to use (it wouldn’t be worth much if it took 10 times as long to embed a chart than it would to automate it from Excel).
I came up with the way to do it a few weeks before the MVP Summit late last year, and slapped together a real quick demo and a terrible unprepared (and thus terrible) presentation to some of the MVPs. Despite the terrible presentation that I gave, there seemed to be a fair bit of interest, and many of those whose opinions I highly respect have indicated that this is definitely a Good Thing.
What makes it work?
One of the best things about this, IMO, is that it’s all native. No 3rd party tools, no DLLs, no ActiveX controls (as long as you don’t count the native Web Browser Control as an ActiveX, even though it actually is). No oddball references – there’s one reference, but it’s benign and added by default if you use the native web browser control anyway, so of little consequence I think. The only real ticker is that we need to make a registry edit to tell the web browser control to run in a different emulation mode (by default it uses IE7, I tell it to use IE11). However, even this worked out well… thanks to fellow MVP Tom van Stiphout, who (shortly before I pulled together all the pieces for this project) determined how to use the HKCU hive to get this functionality, meaning that no administrative privileges are required, ultimately meaning that we can implement this pretty easily with a bit of code in the VBA project.
IE Emulation Mode
So this one’s fairly straightforward: add the following key if it doesn’t already exist:
Then add a DWORD value called “msaccess.exe” for the key using a value of 0x2edf (11999) for IE 11 emulation. (the package I put together on the bemb download page has a registry module that uses WMI to check/create registry keys and values, and a BembRegistry module where you can call one function that will set this as required).
Mark Of The Web
<!-- saved from URL:(0022)http://www.dymeng.com -->
That just tells IE that it’s ok to run content on this page. Good to go on that end.
Unfortunately, this in itself isn’t quite enough. It has some major restrictions and is not the silver bullet, but it does open the doors. For one, the event you subscribe to here has to be of public scope in the DOM and can’t be referenced by a literal string value. This is a major issue, because most events that we’d want to subscribe to we’d have to use literal strings to specify.
Then there’s a matter of getting any sort of usable information from the event. There’s no support for passing information via parameters to our VBA object, and realistically, events do us little good without some information to go with it.
- Have a local html page that calls up the script required to handle a charting library
- Load that into a web browser control
- Attached a little framework script to the DOM of that loaded page. This would…
- Create a dedicated global variable in JS that we can use to transfer information from JS to VBA
My initial thoughts were a bit more loose than that, but after a long round of trial and error that’s more or less what it amounted to.
Let’s look at how the Event process works in the practical sense:
- The VBA project keeps a collection of events that we’ve told it to subscribe to. Each event is internally given an ID value.
- When we tell it to subscribe to the event, we pass a jQuery selector and an event name. This is pretty awesome because we can subscribe to every click event with class “btn” if we want.
- The “framework” file that gets added to the DOM on initialization has JS code to handle this request. Here’s what that code does:
- It runs an AddEventListener call to specify the event that we’ve told it to against the selectors that we’ve told it to. Additionally, it passes along a function (because JS is pretty awesome and can pass functions as arguments) that will run when this event is raised.
- This function that’s attached to the AddEventListener call basically says “hey, let our framework code know that this event was subscribed to by the VBA project”
- At which point, the event ID, event object (and a few other important things) are written to global variables.
- Next, the JS code looks at that hidden event bridge element that was created on initialization and fires the click event of that element.
- That click event is received in VBA. Essentially this means to the VBA project “hey, some event we subscribed to was just fired”
- The VBA project then reads the global vars from the JS framework, getting the EventID, which it cross references to the VBA collection of subscribed events.
- It finds the correct event for the given ID and proceeds to call the code we specified when we originally set up the event.
Awesome. Still needs a bit of work though… primarily, converting data from JS objects onto VBA strings can be a bit of a pain (VBA can only read global variables as strings, not objects). We could use JSON stringification, but that still leaves a lot of parsing required on the VBA end, which is kinda ugly. For example, let’s say you have a chart Click event that would get serialized to a string and that whole JSON string would be read into VBA. You’d then have to parse out that entire object to find the piece of information you want (such as: what series/point was clicked?).
What would be a lot easier would be to define a custom JS function that could run after the event is raised in JS, but before it’s returned to VBA. So I set this up also… the AddEventHandler method of the VBA BembObject class takes an optional JS script to run. Thus, in the case of clicking a chart, we can effectively say “take the event object, get the points that were clicked, write them into the main data var”, so when the VBA event is received, we just need to check the .LastData property of the BembObject and see whatever we wanted to write there.
Does it Work?
Actually, yes. Quick well in fact. After months of tinkering with it and shelving it and tinkering with it some more, I found a scenario to use it for a client and said well, let’s see how this goes.
The scenario was a monitoring system for some automated server processes that run every 10 minutes or so… I wanted to get a realtime charted display of various information (run times for the process, count of errors, etc). Each process writes to a SQL Azure event log, and this monitor would aggregate that data into hourly chunks and show me the aggregates for each hour. If I hover over an item on the chart, it’d tell me how many successful processes ran, how many errors there were, what the max runtime of the process was for the hour, and if I click on a point, it’d open a detail form in Access that drills down to the processes that ran in that hour giving me further information.
So, with the framework already worked up and used fairly intensively in personal tests, I imported a couple of objects, set up a few template functions for charting the info, extracted the data from the queries and stuffed the values into a string and passed them on to the chart. This went so well that I was absolutely tickled pink (a rare event for me… few things really get me excited). The best part? Besides this awesome functionality that we’ve never really had before… it took less than an hour to implement. Less than an hour to set up an interactive drilldown chart within Access? I couldn’t have done the Excel setup for embedded chart snapshots in less than an hour, let alone this (even if it could be done via Excel). And that hour included having to google some things like how to format the mouseover tooltips and such. That includes putting it on the client’s server and running the registry code (which worked great, btw).
That, my friends, is a success story. (the implementation was similar to that in the v0.5 demo that uses ChartJS, except I went with Highcharts instead).
Where to go from here?
Good question. While my primary intent with this whole thing was to get really good charting capability (which it does very well with), there’s a whole slew of other things it could be used for. Navigation bars will probably be my next toy project for it, but the implications are huge…
Currently, the framework is designed to well handle local pages that have been created with the intent to embed. Even so, the html page can be created in isolation, as if it were standalone, so we don’t have to worry about whether it will actually be embedded… the initialization procedures take care of appending the framework script and wiring up the events, so theoretically this could be against ANY html, local or out on the web.
One thing that I definitely want to revisit is the current handling for jQuery. As of now (v0.5), the framework assumes that jQuery will be present and makes use of that existing jQuery reference to handle what needs handling. This is fine for hosting your own local pages to be embedded, because we’ll have complete control over that page and can easily just make sure we have some reasonably recent jQuery version, but if we were to want to use this against webpages found “in the wild”, we’d need better support for that. Another area that could use significant improvement is error handling. Interestingly enough, if a VBA procedure is called as the result of a JS event and that VBA code doesn’t include an error handler, any errors raised by VBA will be propagated back through JS and can be handled there. Cool… not sure what practical use that might be, but neat anyway. On the other end, we can wrap pertinent JS code in try/catch blocks and elegantly handle errors and report messages/error info back to VBA via the main data transfer var. The current version has little of this set up (actually it has little error handling at all set up), but the options are there to expand upon, ultimately making it easier to troubleshoot JS errors that pop up as “script errors” with absolutely no helpful information.
Another tool I’m likely to try out is Firebug Lite, which can apparently be embedded into any browser on demand, so that would be of significant help in debugging as well. Again though, I haven’t done anything on this end quite yet.
All in all, I’m quite happy with how this project came together. Hopefully someone else can find it useful as well!