Using Solver from VBA Excel 2010

[secret2]

I have to maintain both an Excel 2003 and an Excel 2010 version of the same code, but the part that calls the solver seems to be not working in 2010 version. In the 2003 version I have:

' Fill price formula temporarily
Worksheets("InstrumentsList").Range("A1").Offset(rowNum - 1, 43) = "= funcPrice(AS" & (rowNum) & ")"
' Solver
SolverReset
SolverOk SetCell:="AR" & rowNum, MaxMinVal:=3, ValueOf:=mktPrice, ByChange:="AS" & rowNum
Dim solverResults As Integer
solverResults = SolverSolve(UserFinish:=True, ShowRef:="SolverIteration")

where funcPrice() is a function that takes one argument. In Excel 2003 this works perfectly. However in 2010 the solver fails, and as I can see on the status bar close to the bottom of the screen the objective cell is always zero, hence the failure.

Has anyone encountered similar issue? Is the syntax for calling the solver different in Excel 2010? Thanks!

[tlafferty]

Not sure about the syntax, but if the solver add-in is not loaded, it will fail regardless of syntax...

[secret2]

Do realize that but that is not the reason, I made sure that Solver is added both on the workbook and on the VBA reference...

[shg]

Does it work if you run Solver from the user interface?

[secret2]

I tried setting up a problem (though different from the VBA computation I'm trying to do) and it does work

[shg]

There's a lot of code missing from what you posted, and your code assumes that InstrumentsList is the active sheet.

This worked for me.

Sub x()
    Dim iResult     As Integer
    Dim iRow        As Long
    Dim mktprice    As Double

    mktprice = 10
    iRow = 1

    With Worksheets("InstrumentsList").Cells(iRow, "AR")
        .FormulaR1C1 = "= funcPrice(rc[-1])"
        SolverReset
        SolverOk SetCell:=.Address, _
                 MaxMinVal:=3, _
                 ValueOf:=mktprice, _
                 ByChange:=.Offset(, -1).Address
    End With
    iResult = SolverSolve(UserFinish:=True, ShowRef:="SolverIteration")
End Sub

Function funcPrice(d As Double)
    funcPrice = d - 1
End Function

[secret2]

Thanks shg. The same problem persists though, i.e. status bar telling me that the objective is always zero...will try again.

[shg]

Maybe the problem is in funcPrice.

[secret2]

Mmm.....unlikely too, I call and display the function output after using the solver and those are verified. Plus, it has had no problem in the 2003 version. Very weird.

[shg]

No problem for me in 2010.

[secret2]

I placed a break point within funcPrice. Seems to me that, while the "byChange" cell has to be negative to produce the desired value, solver is only willing to plug positive values.

[shg]

Solver is smarter than that, but the information you've provided is insufficient to even make a guess.

Good luck.