Skip to main content
E
Eivind1-Visitor
1-Visitor
October 6, 2010
Solved

How to run a worksheet multiple times while alternating a parameter

  • October 6, 2010
  • 21 replies
  • 13234 views

Since I am new to MathCad, I am hoping that some skilled experts could enlighten me with a solution to this small (hopefully) problem!

My project(master thesis) is a massflow diagram for a theoretical bioenergy plant. The calculations (about 10 pages), starts with some pre-defined constants, goes through the calculations and calculates different result parameters (flows in/out, energy produced, energy consumed, energy exported ...).

In order to analyse the relationship between the pre-defined constants and the results, I want to run the worksheet multiple times (100) while altenating a chosen parameter. Doing this manually is not an optimal solution.

I am looking for a script which execute these steps:

1. Starting with a list of the different alternatives for a parameter.

2. Run my worksheet (AA.xmcd) once for every alternative.

3. Return the results for each run and store them (together with the alternating parameter) for further data analysis.

Is there a simple way I could automate this procedure? Within the same worksheet, or in a linked worksheet? I have read through the different discussions on the topic, but have not yet manage to utilize the answers... Alternative procedures solving the same problem is also appreciated .

Thank you in advance!

Eivind

NB: Running on MathCad 14.0

Best answer by

Hi Eivind

I've attached a reworked version that you can look at to see if it helps you.

The Master sheet now opens the working sheet where your main program will go.

The master sheet set up the parameters to control the looping of the working sheet & consolidates the results from each run.

Once the data is all collected in the master sheet you can then post process & display it in any way you like.

Once the sheet is running suggest tiling vertically to see both sheets side by side.

To run again, select the Master Sheet and press <ctrl> <F9>

On my laptop at home I encountered a memory leak which means that I receive a "null pointer error" at varying point during the looping of the work.mcd file.

It is more apparent at larger numbers of iterations (>80) but re-running the master worksheet will give the error happening at earlier stages of the loop time after time (first run 79 iterations, second 40, third 29 etc...)

However on my work computer it doesn't seem to happen.

Hope this doesn't mean that I've caught a virus from any of the dodgy websites I trawl (BBC, GOOGLE, MSN etc)

It doesn't make too much sense, but the functions used aren't documented in the help files & won't be in very common use, so could still have untrapped errors.

regards

Andy

21 replies

R
Raiko17-Peridot
17-Peridot
October 6, 2010

Hello Eivind,

I suggest to store the parameters in separate Excel files and to read them in with the readfile command which itself is executed within a loop.

Have a look at the attached image where some code is depicted. Maybe it is of use for you

Cheers

Raiko

M
MikeArmstrong1-Visitor
1-Visitor
October 6, 2010

Raiko Milanovic wrote:

Hello Eivind,

I suggest to store the parameters in separate Excel files and to read them in with the readfile command which itself is executed within a loop.

Have a look at the attached image where some code is depicted. Maybe it is of use for you

Cheers

Raiko

Raiko,

That would mean creating 100 Excel files wouldn't it?

He could use the attached "WRITEEXCEL" function which will write data to a specific worksheet in a single Excel file

Mike

R
Raiko17-Peridot
17-Peridot
October 6, 2010

Mike,

yes you're right. That would entail creating 100 Excel files if you have 100 Parameters.

However, if it is only one parameter that is to change than it might be easier to have an array of 100 elements from which you succesively read in the parameter value. This would be much faster than reading from an external file.

Raiko

F
f.kohlhepp1-Visitor
1-Visitor
October 6, 2010

You haven't shown your sheet, so we can only guess about how you set it up; but if you're totally new you may not realize the power of making functions instead of single-valued computations. The attached sheet is a simple demonstration of a number of ways to calculate the same thing, adding a bit more variability as you change the way you define a function. It sounds to me like you need to rework your sheet into a set of functions of the variables you want to change, then feed it a vector of the variables.

Good Luck!

A
Anonymous
October 6, 2010

Hello Elvind,

