Using Scripts in MathCAD (VB Script?) for foundation design calculation

[Debayan]

A Big Hello to all fellow Civil & Structural Engineers!

I had thought that I should put this question in 'Programming & Extending MathCAD' discussion, but then I thought against it and decided to put this question here.

I thought I will just explain it the way it is in a language that fellow engineers will understand.

Bascially what I have is this

1) STAAD output of support reactions in MS excel worksheet.

2) MathCAD 15.0

Now what I want to do is

1) To script a code (please let me know whether I can do this without a code too) and run a loop so that it automatically pastes each and every node and load case number one by one in a particular variable by reading the following from the excel sheet.

2) As and when the node no and LC is getting pasted, the variables which I have assigned for forces like FX,FY and FZ should change automatically (The values of FX, FY and FZ will be read from the excel sheet)

3) Assuming that I have just one check , whose output is 'Safe' and 'Unsafe' should be tabulated in an array form for each and every load case and node no

4) I should be able to display the most critical LC and node no combination for documentation purposes.

I hope that I have not jumbled up all of this. Any help will be appreciated. I have done this in VB Macro in excel and am just shifting to MathCAD thus this long question.

Regards,

Debayan

[MikeArmstrong]

Welcome to the forum.

Can you provide the Excel sheet and maybe the Mathcad sheet, to see how far you have got. You could also provide examples of the results you expect to obtain for clarity.

Mike

[Debayan]

Hey Mike!

Because of client confidentiality and all that, I had to make a mathcad sheet myself!

Please find attached excel sheet and MathCAD sheet. Please paste both on desktop.

In the excel sheet consider leftmost column as node numbers which I want to be pasted one by one to execute the condition "Safe/Unsafe" below.

The Mathcad sheet should have a variable for nodes so that when the node number pastes in that variable the values of x2,x3,x4 and x5 change automatically and pick the values which are to the right of the pasted node number.

Now in my sheet there are 17 nodes so in the end of the operation it should display a table which has two columns (one for node no and the other for result) which shows 1-safe,2-safe,3-unsafe...........and so on till 17th node.

I think it is similar to the READ-EVAL-PRINT LOOP.

Hope this helps.

[MikeArmstrong]

I think I'm sure I know what your after. It seems that you have extracted the values from the data incorrectly.

Are the values to be summated for each node found on the right hand side of the corresponding node number? (Columns 1 - 6)

Mike

[MikeArmstrong]

Something like the attached?

Mike

[Debayan]

I am really thankful to you, as you have fairly got the correct idea as to what I want.

As for your first question, yes the data has been extracted incorrectly. I want values to the right of the node to be x2, x3 ....so on and so forth, I guess I can manage that.

But there is also one additional thing, the node number should be user editable, means if in the summary sheet if I see unsafe then I should be able to enter that node number somewhere in the sheet so that the I can review the actual calculation and see where it has failed.

Also the whole programming looks a bit greek to me , I know it is asking for much but could you please explain what your program does...coz If someone asks me, how this sheet functions then I will have to reply "Go and ask Mike!"

If that is a bit too much then can you please explain at least the following two things.

1) Which lines defines the start and end of the loop?

2) Which line asks MathCAD to make a table (it looks as if it has been exported)

Apart from this there are other silly doubts as below.

1) Why can't I view the table in totality?

2) How do I make MathCAD recognize the Book1.xls file to read the contents if it is not located on desktop?

Thanks a ton!

Debayan

[MikeArmstrong · Accepted solution]

But there is also one additional thing, the node number should be user editable, means if in the summary sheet if I see unsafe then I should be able to enter that node number somewhere in the sheet so that the I can review the actual calculation and see where it has failed.

Will the attached help?

I have added an extract function which loops through all elements of Evaluation and checks for the condition 'Unsafe' and then returns the original input data, in order for it to be checked. Is this what you had in mind?

Also the whole programming looks a bit greek to me , I know it is asking for much but could you please explain what your program does...coz If someone asks me, how this sheet functions then I will have to reply "Go and ask Mike!"

I will write some notes and re-post a bit later if that's ok?

1) Why can't I view the table in totality?

2) How do I make MathCAD recognize the Book1.xls file to read the contents if it is not located on desktop?

If you double left click and set the 'Matrix display options' under the display tab to Matrix you should be able to see it.

To change the path of the component, right click and select properties and browse for your file.

Hope this helps.

Mike

[MikeArmstrong]

