Solver Fails in Excel XP

M

Martin

Hi
I have a little spreadsheet aplication which ran
perfectly in Office 97, but since the move to
Windows/Office XP, I get the message:

Solver: An unexpected internal error occurred, or
available memory was exhausted.

I'm running a P4 2.26GHz with 512MB RAM.

My code is as follows:

Public StartTerm As Integer, StopTerm As Integer
Public StepTerm As Integer
Public ResultsLabelCount As Integer
Public rs As Object
Public ctl As Control

Sub RunSolverCalcs()
Dim ResultsCounter As Integer
Set rs = [RStart]
Range(rs.Address & ":" & rs.Offset(0, 1).End
(xlDown).Address).ClearContents
Sheets("Calculation").Select
For ResultsCounter = StartTerm To StopTerm Step StepTerm
[Term] = ResultsCounter
[SellingTerm] = ResultsCounter
[Interest] = Range("Int")
RunSolverAddin
rs = [Term]
rs.Offset(0, 1) = [Interest]
CreateSolverResult
Set rs = rs.Offset(1, 0)
Next ResultsCounter
Sheets("Titlesheet").Select
End Sub

Sub RunSolverAddin()
Application.Run "Solver.xla!Auto_Open" 'this fixes the
error message
SolverOk SetCell:=, MaxMinVal:=3, ValueOf:="1", _
ByChange:=[Interest] & "," & [SellingTerm]
SolverAdd CellRef:=[MeanTerm], Relation:=2, _
FormulaText:=[Term]

SolverSolve
SolverReset
End Sub


The spreadsheet is set up as follows (range names are i
brackets):

Variables:
Starting interest rate (Int) 5.20%
Dividend growth (G) 1.50%
Selling dividend yield (SDY) 3.24%
Starting dividend yield (NDY) 3.43%
Term Start (TStart) 5
Term Stop (TStop) 45
Term Step (TStep) 5

Calculations:
Term (Term) 5
Interest (Interest) 5.20%
Dividend Growth (DivGrowth) =G
Net Rate (NetRate) =(1+Interest)/(1+DivGrowth)-1 = 3.65%
Selling Dividend Yield =SDY
Starting Yield =NDY
Selling Term (SellingTerm) 5
Mean Term (MeanTerm) =LN(IPLUS1/I)/(LN((1+Interest)/
(1+Interest+0.001)))

I (I) =((1-(1+NetRate)^-SellingTerm)/(LN(1+NetRate))+
(1+NetRate)^-SellingTerm/SDY)*NDY
I+.1% (IPLUS1) =((1-(1+NetRate+0.001)^-SellingTerm)/LN(1+
(NetRate+0.001))+(1+NetRate+0.001)^-SellingTerm/SDY)*NDY

The user is presented with a form where they can enter
the following:
Starting Interest Rate (Int)
Dividend Growth (G)
Selling Dividend Yield (SDY)
Starting Dividend Yield (NDY)
Start (TStart)
Stop (TStop)
Step (TStep)

The strange thing is that if you set up the solver
manually, then it works fine the first time, but then all
subsequent tries are wrong. Is there any known issues
with Solver, or am I missing something?
I've found a fix to get rid of the error, but the answers
are wrong for all but the very first run.
Any help gratefully received
Regards
Martin

p.s. If anyone wants me to mail them the file (to save
setting it all up), then please mail me using th address
give, but change 'nospam' to 'martin'
 
T

Tushar Mehta

p.s. If anyone wants me to mail them the file (to save
setting it all up), then please mail me using th address
give, but change 'nospam' to 'martin'
Click to expand...
I don't know if this means martin is or is not monitoring the
discussion, but it might be worth clarifying before investing any
resources trying to help.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
M

Martin

<quote>
I don't know if this means martin is or is not monitoring
the discussion, but it might be worth clarifying before
investing any resources trying to help.
</quote>

Hi Tushar
What discussion?
Regards
Martin
 
