# Excel as a function

We have discussed in a previous blog how to create Custom Functions in PTC Mathcad Prime in the form of DLLs: The Down Low on Mathcad DLLs. And even more recently my colleague, Thomas Devaraj, wrote an excellent piece on 6 interesting use cases for the Excel component in PTC Mathcad Prime: best-of-both-worlds-with-the-embedded-excel-component-and-its-top-6-use-cases/. In use case #6 Thomas introduces how to call Excel as a function. This is really powerful and we wanted to show more examples to help users get comfortable with this Excel as a function paradigm.

First I want to state that while we are creating a function, there is no code involved. All development takes place within the PTC Mathcad worksheet and we’ve attached the actual worksheet for you use.

In this slightly modified example from Thomas’s article you will see first that the Excel component provides great support for Excel’s syntax while defining the Input and Output expression of the component.

The embedded Excel worksheet is actually a workbook with two sheets. You can see the Sheet1! and Sheet2! syntax on the input expressions. In the output section of the Excel component (bottom piece) you see getExcelData function defined as a single statement, returning the result of keyword excel cleverly subscripted with the parameter range.

So now the worksheet has a function called getExcelData which can take in any Excel range using Excel syntax to pull back data from the Excel component. We think it’s pretty cool to make the Excel component available as a parameterized function.

OK, let’s look at a different approach to parameterizing the Excel component. In this case we will use an input vector of different sizes and a range variable to compute the Excel component multiple times. There is not a function per se; we are simply animating the Excel component through the use of a range variable.

So in this scenario we create a dummy matrix and feed it into the Excel component using the normal Input expression syntax. We then create a vector called lookup­­val which represents the data values we want to lookup. When we find the desired value we want to return the entire row the matched value resides in. Next we dynamically create a range variable called i using the last function. As lookupval changes so does the range variable i. Another piece of this puzzle is in the embedded Excel sheet, in cell G1 we store the MATCH(H1,A1:A4,0). Finally we assigned to the Excel sheet, cell H1 the elements of lookupval using the range variable i. This is how the repeated call takes place, we take each element of lookupval one at a time, stuff it into H1, run the MATCH expression in G1 and return G1 to rowval[i].

Let’s let that sink in for a minute…. The value 65 from lookupval gets put into H1, H1 feeds MATCH in G1, G1 gets returned to rowval[i] and once again we use the Excel output with Excel syntax created through the concat function. Next the value 56 from lookupval gets put into H1 and we repeat the process. The input array lookupval can grow or shrink and nothing else needs to change. So Excel will be called length of lookupval number of times.

Using the MATCH in Excel is not required for the looping of the component. This was done in Excel because in the real Mathcad worksheet the data shown in Excel was the collection of many PTC Mathcad input arrays.

So it was easier to do it in Excel rather than stack and augment our way to a single Mathcad matrix.

So there you have it, a deeper dive into a new Excel component paradigm. Please let us know if you have questions with regards to this blog or any topics in PTC Mathcad.

Try out PTC Mathcad Express, free today.
Watch a 90 second demo on the Excel component.