I develop workbooks that require an addin for some of their functionality. For example, a set of school staffing models whose addin allows for selecting data sources, building pivot tables and saving timestamped copies. I like this structure because it’s very maintainable – it’s much easier to fix a bug or add a feature in one addin than it is to try to track down a bunch of workbooks and update their VBA. Easier on me and better for my users. Of course, people’s computers get re-imaged or replaced and IT departments are unlikely, to say the least, to re-install a VBA addin. Folks often don’t realize their addins are gone and that’s why their model is broken. Yesterday I got an idea about how to warn users about a missing addin. I’m still tinkering with it, but the idea is quite simple.
Use a UDF in the Addin
What I did was create the world’s simplest UDF and put it in the addin:
IsAddinLoaded = True
End Function
It doesn’t have to be boolean. It could be a string and return “Cult of the Flying Spaghetti.” That’s because it’s only purpose is to fail when it’s not there.
Missing Addin = Missing UDF = opportunity for IsError formula. So, for example, in cell A1 of the addin-dependent workbook put something like:
Please install it.","is Good to Go")
Then add a little conditional formatting based on the missing addin condition and you get something like this. (I tried referring to the UDF directly in the conditional formatting, but it looks like that’s not allowed.)
The formula contains a volatile function: TODAY. That’s so if the addin is uninstalled the error is activated with the next change to the worksheet. I’d like to avoid the volatile function and I’ve tried to do this in the VBA itself, but haven’t figured out anything yet.
The message does change to the non-warning one as soon as the addin is installed.
The addin a very simple one made up just for this post. It’s called Green Eyeshade and it’s for modernizing old spreadsheets like this one. Its ribbon tab is shown when a workbook meeting certain criteria is opened. Here’s the ribbon tab:
And here’s the modernization button in action (wonderful what you can find by googling “Peltier pie charts”).
This idea could also be applied to the problem of warning users to enable VBA when opening xlsm files. I’ve never been a big fan of the “hide everything in the workbook except a warning page” although I do like Mike Alexander’s clear explanation. Anyways, something like this might work as an alternative in that case too.
Download
Here’s a zip file with the xlam addin and the target “olde spreadsheet” xlsx file.
What is that…Excelle 1805?
We should start our own cult. Cult of the flying 3d pie-chart. Jon Peltier could be the antichrist.
Hi Doug
What if the addin is installed but macros are not enabled…
Why dont you like the hide all sheets/display a message sheet option? out of intrest?
Cheers
Ross
Hey Ross, thanks for dropping by.
It still works. The functions in the addin are available even with macros disabled.
I don’t like the fact that you are saving the workbook without the user’s consent, as mentioned in the comments in the post I linked to. I’ve just always found that method unwieldy for something that’s still breakable. This method seems a little more elegant to me.
That doesn’t feel like much of an answer, but it’s all I’ve got 🙂