I am trying to invoke the Excel Solver tool through a VBA interface.
Specifically, I would like to invoke the Solver functionality directly from a cell, in the same way I would use any user-defined function.
Using online help, I implemented the following "proof-of-concept" basic Sub / macro.
Sub mySolverSub()
SolverReset
SolverOk SetCell:="C4", MaxMinVal:=3, ValueOf:=2, ByChange:="C3"
SolverSolve (True)
End Sub
and can invoke this macro using a button or in the immediate window. It works correctly and gives cells C3 and C4 the correct values (assuming cell C4 has a meaningful formula).
However, I have been unable to implement the same concept in a Function.
For example, I tried
Function mySolverF()
SolverReset
SolverOk SetCell:="C4", MaxMinVal:=3, ValueOf:=2, ByChange:="C3"
SolverSolve (True)
mySolverF = Range("C3").Value
End Function
My intended usage would be to type this function in a cell, say in Cell C5, and have Solver then determine the values of cells C3 and C4, and for Cell C5 to have the same value as Cell C3. This would be much more convenient/scalable than having to create and push macro buttons.
However this function mySolverF does not work when used in the spreadsheet. On earlier versions of Excel it gives the error message "Solver: An unexpected internal error occurred, or available memory was exhausted". On later versions of Excel there is no error message but the function does not actually change anything and just returns the current value of Cell C3 - i.e. Solver does not run.
Interestingly the function mySolverF does work correctly when called from a sub, e.g.
Sub mySolverSub2()
res = mySolverF()
MsgBox "mySolverSub2:res=" & res
End Sub
but this is not quite what I want, because it still requires creating + pushing a button instead of being invoked automatically like a proper function.
Any idea what is going wrong?
thanks in advance!
PS: Note that I would specifically like to interface to Solver rather than implement the numerical algorithm itself in VBA.