With every new version of Excel (and other Office suite applications), Microsoft is adding new security enhancements. While tighter security protects you from evil viruses, it also prevents you from using powerful third-party applications and routines within Excel.
With highest security, our Excel workbooks may generate error messages such as “Can’t find Project or Library” or “Programmatic Access Denied.”
If this happens to you, it may be a security issue. In the latest versions of Excel, two security options must be properly set. Pull down the Tools menu, click Macro, and click Security. This will bring up the Security dialog box.
1) Set Security to Medium.
Make sure, as shown in the graphic below, that security is set to Medium. If it is set on High, it must be changed or the Visual Basic code in our workbooks will not work.
2) Set Trust Access to Visual Basic Project on.
Click the Trusted Sources tab and make sure the Trust Access to Visual Basic Project is checkmarked. If not, it must be clicked on, as shown in the graphic below, or our Visual Basic code will not work.
Article Q282830 on Programmatic Access Denied in Microsoft’s Knowledge Base says: