Minimize and MinimizeNonnegative Spreadsheet Optimization v6

Excel Spreadsheet Function to Optimize Cells (without Using the Excel Solver) Version 6

The Excel Solver is a powerful tool for solving complicated problems.  But what if you want something smaller and more compact like… an Excel spreadsheet function?  Minimize() and MinimizeNonnegative() combine the power of a nonlinear optimizer with the convenience of a spreadsheet function.  Unlike the Excel Solver, these functions automatically respond to changes in the spreadsheet and recalculate like any other formula.  As an added bonus, Minimize() and MinimizeNonnegative() can optimize some things that the Excel Solver cannot.

Download the spreadsheet for Minimize() with examples and full VBA source code.

Updates in Version 6

Version 6 includes several calculation options and a bug fix.  This version is backward-compatible; the usage and syntax are exactly the same.

Calculation Modes

In the VBA code the constant CalculationMode controls how cells are recalculated during optimization.  One way to recalculate is with Application.Calculate(), which causes Excel to recalculate everything that is open.  Another way is to find the target cell’s precedent cells and to recalculate them only. (See Colin Legg’s RAD Excel blog for more details about finding and recalculating precedent cells in Excel.)  In spreadsheets where there are few precedent cells or the dependency trees are very small, the precedents method is faster.  On spreadsheets with complex cell dependencies, the “ByApplication” method is faster (because cataloging cell dependencies might take longer than the calculations themselves; Excel does not give us direct access to the dependency tree, and it takes time to recreate it).  There is yet another calculation method called “ByNaivePrecedents”.  This finds and recalculates precedents using the Precedents property of the target cell.  This method is “naive” because it only works if all of the precedents are on the same worksheet.  If two precedents happen to be on two different worksheets, one of them will not be recalculated!  The default is “ByApplication”, but you can change this if you want to experiment with the other methods.  If you find that Minimize() and MinimizeNonnegative() are running too slow, the solution is to change this constant.

Bug Fixed – Crash!

When Excel’s calculation method is set to manual, Excel tries to recalculate the workbook when you save it.  So if you hit the save button, Excel is trying to save spreadsheet data at the same time as Minimize() is making changes to spreadsheet data.  Excel does not protect against that, but this version of the macro won’t try to change the spreadsheet data during a save operation.  The fix involves adding some lines of code to the Workbook module.  If you import the macro into your own workbooks, make sure to also copy the two events Workbook_AfterSave and Workbook_BeforeSave into the Workbook module.  If you experience crashes, then you need to check that the two events were copied over.