Dynamically Enabling Access to the VBA Object Model in Office Apps

Filed under Uncategorized

Writing addins and macros for Office apps (specifically Word, but this includes Excel and Powerpoint) can be difficult enough as it is.

But when you throw security gotchas into the mix, things can get interesting quickly.

One thing that often hangs people up are the security settings.

There are several different security levels for macros, of course. And there’s also security that controls access to the internal VBA object model.

But what’s the “VBA Object Model”?

Essentially, it’s an object model (not unlike the Word object model that starts at the Word.Application object) that allows macro code (and COM add-in code as well) full access to all the macros written in VBA and stored within the actual document (or template) file.

For instance, you can actually print out all the VBA macro code in a document with a code snippet similar to the following:

For i = 1 to WordDoc.VBProject.VBComponents.Count
   Set vbcode = WordDoc.VBProject.VBComponents(i)

   C = vbcode.CodeModule.CountOfLines

   '---- retrieve the entire code block
   If C > 0 Then
      code$ = vbcode.CodeModule.Lines(1, vbcode.CodeModule.CountOfLines)
      Debug.print code$
   End if
Next

Depending on what you need to do with your code, this can be very very useful.

But…

It doesn’t work at all unless the user checks the “Trust Access to the VBA project object model” on the Macro Security screen (shown here from Word 2007, it’s essentially the same in Word 2000-2003).

image

Now, you might think that, hey, a potentially powerful security hole like that would be something that ONLY a user would be able to change. In other words, the setting wouldn’t be readily available directly through the operating system.

And you’d be right, and wrong.

That setting can be found, quite simply, in the HKEY CURRENT USER registry hive.

For Excel:

image

For PowerPoint:

image

and for Word:

image

However, that’s not the end of the story.

It is true that the setting is a PER USER setting, which means that your application would have to alter this setting for each user that logged onto a machine. But the current user generally has FULL RIGHTS to HKEY CURRENT USER hive, so why put a security setting like this there?

Because it’s not the only place you set it.

There are also keys like these (note the version of Office):

HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Word\Security
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Excel\Security
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Powerpoint\Security

Each potentially contains a value named AccessVBOM as well.

If this value is defined and has a value of 0, the value in HKCU is essentially ignored, and the user cannot grant access to the VBOM, even if they manually enter a 1 for the value under the HKCU.

If the HKLM value doesn’t exist at all, or has a value of 1, then the user can set the HKCU value, or change the Trust Access checkbox manually in the application.

And since write access the HKLM is often restricted to local admins, this gives admins a way to lock down this setting on machines, which is a good thing.

Another important point; changing these settings while the Office app is already loaded, will not cause said app to change its behavior dynamically. Put another way, Word, Excel and Powerpoint appear to read these settings once, at load time, and then never read what’s in the registry again until all instances are unloaded and the app is loaded again.

Practically, this means that you can’t change these settings from within a true Office Addin (ie a COM addin or a VBA Macro).

But you CAN change them quite easily from other code. At least, it’s easy to change the HKCU value, and the HKLM value can be changed during your MSI based installation.

Now comes the ethical dilemma.

Your application won’t run (or won’t run in entirety) if access to the VBOM is not granted, and many people would find it difficult to change that setting, even through the UI. But that’s not the kind of setting that you want to change automatically  or surreptitiously without letting the user know you’re doing so. Or is it? Would typical users know what it meant anyway? And most Office macro viruses would be caught via other means now, right?

As a final note, googling “AccessVBOM” turns up plenty of pages describing these keys in various levels of detail, but interestingly, searching the Microsoft.com site comes up with very little on the topic.

2 Comments

  1. Ems says:

    Hi,
    In fact you can change the settings from inside VBA by setting registry key then opening another instance of Excel/Word

    See http://www.sevagas.com/?My-VBA-Bot

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*