There are some pretty cool “on the fly” programmatic things we can do with the VBA references like deleting “MISSING” references (which break code that is referenced UNDER these missing references- that is why they need to be fixed). But to do so requires that the user checks the box ‘Trust access to the VBA project object model’ which is buried deep (in typical Microsoft fashion). The Ribbon to check this manually is in the popup window accessed by File->Options->Trust Center->Macro Settings. Instead of writing code to tell the user what to do – why not get a registry change on all the target computers first. This article gives the *hint* on how to do that. I have 32bit Office (on purpose – so I don’t forget about those users) but everyone should have 64bit. So – there are a few registry keys that should get set.

[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Excel\Security]
“AccessVBOM”=dword:00000001

[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Excel\Security\Trusted Locations]
“AllowNetworkLocations”=dword:00000001
“AllLocationsDisabled”=dword:00000000

• don’t forget to also add a HKEY_CURRENT_USER\Software\Wow6432Node version for 64bit Office installs
• don’t forget to also add a set for HKEY_LOCAL_MACHINE for all new users that might login