How to use a sheet as a macro?

Consider the following scenario:

You have a sheet A with complex calculations that depend on some parameters P1,P2 and P3 given on a sheet B which gives a result R.

Now I want to make a parameter study, of how R changes with the parameters P1,P2 and P3. Sure I can manually adjust the parameters P1,P2 and P3 and see how R changes. But I want a table with lots of different parameter values and resulting values of R. One way to do this is to reimplement all of the complex calculations done in sheet A to a macro.

Now my question is, if there is a more direct way to do this. I.e. (semi-) automatically convert the calculation in A to a macro which I can call to fill my table.

Hello @student,

+1 for interesting scenario.

“convert the calculation in A to a
macro which I can call to fill my

That would not be neccessary…

Your macro only has to fill in the values for {P1, P2, P3} in Sheet B, then at that time the formulae in Sheet A will do their job, and your macro can then read out the value for R from Sheet A, and store it somewhere else ( for example in the same Sheet where your test input parameter sets {P1,P2,P3} are stored ).

Then repeat for all available input parameter sets {P1,P2,P3}.

If you need any help in writing such a macro, then please upload a sample of your document containing the neccessary information.

With Regards,

I’m not sure but maybe Multiple operations can help.