Skip to main content
A
Azad_Chowdhery8-Gravel
8-Gravel
March 20, 2022
Solved

Mathcad and Excel VBA

  • March 20, 2022
  • 2 replies
  • 8923 views

Is there any way to send excel data into Matchcad using VBA. I have many claculation work on mathcad but I want excel to be use as an input body which send the specific cell data to a variable in mathcad and the mathcad run the calculation. after that vba send the message to print the mathcad worksheet Example is here to understand

 

Step 1 In excel sheet, we have let say three values in three cells variable with values such as a is 350, b is 505 and c is 900 and so on (actually I have more than 100 values)

 

Step 2 Excel vba open mathcad using COM or API (I do not know the code yet and how to open mathcad from excel vba)

 

Step 3 Excel sent a, b, and c value in mathcad.  where many operation are performed such addition, square and many more using these a,b and.

 

Step 4 Excel send signal to print mathcad and then close the mathcad worksheet.

 

Note: Please note the actual work is much more complicated it has atlead 60 pages of calculation in mathcad and more than 100 variables need to be sent from excel to mathcad using VBA. Each time I performed the calcualtion, these 100 values are different every time. Right now I put these values manually that takes time and may have errors

 

Is there any solution available like I mentioned in four steps?

 

Thanks in adance.

 

I am new in mathcad.

Best answer by terryhendicott

Hi,

Step 1 In excel sheet, we have let say three values in three cells variable with values such as a is 350, b is 505 and c is 900 and so on (actually I have more than 100 values)

Put the 100 values in 100 contiguous Excel cells so they can be referred to as one matrix of values to transfer to Prime.

 

Step 2 Excel vba open mathcad using COM or API (I do not know the code yet and how to open mathcad from excel vba)

Step 3 Excel sent a, b, and c value in mathcad.  where many operation are performed such addition, square and many more using these a,b and.

The enclosed spreadsheet has 100 cells with values and the VBA macros and forms shows how to transfer the values either way.

A button on the Excel worksheet shows a form.  The form has a Go button.  Press Go and Excel opens Mathcad transfers 100 values to Prime. Prime multiplies them all by three.  Excel reads the values back and shows them in the form.  Be patient as there is a pause time built in.

 

Step 4 Excel send signal to print mathcad and then close the mathcad worksheet.

Use the VBA commands below to save and close the file.  Don't know if VBA can print the Prime file?

 

WS.Save
MCApp.CloseAll (SaveOption_spSaveChanges)

 

 

2 replies

T
21-Topaz II
terryhendicott21-Topaz IIAnswer
21-Topaz II
March 21, 2022

Hi,

Step 1 In excel sheet, we have let say three values in three cells variable with values such as a is 350, b is 505 and c is 900 and so on (actually I have more than 100 values)

Put the 100 values in 100 contiguous Excel cells so they can be referred to as one matrix of values to transfer to Prime.

 

Step 2 Excel vba open mathcad using COM or API (I do not know the code yet and how to open mathcad from excel vba)

Step 3 Excel sent a, b, and c value in mathcad.  where many operation are performed such addition, square and many more using these a,b and.

The enclosed spreadsheet has 100 cells with values and the VBA macros and forms shows how to transfer the values either way.

A button on the Excel worksheet shows a form.  The form has a Go button.  Press Go and Excel opens Mathcad transfers 100 values to Prime. Prime multiplies them all by three.  Excel reads the values back and shows them in the form.  Be patient as there is a pause time built in.

 

Step 4 Excel send signal to print mathcad and then close the mathcad worksheet.

Use the VBA commands below to save and close the file.  Don't know if VBA can print the Prime file?

 

WS.Save
MCApp.CloseAll (SaveOption_spSaveChanges)

 

 

    A
    Azad_Chowdhery8-GravelAuthor
    8-Gravel
    March 21, 2022

    Hi Terry,

    Thanks for the solution. It really helped me.

     

    I am stuck in matrix. I need to assign these values as real number instead of matrix

     

    For example 

     

    In excel we have:

    A1 cell has 25

    A2 cell has 99

    A3 cell has 2568

     

    So in Mathcad 

    a1=25

    a2=99

    a3=2568

     

    and then few calcuation is performed

     

    Note: I have different units for these values for example kg, N, Nm etc

     

    Unfortunately I am new with mathcad and no idea how can convert matrix value to the real numbers.

     

    The example you have provided it running flawlessly. I really appreciate that you find time and helped me 🙂

     

    Thanking you in advance 

    L
    LucMeekes23-Emerald IV
    23-Emerald IV
    March 21, 2022

    Beware that the default origin for arrays (vectors and matrices) in Mathcad is 0, not 1, so your first array element may be a0 in Mathcad.But you can set the ORIGIN to 1, if you want to refer to the first array elemenet with index 1.

    You say you need 'real' numbers, I guess you mean that you need scalars (as opposed to arrays), to work with you calculations in Mathcad. Well, if your array in Mathcad is called a, and it has three elements, the first element (by default) is a0, and you get it by typing (in mathcad): a[0

    and this a[0 is a scalar. If you want to refer to it otherwise you can assign it to a variable with an appropriate name, e.g.:

    Mass:a[0

    (This will show as:  LucMeekes_0-1647877885716.png  )

    Excel doesn't know about/work with units. Hence there is no way to export united values from Mathcad to Excel, or import values with units. There are (at least) two methods to work around this:

    1. You work with implied units: e.g. a1 is (always assumed to be) in kg, a2 in N and a3 in N*m. Then when you assign a1 to the variable Mass, you apply the correct unit. E.g.:

    Mass:a[1*kg, which shows as LucMeekes_2-1647878425656.png.

     

    2. You add in Excell a second column (B) next to the column with values. And you put in that column the units for each of the values as string values ("kg", "N" and "N*m"). In Mathcad you now must fetch the two columns from excel simultaneously, that will result in a 2 column matrix in mathcad. Now you can examine the unit strings for each of the values and apply the respective units. Note that there is no built in feature in Mathcad to do so, so you have to write your own mathcad program to apply these units, making sure that you account for every possible unit string that you want to use in Excel.

    I suggest you start with option 1.

     

    Success!
    Luc

     

      C
      CP_93338034-Participant
      4-Participant
      April 18, 2022

      I am maintaining an Excel template that can iterate through a Mathcad worksheet using VBA. It's available and documented here: www.radianrad.com/met.

      Terms & ConditionsCookie settingsAccessibility statement