solver.xla problems migrating from Excel'97 to 2003

[Deborah Digby]

hi,

at work, we are currently upgrading from Win200 to XP and from Excel '97 to
Excel 2003. We have hundreds/thousands of spreadsheets that include in the
VBA projects code like:

Application.ExecuteExcel4Macro "Solver.xla..."

This no longer works in Excel 2003. It does not produce an error, but seems
to do nothing. The code is easy to rewrite by adding a reference to
solver.xla and calling the functions as normal in VBA. The problem is that I
would have to replace the code in all the hundreds/thousands of scientific
spreadsheets that have already been created. I need to have a solid credible
reason to tell my boss why I am going to need a lot more time to do my part
of the migration, more than just something like "I think Microsoft have
stopped supporting the old macro language". I need to KNOW. And that was
just a guess anyway. Does anyone know why the above code does not work in
Excel 2003, while it is fine in Excel'97? Also, is there a update/patch
available from Microsoft to fix the problem? Basically, is there any way I
can avoid having to rewrite the code?

Thanks in advance,
Chris

 

[Jim Cone]

Deborah/Chris,
Since you have no help so far, I will stick my neck out here...

XL4 macros should work in XL2003. However, Microsoft is trying
to come up with some way to eliminate them in post XL12 versions
without making too many people mad.

You mention that the code works if you set a reference to the
Solver add-in. That sounds as if your code depends on the Solver
add-in being installed by the user. A new Installation of Excel
would certainly not load any add-ins.

I would think that an IT department should be able to create and run a
script that would load the Solver add-in on networked units using Excel.
Suggest you see what they have to say.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Deborah Digby"
<[email protected]>
wrote in message
hi,
at work, we are currently upgrading from Win200 to XP and from Excel '97 to
Excel 2003. We have hundreds/thousands of spreadsheets that include in the
VBA projects code like:

Application.ExecuteExcel4Macro "Solver.xla..."

This no longer works in Excel 2003. It does not produce an error, but seems
to do nothing. The code is easy to rewrite by adding a reference to
solver.xla and calling the functions as normal in VBA. The problem is that I
would have to replace the code in all the hundreds/thousands of scientific
spreadsheets that have already been created. I need to have a solid credible
reason to tell my boss why I am going to need a lot more time to do my part
of the migration, more than just something like "I think Microsoft have
stopped supporting the old macro language". I need to KNOW. And that was
just a guess anyway. Does anyone know why the above code does not work in
Excel 2003, while it is fine in Excel'97? Also, is there a update/patch
available from Microsoft to fix the problem? Basically, is there any way I
can avoid having to rewrite the code?

Thanks in advance,
Chris

 

[Jon Peltier]

List all of the solver commands you're using, not this abbreviated one.
There may be one or two lines omitted which cause your issues.

- Jon

 

[ChrisDub]

hi jon,

I'm not at work so I cannot give you the exact code (I will cut &
paste it tomorrow) but these are the commands used:

Application.ExecuteExcel4Macro "Solver.xla!solverreset()"
Application.ExecuteExcel4Macro "Solver.xla!solverok(plot!R46C4, 2, 0,
plot!r13c12:r14c12)"
Application.ExecuteExcel4Marco "Solver.xla!solverOptions(....)"
Application.ExecuteExcel4Macro "Solver.xla!solverok(plot!R46C4, 2, 0,
plot!r13c12:r14c12)"
Application.ExecuteExcel4Macro "Solver.xla!solverSolve()"

Other than the parameters for the SolverOptions line that is it except
that it is all in Uppercase in the original. It does nothing. I have
had a debug stop or a msgbox between every line and it makes no
difference, nothing happens. It was originally written in Excel '97 on
Win2000 and I am trying to make it run in Excel 2003 (Office 2003 SP2)
on winXP SP2.

If I change the code to use the VBA form, .e.g.
SolverReset
SolverOK "$D$46", 2, 0, "$L$13L$L$14",
etc
and add the reference and install the addin then it works fine. The
trouble is that does not help with the hundreds of spreadsheets
already created with the XLM-style code in it.

Thanks,
Chris

 

[Dana DeLouis]

SolverOk(plot!R46C4...

Just some thoughts...

This does not apply, but you may find it interesting.
http://support.microsoft.com/kb/213215

Usually, since Excel 97, you have to use A1 notation.
Make sure the worksheet "Plot" is the active sheet also.
If it's a complex model, make sure the workbook name does not have any
spaces in them.

 

[Chris Wilkinson]

Thanks Jim,

I meant that the new code works with a reference. The offending code just
does nothing, whether I put a reference to SOLVER in tools>RErefences.. or
not. Even if I install the addin in Tools>addins it still does work.

 

[Chris Wilkinson]

thanks but that made no difference. anyway here is the original code:
s = Application.LibraryPath & "\solver\solver.XLA"
On Error Resume Next
Workbooks.Open filename:=s
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.RESET()"
Application.ExecuteExcel4Macro
String:="SOLVER.XLA!SOLVER.OK(PLOT!R46C4,2,0,(PLOT!R14C12:R17C12))"
Application.ExecuteExcel4Macro
String:="SOLVER.XLA!SOLVER.OPTIONS(100,100,0.000001,FALSE,FALSE,2,1,1,0.05,TRUE)"
' Application.ExecuteExcel4Macro
String:="SOLVER.XLA!SOLVER.ADD(!R15C12,2,""=R17C12"")"
Application.ExecuteExcel4Macro
String:="SOLVER.XLA!SOLVER.OK(PLOT!R46C4,2,0,(PLOT!R14C12:R17C12))"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.SOLVE()"

(the above lines all fit on a single line each - not needing a _ line
continuation character)

I am mystified. My hunch is that the XLM code has been withdrawn, as
suggested by people on other forums, but I want to know for sure why it does
not work. I know that in Win2000, when it sort of supported both the Win98
and WInNT API calls - you had a choice of which type to use, that some of the
Win98 ones were missing. Instead of getting an error message, again simply
nothing happened - making it very hard to debug. Maybe this is the case here.
In some ways it would be more helpful if the code fell over because the
functions did not exists anymore.

Chris