Is there any possibility to use solver as a function?-CodePudding

I performed some calculations in excel using solver (with constraints) and I want to write a function in VBA that does exactly the same thing. However, I have a problem, with the usage of solver within a VBA. I read that it is possible to use solver, but you have to refer to a certain cell in worksheet, which is not exactly what I want. What I want it would be something like this:

Function find_maximum(x As Double)
arg_to_bound = x   1               %%% Created variable for constraint
fun_to_maximize = -(x - Exp(x))    %%% Function I want to maximize
%%% Pseudo code %%%
find_maximum = solver(maximize = fun_to_maximize, constraint = arg_to_bound <=1)
End Function

Is there any possibility to have it in such a way? That I do everything inside the function?

CodePudding user response：

You can programmatically set up a solver model and solve it inside a function. The setting up of the model will have to be done on the active sheet, with dependencies expressed as Excel formulas.

Public Function FindMaximum(ByVal x As Double) As Variant
    With Worksheets(1)
        .Activate
        .Range("B1").Formula = "=-(A1 - Exp(A1))"
        .Range("A2").Formula = "=A1 1"
        
        SolverReset
        SolverAdd "$A$2", 1, "1"
        SolverOk "$B$1", 1, , "$A$1"
        SolverSolve True
        SolverFinish 1
        
        FindMaximum = .Range("B1").Value
    End With
End Function