Ask Your Question
1

How to use a sheet as a macro?

asked 2018-01-20 11:41:08 +0200

student gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-01-20 13:18:48 +0200

librebel gravatar image

Hello @student,

+1 for interesting scenario.

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

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, lib

edit flag offensive delete link more
0

answered 2018-01-20 14:12:51 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-20 11:41:08 +0200

Seen: 39 times

Last updated: Jan 20 '18