Automation Example: Excel Table of Measurements |
For any Windows Application that supports scripting, Mathcad's Automation interface can be used to pass data from the application into Mathcad for further calculation, then return the data to the application. For example, Microsoft Excel is one application that can be used for storing tabular measured information as well as the repeated results of a Mathcad calculation on the measurements.
In the Thermocouple.xls spreadsheet in the qsheet/samples/excel folder, you can enter a new voltage, select the cell containing the voltage, then press [Ctrl] [Shift] T (for temperature) to calculate a new temperature measurement in Mathcad. The temperature and the date the calculation was performed are recorded in Excel.
In Excel, create columns or rows that will hold your data. Much of Excel's scripting relys on knowing the row and column references for a cell, so you'll want a stable cell setup before you begin scripting. Then, to create a script, choose Macro > Macros from the Tools menu to name a new macro script and edit it. The script in the sample file is shown below.
As with any application that supports scripting, you'll have to familiarize yourself with the classes available within that application. Excel includes a large subset of the VB commands, along with many classes unique to Excel to specify selected cells, OLEObjects, Worksheets, and so on. Pressing F1 when on any script command in the script editor will bring up the documentation.
In this example, the macro to recalculate a cell is called from the key sequence [Ctrl] [Shift] T. Choose Macro > Macros from the Tools menu to bring up the macro name box, then choose Options to assign a shortcut key.
There are some important techniques you should use when coding an Excel macro to control Mathcad:
In this example, the Mathcad worksheet is embedded in the Excel sheet (Insert/Object) for equation reference and portability. The Mathcad sheet is not linked, which would open Mathcad in a separate window when activated, and which causes errors in the VB Automation code. It is also possible to use the techniques shown in the VB application example to drive a worksheet completely external to the Excel sheet. Since the sheet is embedded, the Mathcad.Worksheet object is called as a property of Excel's OLEObjects collection.
All values set using Mathcad.Application.Worksheet.SetValue are set before other evaluations in the worksheet take place. Beware of resetting or reusing variable names in your calculations; you may wish to make external variable names unique.
It's possible to pass the ActiveSheet.Range.Value property directly as an array into Mathcad using SetValue, however, you must dimension the variable to which you assign it as type variant.
Sub mcadexecute()
'Make sure a cell with numeric data is selected.
If TypeName(Selection) <> "Range" Then
MsgBox "No cell selected."
Exit Sub
End If
'Get the value in the selected cell
s = Selection.Value
'Make sure the cell isn't empty
If s = "" Then
MsgBox "Empty cell selected."
Exit Sub
End If
'Activate the Mathcad object
Dim obj As OLEObject
Set obj = ActiveSheet.OLEObjects(1)
obj.Activate
' Get the Mathcad worksheet
Set Mcws = obj.Object.Worksheet
'Take data from the selected cell and enter it in the worksheet
Mcws.SetValue "voltage", s
'Now take the 13 calibration coefficients from the worksheet, for use
'in the temperature calculation formula, and make a vector
Dim coeff(13) As Double
For I = 0 To 12
coeff(I) = ActiveSheet.Range("A3:A15").Cells(I + 1, 1).Value
Next I
'Pass the vector to Mathcad
Mcws.SetValue "K", coeff
Mcws.Recalculate
'Figure out which row the return data should populate
rowNum = Selection.Rows(1).Row
' Put the output data into the cells.
On Error Resume Next
Cells(rowNum, 4) = Mcws.getValue("temp")
' Report bad returns from Mathcad
If Err Then
Cells(rowNum, 4) = "error in calc"
End If
Cells(rowNum, 5) = Date
End Sub