Skip to content

Category: Experiment

syExposeVariableCells

Defines cell or range of cells to be exposed to external tools.

Syntax

syExposeVariableCells(RangeToExpose, [RangeToExpose] ...)

The syExposeVariableCells function syntax has the following arguments:

  • RangeToExpose: required. Cell or range of cells to be exposed. Multiple ranges can be given.

Return value is the cell address or range address of the exposed cells in double square brackets, e.g. [[A1, B3]].

Remarks

The function defines the exposed cells or range of cells. When an exposed cell is changed by the user or macro program (e.g. Solver add-in), Spry runs the simulation model, as defined by syClock. This is equivalent to clicking on the Run button on the ribbon.

Exposed cells are useful for optimization with Solver. When searching for the solution, Solver changes variable cells, waits for the spreadsheet to recalculate, observes the objective cell and if the solution is not found, repeats the cycle. Before setting the Solver options, you can expose variable cells in Spry simulation model.

You can use one or more syExposeVariableCells functions in simulation model.

Normally, we expose cells when the model is build and tested, right before we do the optimization. For complex models, the simulation can last for several minutes. Each change of the exposed cell will run one complete simulation. After your optimization experiment is done, it is advisable to delete or comment out the formula with syExposeVariableCell function. To avoid unnecessary use, the function has no corresponding button on the Spry ribbon.

Examples

  • syExposeVariableCells(H2:H3, G1) - When you change content in cells H2, H3 or G1, Spry will run the simulation model, as defined by syClock. The cell with syExposeVariableCells function will return [[H2:H3, G1]].
  • syExposeVariableCells(InputOne, InputTwo) - Same as above, if we use named ranges (InputOne for H2:H3 and InputTwo for G1). In this case, the function returns [[InputOne, InputTwo]].