Every function of form f(x,k1,…,kn), where x is a value or a vector and ki is a value or a vector of parameters, can be optimized using SAFE TOOLBOXES® optimization functions. Of course, Excel comes with Microsoft Solver that can be very helpful to find the minimum or maximum of functions. But the drawback of Microsoft Solver is that it does not update the optimization results when the parameters changes, i.e., it does not provide optimization dynamically. Other difficult is to using Microsoft Solver from VBA to optimize other VBA functions since it is only possible to refer to Excel cells to define a Microsoft Solver model in VBA.
SAFE TOOLBOXES® comes with two univariate optimization functions, named “sOptimizationUnivariate_GoldenSection” and “sOptimizationUnivariate_Brent” and one general purpose multivariate optimization function, called “sOptimizationMultivariate_NelderMead”. Those functions return the value or vector x that minimizes (the default option) or that maximizes the desired function. There is also a linear programming function, called “sOptimization_LinearProgramming”, that minimizes or maximizes a linear object function subjected to linear constraints, but due to the peculiarities of its syntax and usage, it will be exemplified in a separate section below.
The SAFE TOOLBOXES® optimization functions can be useful when you need to optimize any Excel function, a SAFE TOOLBOXES® function or a user-defined function (i.e., a VBA function) where the variable to be optimized comes first and it is followed by one or more optional parameters that will act as constants in the optimization problem. Of course, to optimize some existing non-conforming function or some Excel formula you can “wrap” them in a VBA function that follows the required syntax. The following examples show how to minimize a univariate or a multivariate function:
A |
B |
C |
D |
|
1 |
Example univariate 1: |
2 |
=sOptimizationUnivariate_Brent("UnivariateFunctionVBA1") |
|
2 |
Example univariate 2: |
2 |
=sOptimizationUnivariate_Brent ("UnivariateFunctionVBA2",,,,B8) |
|
3 |
Example univariate 3: |
2 |
=UnivariateOptimizationInVBA() |
|
4 |
Example multivariate: |
3 |
=OptimizationMultivariate_NelderMead("MultivariateFunctionVBA",B9:B10,,B11) |
|
5 |
|
9 |
|
|
6 |
|
|
|
|
7 |
Auxiliary parameters: |
|
|
|
8 |
Constant 1: |
2 |
|
|
9 |
Initial guess: |
1 |
|
|
10 |
|
5 |
|
|
11 |
Constant 2: |
3 |
|
|
Function UnivariateFunctionVBA1(x As Double) As Double
UnivariateFunctionVBA1 = x ^ 2 - 4 * x + 2
End Function
Function UnivariateFunctionVBA2(x As Double, constant1 As Double) As Double
UnivariateFunctionVBA2 = x ^ constant1 - 4 * x + 2
End Function
Function UnivariateOptimizationInVBA()
UnivariateOptimizationInVBA = Application.Run("sOptimizationUnivariate_Brent", "UnivariateFunctionVBA1")
End Function
Function MultivariateFunctionVBA(x, constant1) As Double
'Rosenbrock function: global minimum at [a,a^2]
Dim a, p, q As Double
a = constant1
q = x(1, 0) - x(0, 0) * x(0, 0)
p = (a - x(0, 0))
MultivariateFunctionVBA = p * p + 100 * q * q
End Function
Goal optimization or constrained optimization problems can also be solved by changing the objective function to reflect a penalty from deviations of the desired result. Some examples of transformations are presented below:
Linear programming can be done in SAFE directly in a function. Although Excel provides ways of running linear programming problems via Excel Solver, running a linear programming using a function can be very useful in some situations like when you want to keep your spreadsheet updated when the input changes or when you need to run a linear programming totally inside VBA.
A linear programming is an optimization problem where the objective function and the constraints are defined using linear functions, i.e., with equations of the form c1X1+ … + cnXn. Due to this linear form, the representation of the problem does not require an explicit function that receives the decision variables X1,…,Xn as parameters. Passing only the vector of coefficients [C1 C2 … Cn] is sufficient and equivalent to define a linear equation c1X1+ … + cnXn.
The specification of a linear programming in SAFE TOOLBOXES® is done passing the following list of parameters:
The example below shows how a linear programming is executed in SAFE TOOLBOXES®.
A |
B |
C |
D |
E |
F |
|
1 |
Linear Programming example: |
|
|
|
|
|
2 |
|
|
|
|
|
|
3 |
Objective Function |
|
|
|
|
|
4 |
|
|
|
|
|
|
5 |
Objective goal: |
MIN |
|
|
|
|
6 |
Objective function coefficients: |
13 |
1 |
15 |
|
|
7 |
Decision variables domain: |
INTEGER AND POSITIVE |
INTEGER AND POSITIVE |
INTEGER AND POSITIVE |
|
|
8 |
|
|
|
|
|
|
9 |
Constraints |
|
|
|
|
|
10 |
|
|
|
|
|
|
11 |
Constraint 1: |
1 |
0.80732 |
0.974735 |
> |
1 |
12 |
Constraint 2: |
0.618916 |
0.55909 |
0.515888 |
< |
15 |
13 |
|
|
|
|
|
|
14 |
Solution (detailed report) |
|
|
|
|
|
15 |
|
|
|
|
|
|
16 |
Objective function: |
2 |
|
|
|
{=sOptimization_LinearProgramming(B5,B6:D6,B7:D7,B11:D12,E11:E12,F11:F12,FALSE)} |
17 |
Optimal solution: |
0 |
2 |
0 |
|
|
18 |
|
|
|
|
|
|
19 |
Constraints: |
|
|
|
Shadow Price: |
|
20 |
1.61464 |
> |
1 |
|
-0 |
|
21 |
1.11818 |
< |
15 |
|
0 |
|
22 |
|
|
|
|
|
|