Notes added to the worksheet. Hope you can understand it.

Mike

[Debayan]

Mike you're my man! You hit the nail on the head. Now I know how to do this.

Just a wee bit of problem here.

I have noticed that you have programmed the whole equation into your loop for it to return results (The 4th point of your explanation). This was just an example, so I used simple terms like addition. The actual sheet in which I will use this program is very complicated and it will not be practical to define the whole thing in a loop.

I have updated the excel file you sent and attached it here so that you can see the exact condition that I need.

I have added one line that has Node, Col1 (which will have the value which is to the right of the mentioned node), Col2, Col3, Col4 and Col5

Now the loop should paste the nodes in the Variable 'Node' only, on doing so the values in Col1 to Col5 will get automatically updated from the excel sheet. This in turn will be linked to the x1 to x5 values below which will perform the calculation which I can see. The summary table should just literally pick the result from the 'Check' variable and 'Stack' it one below the other.

Thanks for the help Mike, you are a godsend.

Regards,

Debayan

[MikeArmstrong]

I have noticed that you have programmed the whole equation into your loop for it to return results (The 4th point of your explanation). This was just an example, so I used simple terms like addition. The actual sheet in which I will use this program is very complicated and it will not be practical to define the whole thing in a loop.

I have updated the excel file you sent and attached it here so that you can see the exact condition that I need.

I have added one line that has Node, Col1 (which will have the value which is to the right of the mentioned node), Col2, Col3, Col4 and Col5

Now the loop should paste the nodes in the Variable 'Node' only, on doing so the values in Col1 to Col5 will get automatically updated from the excel sheet. This in turn will be linked to the x1 to x5 values below which will perform the calculation which I can see. The summary table should just literally pick the result from the 'Check' variable and 'Stack' it one below the other.

I'm not entirely sure what your after.

So you want to check each node separately?

Mike

[Debayan]

Dear Mike,

Sorry, It got a bit confusing. I made another mathCad sheet. Please refere my comments in red text.

Thanks & Regards,

Debayan

[MikeArmstrong]

Sorry, It got a bit confusing. I made another mathCad sheet. Please refere my comments in red text.

I think I'm getting confused.

Do you want Mathcad to automatically loop through all your Load cases? We seem to have moved away from the previous functions I posted.

Mike

[Debayan]

Hi Mike,

Sorry for the delay in continuing the post, I was on vacation.

See the thing is, if you just have the input excel sheet and your program, the evaluation sheet is generated, there is no need of writing x1, x2, sum and all that.

What I want is that instead of the actual process being programmed in the code, it should pick up the values from the variables defined in the mathcad sheet and then compare it as safe or unsafe.

Regards,

[MikeArmstrong]

Have you managed to get a final solution to your query?

Mike

[Debayan]

Hey Mike,

Sorry for no reply in a long time.

No. I am still working on it.

[BongTallorin]

Please try this one..

[Debayan]

Hi Lorenzo,

Your method is deceptively simple and it might actually work.

Please just guide me as to how to collate the two seperate tables into one table. Let me see if I can take it from there.

If not then I will re-post!

Thanks

[BongTallorin]

Something like attached?

[Debayan]

Lorenzo/Mike

The situation is that I have almost 800 to 900 rows of data to be processed and judged accordingly, but for documenation purposes I have to show the most critical one of them.

PLEASE REFER ATTACHED FILE FROM NOW ONWARDS.

Now in the shee that Lorenzo gave, the problem is that although it does seem to give the summary correctly but I cannot display the individual values of Fx, Fy, Fz and so on. This happens even when I change the input to a single number instead of defining the range in LC input.

Now in the sheet that I have uploaded, I tried a workaround for that, but encountered the problem which I shall explain below.

1) I changed the input in LC variable and just put a integer value, so that the Fx, Fy, Fz value defined again with the vlookup command works.

2) The condition to be checked is placed below the newly defined Fx, Fy, Fz values.

Then the error as shown in the attached sheet happens.

It is saying that the value of Fx(LC-1) must be a vector. Why is that so? Because just to check, I randomly added the line Fx + Fy and voila! it gives a result.

So the values extracted through vlookup command are definitely not scalar.

Then why there is still an error?

Any help is appreciated.

[wayne]

Debayan,

You don't need scripts or vlookup. This is a straight forward problem, unless I am missing something. You already have the matrices parsed, so I don't know why you are trying to use vlookup.

See attached.

If I am missing something, please let me know,

Wayne