View Single Post
  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

Last year I encountered great difficulty when using Solver through VBA.
The problem was pronounced when a workbook was used on different
computers with different versions of Office and Solver. While trying to
fix everything, I learned all about how add-ins are supposed to work,
how to install and uninstall ad-ins and set references to them through
VBA. I also learned that Solver really doesn't follow the rules.

Ultimately I had to resort to using Application.Run to accomplish the
task. While inelegant, it was very robust compared to trying to use the
"official" procedures. Here is a short example (watch line wrap in the
second line):

Application.Run "solver.xla!solverreset"
Application.Run "solver.xla!solverok", "Output", 1, , "Input1,Input2"
Application.Run "solver.xla!solveradd", "Input1", 1, 12
Application.Run "solver.xla!solveradd", "Input2", 1, 7
TestSolver = Application.Run("solver.xla!solversolve", True)
Application.Run "solver.xla!solverfinish"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


wrote:

Hi all,

I have a question regarding Microsoft Excel, VBA and add-ins. The story
is as follows.

We have developed an Excel Add-In using VBA, initially in Excel 2000.
They were working fine until the computers were upgraded to Office 2003
(Company IT policy, no choice about it). They stopped working, as some
libraries which were referenced by the VBA code changed location.

Specifically, it was the Microsoft Office Web Components and the
Solver.xla. We managed to find the new locations of these files and
changed the references to match these new locations. Next we recreated
the add-ins (.xla file) from the developer version (.xls file).

The problem now is that when we install the add-in into excel 2003, it
crashes on startup. The strange thing is the .xls version works
perfectly and carries out all the functions correctly without crashing,
but the .xla version does not.

I have been able to pinpoint that it is specifically the solver
reference that causes the crash. I know this because when I create the
add-in without a reference to solver, it loads fine (though it
obviously cannot execute any functions that need solver.)

Can anyone offer me an explanation of what is going wrong. I would
appreciate any help.

Thank You in advance,

RG