Build 3.0.0.98 of xlsgen makes it possible to create scenarios in Excel. This works with XLS and XLSX files.


Scenarios in Excel (and now in xlsgen)

Scenarios differ significantly over what-if tables in the sense that what-if tables are designed to test changes in formula values when one or more of its parameter are tested against other values expressed in a table, whereas scenarios input values are cells, and resulting values are not expressed in a table.

(VB code follows)

worksheet.Number(2,3) = 1
worksheet.Number(3,3) = 10
worksheet.Number(4,3) = 100

worksheet.Formula(8,3) = "=SUM(C2:C4)"

' one scenario
Dim s1 As IXlsScenario
Set s1 = worksheet.NewScenario
s1.ScenarioName = "scenario 1"
s1.ScenarioComment = "my scenario!"
s1.ScenarioResultCellsRange = "C8:E8"

' one input value to be tested
Dim sv1 As IXlsScenarioValue
Set sv1 = s1.ScenarioAddValue()
sv1.ScenarioValueRow = 3
sv1.ScenarioValueColumn = 3
sv1.ScenarioValueAsFloat = 20 ' replace 10 with 20 (in the scenario only)