I have an Excel spreadsheet which uses the solver add-in. I've developed it on a Windows 7 64 bit machine. The spreadsheet relies on some VBA code to set up the solver so I need a reference to solver.
The problem I run into, is that since I've developed it on 64 bit Windows 7, the reference points to the solver in
C:\Program Files (x86)\Microsoft Office\Office12\Library\SOLVER. When some of my users try running the spreadsheet on their machines, the VBA throws some strange errors because the references are wrong. (Strangely they don't show up as Missing).
I've tried adding some code to remove the reference and then add it back in, but it's a mess because then you have to worry about how the security settings for Excel are set up on the machine. (i.e. my code requires that the "Trust access to the VBA object model" be set). That isn't that much of an issue, as I have complete control of the machines in the enterprise, but I'm not sure how to automate setting that flag on all of the machines.
Does anyone know how to set the "Developer Macro Settings": "Trust access to the VBA object model" via GPO or registry? Otherwise, what can I do, either by modifying the spreadsheet or by performing some action automatically on each machine, to deploy that spreadsheet?
Any experience in using Excel with Solver on both 32 and 64 bit OSes would be appreciated.