Creating what-if's in VBA

Question

Data tables in Excel are really nice to see how numbers would evolve if one of your input changes. However:

they're (very) slow
the variable you edit is not dynamic; i.e. changing the row or column input cell to =INDIRECT(<dynamic address>) works but doesn't update if the dynamic adress changes.
they force you to use an annoying structure
the variable needs to be in the same sheet as the table

Because I want to do a lot of them, point 1 and 2 make it very impractical to work with data tables. Is there any way to make a function that does this in VBA? I tried a UDF that changes the variable's value (e.g. =whatif(<hypothetical output>, <variable cell>, <variable hypothetical value>), but that's not allowed in VBA (see How to prevent VBA function from re-executing inside the code).

Gary's Student · Accepted Answer · 2013-11-07 00:22:23Z

1

I am quite sure you can achieve your goals using subs rather than UDFs. The concept is to, basically, treat the worksheet like one huge function:

the sub sets up a group of input cells
the sub allows the worksheet to calculate
the sub monitors the output cell(s)

If the goal is some kind of non-linear analysis (like sensitivity analysis), the sub compares successive runs. If the goal is some kind of optimization, the sub drives the inputs to achieve a result. If the goal is some kind of statistical analysis or modelling, the sub can drive a Monti Carlo.

answered Nov 7, 2013 at 0:22

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tb189 Over a year ago

I ended up doing it with subs indeed, and copy pasting values the output

Collectives™ on Stack Overflow

Creating what-if's in VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related