We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to use a sheet as a macro? [closed]

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

student gravatar image

updated 2020-07-11 20:51:45 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-02-14 06:09:11.564985

2 Answers

Sort by » oldest newest most voted

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

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

Question Tools

1 follower


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

Seen: 48 times

Last updated: Jan 20 '18