Corresponding author: Prasanth Sambaraju (

Academic editor: Editorial Secretary

Solver is a Microsoft Excel add-in program which is used to find an optimal value for a formula in the objective cell. Solver accomplishes this either by maximizing, minimizing or setting the objective cell value to a specific value. The article presents the utility of in built worksheet events in Excel VBA to save the value of objective cell from each iteration when solver is used for optimization.

Solver has been used in numerous and diverse applications (

Biochemical oxygen demand (BOD) is used as measure of the pollution produced by domestic and industrial wastes. The data shown in Table

Y is the exponential model describing BOD

b_{2} is the rate constant (parameter 1)

b_{1} is the BOD value (parameter 2)

x is incubation time in days

a) Data is entered in Excel, incubation days (x) in column A and BOD values (y) in column B.

b) Initial values for b_{1} and b_{2} are set to 100 and 0.75 in cells F2 and F3.

c) Y calculated was obtained by substituting b_{1}, b_{2} and corresponding x values in equation (1).

d) Sum of squared errors (SSE) was obtained by squaring the difference between Yobs and Ycalc.

e) Open Solver (In Excel 2013 it is located Data tab). The dialogue box is shown in Fig.

f) Solver also option of showing the iteration results (Refer to Fig.

g) When the show iteration results is selected the following message is displayed, it prompts the user either to continue, stop or save scenario (Refer Fig.

h) The user has to manually select an option to show results of each iteration. This would considerably increase the time for reaching an optimal solution.

i) The results obtained for parameters b_{1}, b_{2} and SSE after solver implementation matched with the reported values (

The worksheet change event macro (as shown in Fig. _{1}), K (b_{2}) and L (SSE) respectively. The values of b_{1}, b_{2} (top) and SSE (bottom) for each iteration during the solver optimization are plotted in Fig.

This article demonstrates the use of Excel worksheet events to obtain the objective cell values after each iteration. The number of objective cell values obtained is dependent on the constraint precision value.

Solver implementation: Objective cell in F4 (minimize sum of squared errors) by changing values of b_{1} and b_{2} in cells F2 and F3 respectively.

Solver options tab.

Solver message box showing iteration result message.

Excel Worksheet Change event macro to copy result from each solver iteration.

Plot showing the values of b_{1}, b_{2} (top) and SSE (bottom) for each iteration during the solver optimization.

BOD Data.

Incubation x (days) | BOD (mg/L) y |

1 | 109 |

2 | 149 |

3 | 149 |

5 | 191 |

7 | 213 |

10 | 224 |

BOD data

Excel macro enabled file

BOD data and macro to save each objective cell value during solver implementation.

File: oo_610765.xlsm