Using the Excel Add-In

This document gives complete instructions for creating a working example using the Mathcad Add-In for Excel. It covers the basic insertion of a Mathcad object into Excel and the mapping of properties as well as the creation of a simple 3D plot using the 3D Plot Wizard. Basic knowledge of Excel 97 and Mathcad 2000 is assumed.

Inserting a Mathcad object

On a blank sheet in Excel we first need to insert a Mathcad object.

To insert a Mathcad object:

Click in a cell in your Excel workbook.

Click the New Mathcad Object toolbar button. The New Mathcad Object dialog box appears.

To create an object with a blank Mathcad worksheet, click OK on the Create New tab.

The Mathcad object is inserted into your Excel worksheet at the currently selected cell.

Editing a Mathcad object

Once you insert a Mathcad object into an Excel workbook, you can double-click on it to activate it. Once it is activated you can resize it or edit its contents. The Mathcad object worksheet should contain whatever calculations you want to perform in Mathcad. If you want to exchange data between the Mathcad object worksheet and Excel, you must have appropriate variables defined in the Mathcad object:

Any variables in your Mathcad object whose values you want to send to Excel should be called out0, out1, out2, etc.

Any variables in your Mathcad object whose values should depend on values passed from Excel should be called in0, in1, in2, etc. or should correspond to named ranges in the Excel sheet

The inputs and outputs must be:

Scalar or array values

Real (if an input or output is complex, only the real part is exchanged.)

Numeric (non-numeric data has a value of zero)

For our purposes we will make a simple worksheet that takes two inputs and provides two outputs. The sheet will simply define a function of two variables, f(x,y), and calculate a matrix of values over some range of x and y. This function will depend on two input parameters. We will extract the matrix of values we calculate and place it in the Excel spreadsheet along with a number that represents the maximum value of the matrix. Finally, we will make a surface plot of the matrix and insert it into the spreadsheet. Double-click the Mathcad sheet to activate it and type the equations illustrated below.

Using the Excel Add-In

by Loren Prentice

This document gives complete instructions for creating a working example using the Mathcad Add-In for Excel. It covers the basic insertion of a Mathcad object into Excel and the mapping of properties as well as the creation of a simple 3D plot using the 3D Plot Wizard. Basic knowledge of Excel 97 and Mathcad 2000 is assumed.

Inserting a Mathcad object

On a blank sheet in Excel we first need to insert a Mathcad object.

To insert a Mathcad object:

Click in a cell in your Excel workbook.

Click the New Mathcad Object toolbar button. The New Mathcad Object dialog box appears.

To create an object with a blank Mathcad worksheet, click OK on the Create New tab.

The Mathcad object is inserted into your Excel worksheet at the currently selected cell.

Editing a Mathcad object

Once you insert a Mathcad object into an Excel workbook, you can double-click on it to activate it. Once it is activated you can resize it or edit its contents. The Mathcad object worksheet should contain whatever calculations you want to perform in Mathcad. If you want to exchange data between the Mathcad object worksheet and Excel, you must have appropriate variables defined in the Mathcad object:

Any variables in your Mathcad object whose values you want to send to Excel should be called out0, out1, out2, etc.

Any variables in your Mathcad object whose values should depend on values passed from Excel should be called in0, in1, in2, etc. or should correspond to named ranges in the Excel sheet

The inputs and outputs must be:

Scalar or array values

Real (if an input or output is complex, only the real part is exchanged.)

Numeric (non-numeric data has a value of zero)

For our purposes we will make a simple worksheet that takes two inputs and provides two outputs. The sheet will simply define a function of two variables, f(x,y), and calculate a matrix of values over some range of x and y. This function will depend on two input parameters. We will extract the matrix of values we calculate and place it in the Excel spreadsheet along with a number that represents the maximum value of the matrix. Finally, we will make a surface plot of the matrix and insert it into the spreadsheet. Double-click the Mathcad sheet to activate it and type the equations illustrated below.

We have an equation that will take parameters a and b. We define a matrix M which is the evaluation of the function for x=0..10 and y=0..10. Next we define out0 as this matrix of values and out1 as the maximum value of the matrix. See the Reference Manual for details on the "matrix" and "max" functions.

Defining the Inputs to the Mathcad Object

We now need to define the two inputs a and b. To do this we will simply type values in two adjacent cells and label them as "a" and "b". The labels are shown below in B2:B3 and the cells that hold the actual values are in C2:C3.

