CODEWITHSUNDEEP
vbaexcelsolver
Nicola Torrisi
Nicola Torrisi

Reputation: 19

Excel Solver ends before finding a solution - VBA

I'm a starter user of VBA and I'm having some problem with Excel Solver.

Basically I have to calculate the Fair Values of several financial instruments, grouped by the country of origins.

For each country the solver will minimize a function in order to find the model parameters.

So far I've been able to make it work and among 50 different countries/category the solver works in 49 cases.

Here comes my problem, when it comes to this country the solvers steps out after 3 or 4 iterations. leaving the objective funciont far from been minimized. But when I click on a commandbutton linked exactly to the same subroutine it finds a solution.

Here is part of my code, NSCoeff is the sub linked to the solver.

Sub NelsonSiegel()
Dim a, b, c, d, e, f, g, p, s, I, t, v, pv, TIR As Variant
Dim NumBonds, bnd_cnt As Integer
Dim current_wb As String
Dim spot(), df, dfcf, NumberCashFlows, Lambda, Lambda2, Beta1, Beta2, Beta3, Beta4, TimeToCashFlow(), NSPV As Variant
Dim j As Integer
Dim Time() As Variant
Dim A1() As Variant
Dim A2() As Variant
Dim A3() As Variant
Dim A4() As Variant
current_wb = ThisWorkbook.Name
NumBonds = Sheets("bonds").Cells(1, 7).Value

Workbooks(current_wb).Sheets("Nelson_Siegel").Range("n4:s4").Value = 1
NSCoeff
Workbooks(current_wb).Sheets("bonds").Calculate 'download coupon date for NS procedure

Lambda = Worksheets("model").Cells(28, 2)
Beta1 = Worksheets("model").Cells(29, 2)
Beta2 = Worksheets("model").Cells(30, 2)
Beta3 = Worksheets("model").Cells(31, 2)
Beta4 = Worksheets("model").Cells(32, 2)
Lambda2 = Worksheets("model").Cells(33, 2)

and here the NSCoeff sub

Sub NSCoeff()
Dim current_wb As String
current_wb = ThisWorkbook.Name


Workbooks(current_wb).Sheets("Nelson_Siegel").Activate

    SolverOk SetCell:="$N$9", MaxMinVal:=2, ValueOf:=0, ByChange:="$N$4:$S$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$N$4", Relation:=3, FormulaText:="0.001"
    SolverAdd CellRef:="$S$4", Relation:=3, FormulaText:="0.001"
    SolverOk SetCell:="$N$9", MaxMinVal:=2, ValueOf:=0, ByChange:="$N$4:$S$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$N$9", MaxMinVal:=2, ValueOf:=0, ByChange:="$N$4:$S$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve userFinish:=True

Any Idea on how to solve this problem? The solver doesn't find a solution when is launched from another subroutine but when it's lauched from a commandbutton it works.

Thanks for the help

Upvotes: 0

Views: 376

Answers (1)

Degustaf
Degustaf

Reputation: 2670

A first step would be to catch the return value from SolverSolve. This would allow you to determine why it ended. 

Sub NSCoeff()
    Dim current_wb As Workbook
    Dim ret As Integer

    Set current_wb = ThisWorkbook

    current_wb.Sheets("Nelson_Siegel").Activate

    SolverOk SetCell:="$N$9", MaxMinVal:=2, ValueOf:=0, ByChange:="$N$4:$S$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$N$4", Relation:=3, FormulaText:="0.001"
    SolverAdd CellRef:="$S$4", Relation:=3, FormulaText:="0.001"
    ret = SolverSolve userFinish:=True
    MsgBox "SolverSolve returned a value of " & ret
End Sub

Then you can consult Microsoft's Website to determine why the solver decided to exit.

From there, you can use that information to determine how to proceed to.

Upvotes: 1

Related Questions