How do I correctly add a global macro to Excel

Question

I am trying in our installer to add a macro that is available to every worksheet. What we presently do in our wix installer is (both 32-bit & 64-bit):

msOfficeApp.Workbooks.Add            
msOfficeApp.Workbooks.Open (Session.Property("ExcelMacroLoc") & "AutoTagExcelMacro.xla")
msOfficeApp.Workbooks("AutoTagExcelMacro.xla").RunAutoMacros 1
...
<Property Id="ExcelMacroLoc" Value='C:\' />
<CustomAction Id="AssignExcelMacroLoc" Return="check" Execute="firstSequence" Property ='ExcelMacroLoc' Value='[INSTALLDIR]'>
</CustomAction>

This works sometimes, but not always. Oftentimes we get some or all of the below. What is the best way to tell Excel where this VBA script is and that it's ok to use in any worksheet?

I think the issues/questions are:

1. Is there anything in this macro (AutoTagExcelMacro.xla) that needs to change (written in 2000)?
2. Should the file's extension be changed to .xlam? And does that require any other changes?
3. How do I digitally sign the xla file?
4. How do I figure out where Excel is installed (to know where to install the macro)?
5. How do I determine the bitness of Excel (GetBinaryType does not work).
6. Is there anything else I need to do besides copy the xla file to the appropriate folder?

Also asked on MSDN (I'll post any answer from there here).

Answer 1

" @ProfoundlyOblivious Could you please post an answer telling us how to do each part best - digitally signing, trusted location, etc. and what/where we should tell people to look for macros being disabled? – David Thielen "

Disclosure: I am not an expert in software deployment, my knowledge is limited to the obstacles I encountered sharing my VBA solutions among peers on IT managed networks.

Note: The dialog boxes in your post do not all originate from the same cause. The first relates to security and the others to file availability.

---

Security

Macros have been, and still are, used in the development and circulating of malicious software. As a result macros are disabled by default and require user interaction to run.

• Macro Security Settings Macros are enabled through the Trust Center, which is accessed through the file menu. These settings are independent for each Office application and some applications may have additional options involving the suppression of a dialog box. Such as Excel where all macros may be disaabled without notification or all macros may be disaabled with notification. There are three basic settings for all Office applications summarized as:
  

  • Disable all macros
  • Disable all unsigned macros
  • Enable all macros (Not Recommended)
    

  I strongly recommend that one does not enable all macros. It may achieve a desired result but I believe the risk is far too great to warrant the consideration.

  
  

• Digitally Signed Macros A digitally signed macro in and of itself simply means the underlying code has not been changed by anyone since last saved by the signer. If the signer's certificate is installed on a computer as a trusted publisher then macros signed with that certificate can run without notification under the requisite macro security setting.
  

  • In corporate environments, IT may retain a library of certificates and there may be an established procedure for an internal developer to submit a project for wider circulation, whereafter IT signs the project so it can be run with minimal intervention.
    
    
  • In my experience however, I have found many companies do not have an SOP for this process or that the process is prohibitively onerous. At such times, I turn to self-signed certificates. A self-signed certificate if miraculous for personal development because it provides ample flexibility without incurring the risk if allowing unsigned code to run. The problem, of course, then comes with sharing your code because the recipient will not have your certificate installed. They could install my certificate but my personal preference is that I send an unsigned project and they create a certificate and sign the project themselves. This is an easy step-by-step process that requires no skill beyond the ability to follow instructions.
    
    

• Trusted Location A document saved in a trusted location can run macros without notifications. Trusted locations are added in the Trust Center.
  

  By default, XLStart is a trusted location

---

File Availability

When Excel is opened, it will try to refresh links to other data sources and add-ins. Those files must be open for a successful refresh and a dialog box will appear if the refresh fails.

Based on little more than experience and deduction, I believe this message is often semi-erroneous and driven by either the lack of a time out timer or too short of one. I have tested this with a simple worksheet and a simple add-in. The message pops saying the link could not be updated but it was open and updated by the time I manually check the status of the connection.

In other words, the dialog box was an iritation that served no value.

There is an option that suposedly offers some control over the "Links Could Not Be Updated" window with options along the lines of:

• Update links and notify
• Update links and do not notify

I have found these settings unreliable and prone to resets from crashes and updates.

To the best of my knowledge, the most assured way if avoiding this type of warning is to install the add-in after Excel is stable and uninstall the add-in before Excel closes. Of course this comes with it's on set of problems including a user perceivable slower loading time.