E

Eric Bentzen

For what it is worth try and change all commands like "SolverAdd" and
"SolverOk" to "SolvAdd", "SolvOk" etc.

I recently had a similar problem, when we upgraded at work, and the reason
was that the new Solver addin didn't translate "Solver..." to "Solv...",
which are the real and undocumented function calls. Microsoft must know
about this bug (they claim to have fixed it in the French Excel version),
but it still thrives in (at least) the Danish version.

Regards Eric Bentzen
(remove nospam to reply by mail)

Martin said:
Hi
I have a little spreadsheet aplication which ran
perfectly in Office 97, but since the move to
Windows/Office XP, I get the message:

Solver: An unexpected internal error occurred, or
available memory was exhausted.

I'm running a P4 2.26GHz with 512MB RAM.

My code is as follows:

Public StartTerm As Integer, StopTerm As Integer
Public StepTerm As Integer
Public ResultsLabelCount As Integer
Public rs As Object
Public ctl As Control

Sub RunSolverCalcs()
Dim ResultsCounter As Integer
Set rs = [RStart]
Range(rs.Address & ":" & rs.Offset(0, 1).End
(xlDown).Address).ClearContents
Sheets("Calculation").Select
For ResultsCounter = StartTerm To StopTerm Step StepTerm
[Term] = ResultsCounter
[SellingTerm] = ResultsCounter
[Interest] = Range("Int")
RunSolverAddin
rs = [Term]
rs.Offset(0, 1) = [Interest]
CreateSolverResult
Set rs = rs.Offset(1, 0)
Next ResultsCounter
Sheets("Titlesheet").Select
End Sub

Sub RunSolverAddin()
Application.Run "Solver.xla!Auto_Open" 'this fixes the
error message
SolverOk SetCell:=, MaxMinVal:=3, ValueOf:="1", _
ByChange:=[Interest] & "," & [SellingTerm]
SolverAdd CellRef:=[MeanTerm], Relation:=2, _
FormulaText:=[Term]

SolverSolve
SolverReset
End Sub


The spreadsheet is set up as follows (range names are i
brackets):

Variables:
Starting interest rate (Int) 5.20%
Dividend growth (G) 1.50%
Selling dividend yield (SDY) 3.24%
Starting dividend yield (NDY) 3.43%
Term Start (TStart) 5
Term Stop (TStop) 45
Term Step (TStep) 5

Calculations:
Term (Term) 5
Interest (Interest) 5.20%
Dividend Growth (DivGrowth) =G
Net Rate (NetRate) =(1+Interest)/(1+DivGrowth)-1 = 3.65%
Selling Dividend Yield =SDY
Starting Yield =NDY
Selling Term (SellingTerm) 5
Mean Term (MeanTerm) =LN(IPLUS1/I)/(LN((1+Interest)/
(1+Interest+0.001)))

I (I) =((1-(1+NetRate)^-SellingTerm)/(LN(1+NetRate))+
(1+NetRate)^-SellingTerm/SDY)*NDY
I+.1% (IPLUS1) =((1-(1+NetRate+0.001)^-SellingTerm)/LN(1+
(NetRate+0.001))+(1+NetRate+0.001)^-SellingTerm/SDY)*NDY

The user is presented with a form where they can enter
the following:
Starting Interest Rate (Int)
Dividend Growth (G)
Selling Dividend Yield (SDY)
Starting Dividend Yield (NDY)
Start (TStart)
Stop (TStop)
Step (TStep)

The strange thing is that if you set up the solver
manually, then it works fine the first time, but then all
subsequent tries are wrong. Is there any known issues
with Solver, or am I missing something?
I've found a fix to get rid of the error, but the answers
are wrong for all but the very first run.
Any help gratefully received
Regards
Martin

p.s. If anyone wants me to mail them the file (to save
setting it all up), then please mail me using th address
give, but change 'nospam' to 'martin'
Click to expand...
 
Top