there was a topic that may help from the old Collabratory , However be aware that it is prone to crashing Mathcad if variables that are being "read" do not exist in the target worksheet; the error trapping is minimal.

That said here is the post:

Topic: VBScript - creating & control another mathcad (4 of 5), Read 208 times, 2 File Attachments
Conf: Authoring
From: rijackson richard.
Date: Saturday, July 28, 2007 07:44 AM

Sorry, my mistake. I copied the wrong URL. It's the next post in that thread, from Stuart.

http://collab.mathsoft.com/read?72543,13

There are also the attached files from Xavier. I can't locate the original thread.

Richard

I have attached the files that I have & seem to work in version 12, other than this version I can't help I'm afraid.

/upload/Controller_v2.1(6).mcdHope that this is helpfull

Regards

Andy

E
Eivind1-VisitorAuthor
1-Visitor
October 7, 2010

Hi Andy, and thank you for your time and contribution!

I have tried to set up the mastersheet and link it with my sheets with the calculations. I have managed to remotely change the parameter, but the results are not displayed and I only get the error message: "Error HRESULT E_FAIL has been returned from a call to a COM component".

Any suggestions? I have attached the masterSheet, my calculations are so messy that they would only confuse

Eivind,

M
MikeArmstrong1-Visitor
1-Visitor
October 7, 2010

Eivind,

Have a look at Richards thread below.

He suggests writing everything as a function of the parameter you wish to change and then create a loop. This is the easiest way to achieve what you are trying to do. If your data or worksheet won't allow this then another method could be adopted, but without the data we'll never know.

If you don't want to paste the actual data/calc for any reason, just replicate the problem and post that worksheet.

Mike

R
RichardJ19-Tanzanite
19-Tanzanite
October 6, 2010

This has come up many times. As already pointed out, the right way to do it is to write everything as a function of the parameter you wish to vary. Then at the end of the worksheet you just have a loop that calls the function multiple times. There are other ways to do it, but they are all kludges and not necessarily trvial to implment either.

Seeing the worksheet would help.

E
Eivind1-VisitorAuthor
1-Visitor
October 7, 2010

As you can see in the attached worksheet, there are many calculations ending up in many different parameters. It is not easy to combine all these calculations in one loop (Maybe it is?). Maybe you have other suggestions to how the worksheet 'actual' should have been build? Are there some tricks or way of building the model which would have simplified the model. The reason for defining all the parameters is that this quantifies all the stages in the process, making it easier to check, controll and monitor the calcultations.

I think that the best way to be able to simulate the concept for different values for ("A.L") is to define the values in a remote worksheet with a loop function.

Any suggestions are welcome!

Eivind

M
MikeArmstrong1-Visitor
1-Visitor
October 8, 2010

The reason for defining all the parameters is that this quantifies all the stages in the process, making it easier to check, controll and monitor the calcultations.

Without being too critical, if somebody gave me that calculation to check in our company I wouldn't be best please. The input variables are scattered everywhere and it’s hard to read / follow. Mathcad has the ability to produce calculations as they are found in textbooks, documents...etc.

Regarding your problem - You haven't stated which variables / parameter you want change in order to run the calculation numerous times.

Mike

A
AlvaroDíaz12-Amethyst
12-Amethyst
October 7, 2010

Please see http://communities.ptc.com/message/148006

Using FRAME is a good solution for me, even maybe not the more elegant.

Regards. Alvaro.

L
LucianBotez3-Newcomer
3-Newcomer
October 7, 2010

Hi,

You can try to do this by embedding your calculation into Excel or a VB application. It is a elegant solution as you can take advantage of Excel's nice charts. There are a couple of good examples in the MathCAD help.

I'm using version 11, and a good Excel-MathCAD integration example is located under: "C:\Program Files\Mathsoft\Mathcad 11 Enterprise Edition\qsheet\Samples\Excel". I think you can easily figure out where it is on your computer.

Regards,

Lucian

Terms & ConditionsAccessibility statement