For example, to make a named range a, simply click cell C2 and then click in the drop down "Name Box". Type "a" and then press enter to make C2 a named range called "a". This process is shown below.

Next click in cell C3, click the drop down box, type "b" and then press enter to make a named range called "b". Whenever you click one of these cells, their name should appear in the Name Box. For more information, search Excel's Help for "named ranges."

Now your Excel sheet should look something like this:

Setting the Mathcad object properties

Now that we have inserted a Mathcad object, edited it, and set up the inputs and the output locations, we need to configure the properties. The properties specify which cells in the Excel workbook send data to the Mathcad object as input and which cells receive data from Mathcad as output.

To set the Mathcad object properties, click on the Set Mathcad Object Properties icon and you will see a dialog:

The Inputs table is designed to let you specify a cell, ranges of cells, or named range to map as inputs to the Mathcad worksheet. The Outputs table lets you specify where outputs from the Mathcad worksheet should be placed in the Excel sheet. The lower table allows you to select which named ranges should be available to use as inputs or outputs in the tables.

In this example, we want to use a and b as inputs so we check them. Go to the inputs table and select a to map to in0 and b to map to in1 from the drop down boxes. The named ranges a and b will be assigned to variables of the same name in your Mathcad worksheet. In the same way, go to the outputs table and type "a17" (or some cell below the Mathcad object) as the starting cell for "out0", which will contain the matrix M. Note that the Add-In will add the correct sheet to your entry if you don't specify it. Finally, type max as the named range to which out1 in the Mathcad worksheet will be sent. Click "OK" to accept the choices and set the Mathcad object's properties.

The inputs are now sent to the Mathcad object and the outputs are retrieved and placed on the sheet in the designated cells. Your sheet should look something like this:

Note that the sheet now contains the 11x11 matrix of data specified as "out0" along with the maximum value of the matrix, specified as "out1".

These steps demonstrate the basic usage of the Mathcad Add-In for Excel. To review, we inserted a new Mathcad object and edited its contents. Then we set up the inputs and the output locations as named ranges. Next we specified the mappings of cells as inputs to Mathcad and as outputs from Mathcad.

If you now change any inputs, the Mathcad object will recalculate and the updated output will appear in the Excel sheet. This feature allows you to create a live link between Excel and Mathcad and allows for experimentation with the inputs.

The next section discusses using the Plot Wizard to make a 3D Surface Plot of the data.

Creating a 3D Surface Plot

Creating a 3D plot is easy with the Plot Wizard. First, select the matrix of data that you want to plot. For our example, we will select the 11x11 matrix of values pictured above. Now click on the 3D Plot Wizard button on the toolbar. You will see the first page of the Wizard as follows:

The Wizard prompts you to select the plot type and the number of surfaces you want. In this case, we will not change the settings, since we want a single surface plot. Click 'Next' to continue and view the following page:

The second page of the Wizard asks you to select the data format. From our selection of cells, it intelligently infers that we are plotting a matrix of data, so we click 'Next' to continue and see:

At this point, we are asked to confirm the range of cells we wish to plot. The Plot Wizard displays the range of cells we've chosen and also highlights our selection on the spreadsheet as a visual aid. Click 'Next' to continue since this is indeed the range we'd like to plot. The final page of the Wizard appears:

We see a preview of our plot at the right and some initial plot settings at the left that allow us to adjust the axis style and specify a plot title. We would like corner axes with each axis numbered, so we will leave these choices alone. To add a title to the plot, we simply type in the text field and an updated preview of the plot appears on the right.

Click 'Finish' to insert the plot into the spreadsheet. Initially, the sheet is sent into design mode, which allows us to left click and move the plot. We will put it to the right of the Mathcad object and resize it using the box handles at the corners.

To interact with the plot as we would in Mathcad we need to go to run mode. To do this, simply click the Exit Design Mode button on the Mathcad Add-In for Excel toolbar. Now you can drag to rotate the plot and alter it by double clicking to get its properties dialog. Adjust the plot's properties and view to your liking just as you would in Mathcad. The sheet should now appear as below:

This article demonstrates a simple example of using the Mathcad Add-In for Excel to access the computational power and rich 3D plots provided by Mathcad. Additional working examples can be found by choosing "MathcadExamples…" from the Excel menu. Detailed Help on inserting a Mathcad object and creating