Signed Excel VBA AddIn (*.xlam) not running properly despite installed certificate

0
votes

This is a tough one.

We developed an Excel VBA AddIn. That's an *.xlam file. This AddIn is quite complex and transforms Excel into a software of it's own (well sort of - it still look pretty much like Excel).

Now we want to increase our security by only allowing signed macros within our company. So we created a certificate, signed our VBA AddIn macro code and installed the certificate. Actually two certificates, one as a trusted root certificate and one in the list of trusted publishers.

And now we want to change the settings in the Excel trust center, so that only signed macros are allowed to run. And this works very well with all of our Excel macros - except with the AddIn.

Here comes the tricky part. The AddIn itself starts and runs. The AddIn checks if a special type of excel file (with a certain custom property) is opened and starts an initialization process. The excel file itself doesn't contain any macros (it's an *.xlsx), but it contains numerous buttons (shape objects) and those are "connected" by the initialization procedure to other procedures (subs/ macros) from the AddIn.

MyWorksheet.Shapes("ShapeName").OnAction = "AddInModuleName.ProcedureName"

This code attaches the VBA subs from the AddIn to the shapes (buttons) in the (macro free) worksheet. So the worksheet becomes wired and functional.

This works very well when no limitations are put on the macros. But in this case with only signed macros allowed to run, the code is not excuted. The buttons are dead.

The code itself is just fine. I can trigger it e.g. with a keyboard shortcut. But the button refuses to run the code. My explanation for this is: By changing the ".OnAction" property of the shape, this itself is regarded as code - at least from a security point of view. So you could argue that I dynamically add "macros" (the OnAction property) to an excel file. And this code is not regarded to be signed. It doesn't inherit the safety level from the code it created.

Whatever the reason my be. My buttons are not working, despite the fact that the code is signed and trusted. Any ideas for workarounds (despite the fact that I could probably use the Excel ribbon instead of a self made interface)?

1
excelvbaexcel-addins
My initial question is, why do you want to limit the users use of VBA? There are so many reasons to not do that. The average Excel user has no clue of VBA, those who knows of VBA also know not to run any VBA code found on the internet. – Andreas
The application is targeted to a limited circle of users who receive a special training for this software. And there also many good reasons for not runnig macros at all. That's why microsoft implemented all the features to prevent them from running ;) – Kez

1 Answers

1
votes

I got it. I post the answer, because this may be interesting to others too.

This is the premise:

  1. We have an *.xlsx file, in other words an excel file with no macros.
  2. We have a digitally signed and installed add-in (and alle certificates properly installed).
  3. But all shape objects (buttons) in this file contain references to macros, in this case references to an add-in. This references are stored in the "OnAction" property of each shape.
  4. When Excel starts, it runs the security check before any code is executed (makes sense). Now Excel finds those strings in the "OnAction" property of the shapes and says: "Hm - that's suspicious! I better block all shapes." It doesn't block the macro code in the add-in or so - just the shapes (the triggers) themselves. And it presents a macro warning (with default security setting). This happens - even when there is no macro available. If the OnAction properties contains any string, Excel will block it.
  5. In the meanwhile my digitally signed Add-In runs just fine in the background provided that all certificates were installed. But none of the buttons is able to trigger any code (keyboard shortcuts work just fine).

So Excel has no problems with my add-in. It just doesn't want those OnAction properties to contain any strings. This is kinda strange. If Excel doesn't allow macros in the file. Why does it allow OnAction properties. Those should also not be saved in an *.xlsx file.

And this is the workaround: I added a BeforeSave event in my add-in clearing all OnAction properties of all shapes in my workbook. And an AfterSave event which adds all those strings again. So the saved file is cleaned from all suspious code.

Excel opens the file. The file passes the security check. And AFTER that my add-in code runs and wires all shapes (writes the macro calls in the OnAction properties of all shapes) and whoosh - all Buttons are fully functional.

So Excel has no problem with the shapes (in an *.xlsx file) calling add-in macro code. Andn it has no problems if this code is added during runtime. It just doesn't like those "calls" to be there during the security check at start.

Feels almost like hacking....