Use of solver to minimise an vba function (instead of an excel cell wrt other excel cell)

Question

I want to minimise the function myFunction by changing the values of alpha. In this dummy example, I expect alpha = X (= 3).

To do so, I want to use the Excel Solver, and avoid copying part of the code in the sheet. This code is part of a Least Sqare Interpolator.

Thus I wonder how to write correctly the Minimizer function - Solver part. (the rest being correct).

Option Explicit

Private alpha As Double

Function myFunction(X)

    'myFunction , the variable is alpha
    myFunction = ( alpha - X ) ^2

End Function


Public Sub Minimizer()

    Dim X As Double
    X = 3
    Solver (change alpha with the value that minimize myFunction(X))

End Sub

Answer 1

Unfortunately, what you want to do isn't possible. The solver is designed for use in a worksheet.

If you decide that you are willing to place data on your worksheet, then you can set the solver options with SolverOptions, Set the target cell and cells to change with SolverOk, add constraints with SolverAdd, and solve with SolverSolve. More details are on Microsoft's Website.