cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Creating a FOR loop including recalculate and export?

SOLVED
Highlighted
Newbie

Creating a FOR loop including recalculate and export?

Hi folks!

I'm having a following problem with finding an effective way to export calculation results from Mathcad:

There's two things I would like to do. Firstly, I'd want to create mechanism that calculates the worksheet several times in a row and exports results from file output box to a new file (or new row) in between the calculation rounds to save the results from each round. My calculations include random variables so the results vary between rounds even without changing the start values. Manually this is easy to do by ctrl+F9 (calculate worksheet), and then opening the excel file attached to file output box and copying the results into a new row of another excel table (or save as to save into individual file). Also this would be possible to do with a for loop in Mcad for simple cases. The problem is that my calculations are too complicated to be put in a default for loop, so I guess I have to create my own with VB.

When the for loop works for the whole calculation, the second thing would be to change some variables between rounds to see the effect of variation in results. I'm just a rookie in programming but the idea is so basic I'm optimistic for finding the solution here. Basicly what I need is following:

For i = 1...10:

Variable1 := i

Calculate Worksheet

Export to results.xls row i (or results_i.xls)

I hope you got the point and can help me with this since I don't seem to solve this by myself. One calculation round takes about 5 mins so I would rather run multiple rounds in a row than manually save the results every 5 mins for about 100 rounds.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Creating a FOR loop including recalculate and export?

Maybe the attached helps.

Store all three files in the same directory, and open CalcLoop.mcd

Calculate the worksheet and enjoy.

Success!

Luc

View solution in original post

10 REPLIES 10
Highlighted

Re: Creating a FOR loop including recalculate and export?

Its not really clear to me what you are going for without a concrete example in form of an attached worksheet, but it looks to me that it would be a good idea to create functions which do your calculations as otherwise you can't "loop a worksheet". Then probably its better not to export your intermediate data to excel after every loop but collect the data in a Mathcad matrix and export all the collected data after the loop has finished.

Highlighted

Re: Creating a FOR loop including recalculate and export?

Thanks for your answer Werner! What do you mean by creating functions for the calculations? To program calculations inside VB code? I have about 40 pages complicated calcultions including Monte Carlo simulations with LHS so this is too much of a work if even possible.

The problem with storing the results in matrix is that how can I prevent the replacing of results from previous round? I should be able to restore the running number i somewhere so that Mcad could recognize the round number and store results into correct row of the result matrix. This would be no problem with the normal for loop in Mcad. It's just that I cannot use it with all these calculations. I need to create one that can recalculate whole worksheet i times and remember the running number i between rounds and use it to store results without replacing the previous.

I'll consider posting a worksheet for example. It's just that all I need is a for loop that can handle all the calculations in my worksheet.

Highlighted

Re: Creating a FOR loop including recalculate and export?

ptc-6630090 wrote:

Thanks for your answer Werner! What do you mean by creating functions for the calculations? To program calculations inside VB code? I have about 40 pages complicated calcultions including Monte Carlo simulations with LHS so this is too much of a work if even possible.

I hadn't VB code in mind.But if you want to iterate a caclulation or simply do it multiple times in one sheet, you have to design it beforehand by using Mathcad functions.

Example:

a:=4

b:=9

A:=a*b

x:=A^0.5

x=6

If you want to do this calculation multiple time with different values of a and b and you do not like the idea of simply manually replacing the values for a and b at the top of the sheet, you would have to turn the calculation of x into a function:

A(a,b):=a*b

x(a,b):=A(a,b)^0.5

x(5,6)=6

x(4,16)=8

etc.

But redesigning a 40 page calculation sheet can be quite cumbersome, to say the least.

On the other side Mathcad does not offer a way to turn a worksheet into a function and loop through it multiple times. The sheet has to be designed differently as shown if you intend to call the calculation multiple times.

I have no experience with it as I don't use Excel for technical calcs but you may use the Mathcad plugin for Excel and call the Mathcad Sheet from within Excel. Not sure but I guess that the looping you are after could be done that way - Letting Excel control the loop and storage of results.

The problem with storing the results in matrix is that how can I prevent the replacing of results from previous round?

No problem at all in Mathcad. You can save the results in different rows or different columns, whatever seems appropriate. You may also create a vector whose elements are the matrices you get for each run. It depends on how you would like to use the results later and what kind of matrix the result of a single run is (scalar, vector, matrix).

Highlighted

Re: Creating a FOR loop including recalculate and export?

Werner Exinger wrote:

I have no experience with it as I don't use Excel for technical calcs but you may use the Mathcad plugin for Excel and call the Mathcad Sheet from within Excel. Not sure but I guess that the looping you are after could be done that way - Letting Excel control the loop and storage of results.

Thanks again. I'm starting to think that calling my calc sheet from excel could be a way to do it. Since I've never done that before, I wonder could I call my calc sheet from another Mcad sheet. I guess that would be more simple. Never done that either but heard some rumors that it's possible. Am I right? The other sheet would run the calc sheet and save the needed results from it.

The problem with storing the results in matrix is that how can I prevent the replacing of results from previous round?

No problem at all in Mathcad. You can save the results in different rows or different columns, whatever seems appropriate. You may also create a vector whose elements are the matrices you get for each run. It depends on how you would like to use the results later and what kind of matrix the result of a single run is (scalar, vector, matrix).

The results are in a form of vector (row of matrix, which is attached to an excel file output). The problem is that if I'm running the whole calculation again, how does Mathcad know, it's not the first calculation round. By recalculating worksheet all the variables are defined again like in the first place, right? I would need one variable (running number i) to store the calc round number to point the correct row in result matrix (in this case the results would be in form of matrix instead of just one row).

But anyway this should not be a problem if I'm using another program for looping and storing. So what might be the simplest way: another Mcad sheet, Excel table, or VB script?

Highlighted

Re: Creating a FOR loop including recalculate and export?

You can call your sheet from Excel, another Mathcad sheet, or a vbscript. If you call it from another Mathcad sheet the way you would do it is using vbscript in a scripted component. If you call it from Excel you would do it using a VBA macro. So there's not really any difference in difficulty. If you have enough programing knowledge to do this then they are all about equal.

Highlighted

Re: Creating a FOR loop including recalculate and export?

Maybe the attached helps.

Store all three files in the same directory, and open CalcLoop.mcd

Calculate the worksheet and enjoy.

Success!

Luc

View solution in original post

Highlighted

Re: Creating a FOR loop including recalculate and export?

Thank you so much! This seems like an appropriate solution. I'll have to wait till tomorrow to test my sheet. It would be perfect if I could change some variables in the calc sheet between loops, but at this point I'm happy to just save the calc sheets with different values into individual files and use different reference in each loop.

Highlighted

Re: Creating a FOR loop including recalculate and export?

The calculation, as it is now setup, defines the variables a and b in the sub-sheet (calcalc).

There's nothing against defining (and redefining!) them in the top sheet (calcloop) instead.

This will provide you with control over their values; basically this approach is shown in the second part of calcloop where, with every iteration, first a and b are calculated (using functions defined in calcfunc).

Note that you can read the results matrix back in with MathCad, but almost just as easily with ExCell.

Success!
Luc

Highlighted

Re: Creating a FOR loop including recalculate and export?

Wow, works beautifully! Thanks again for everyone, this helped me a lot!

Announcements