Learning Center

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:

In Excel:


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

 

 

In VBA:


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:

  • Goal optimization: Change the objective function from f(x,k1,…,kn) to g(x,k1,…,kn) = (f(x,k1,…,kn)-TargetValue)^2 and minimize it.
  • Constrained optimization: Change the objective function from f(x,k1,…,kn) subject to g(x,k1,…,kn) <=0 to h(x,k1,…,kn) = f(x,k1,…,kn)+BigNumber*( Min(g(x,k1,…,kn),0)^2) and minimized it.

Running a linear programming

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:

  • Objective goal: Minimize or Maximize
  • Objective function coefficients: c1+ … + cn
  • Decision variables domain: Real, Real and positive, Integer, Integer and positive
  • Left-hand side of constraint equations: Matrix with constraints coefficients
  • Constraints signs: {<, <=, = , >=, >}
  • Right-hand side of constraint equations: b1, … , bm
  • Report details: Indicate if the function will return just the optimal decision variables values or a full detailed report.

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

 

 

 

 

 

 

© 2016-2018 Safe Quantitative Technologies, ltd. All rights reserved.