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!Autpen" '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'
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!Autpen" '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'