Executing Spreadsheet Link Functions

Spreadsheet Link™ functions manage the connection and data exchange between Microsoft^® Excel^® and MATLAB^®, without leaving the Microsoft Excel environment.

To execute Spreadsheet Link functions, you must:

Understand the differences between these functions and Microsoft Excel functions.
Choose the right function type, execution method, and calculation mode for your situation.
Decide how to specify functions and arguments.

Spreadsheet Link and Microsoft Excel Function Differences

In Microsoft Excel, entering Spreadsheet Link functions can be similar to Microsoft Excel functions. The differences include:

Spreadsheet Link functions perform an action, while Microsoft Excel functions return a value.
Spreadsheet Link function names are case-insensitive. Entering either MLPutMatrix or mlputmatrix executes the MLPutMatrix function.
MATLAB function names and variable names are case-sensitive. For example, BONDS, Bonds, and bonds are three different MATLAB variables.

Spreadsheet Link Function Types

There are link management and data management functions in Spreadsheet Link.

Link management functions initialize, start, and stop the Spreadsheet Link and MATLAB software. Execute the matlabinit function from the Excel Tools > Macro menu or in macro subroutines.

Data management functions copy data between Microsoft Excel and the MATLAB workspace. These functions execute MATLAB commands in Microsoft Excel. Except for MLPutVar and MLGetVar, you can execute any data management function as a worksheet cell formula or in a VBA macro. The MLPutVar and MLGetVar functions execute only in VBA macros.

Spreadsheet Link Function Execution Method

You can execute Spreadsheet Link functions using these various methods.

Execution Method	Advantages	Limitations
Microsoft Excel ribbon	Quickly access common Spreadsheet Link functionality in the MATLAB group: `matlabinit` `MLPutMatrix` `MLPutRanges` `MLGetMatrix` `MLEvalString` `MLGetFigure` MATLAB Function Wizard (For details, see Find and Execute MATLAB Function Using MATLAB Function Wizard.) Preferences (For details, see Setting Spreadsheet Link Preferences.)	Full Spreadsheet Link functionality is unavailable.
Microsoft Excel context menu	Quickly access common Spreadsheet Link functionality in a worksheet cell: `MLPutMatrix` `MLPutRanges` `MLEvalString` `MLGetFigure` MATLAB Function Wizard (For details, see Find and Execute MATLAB Function Using MATLAB Function Wizard.) `MLGetMatrix`	Full Spreadsheet Link functionality is unavailable.
Microsoft Excel worksheet cell	Execute any Spreadsheet Link function. Execute MATLAB functions.	You cannot execute `MLGetVar`, `MLPutVar`, or `matlabinit` within a worksheet cell.
Microsoft Excel VBA macro	Execute any Spreadsheet Link function. Execute MATLAB functions. Execute advanced VBA code.	Requires knowledge of Microsoft Visual Basic^®.
MATLAB Function Wizard	Find MATLAB function by category or folder. Explore MATLAB function syntaxes. Execute MATLAB function by choosing a syntax and specifying arguments. Execute custom MATLAB function.	Execute a MATLAB function using only the Spreadsheet Link functions `matlabfcn` and `matlabsub`.

Specify Spreadsheet Link Function in Microsoft Excel

When you specify a Spreadsheet Link function in a worksheet cell, enter the formula by starting with a + or = sign. Then, enclose function arguments in parentheses. This example formula uses the MLPutMatrix function to export data in cell C10 into matrix A.

=MLPutMatrix("A",C10)

In VBA macros, leave a space between the function name and the first argument. Do not use parentheses.

MLPutMatrix "A",C10

To change the active cell when an operation completes, select Excel Tools Options > Edit > Move Selection after Enter. This action provides a useful confirmation for lengthy operations.

Set Calculation Mode

Spreadsheet Link functions are most effective in automatic calculation mode. To automate the recalculation of a Spreadsheet Link function, add a cell reference to a cell whose value changes. For example, the MLPutMatrix function executes again when the value in cell C1 changes.

=MLPutMatrix("bonds", D1:G26) + C1

To use MLPutMatrix in manual calculation mode:

Enter the function into a cell.
Press F2.
Press Enter. The function executes.

Spreadsheet Link functions do not automatically adjust cell addresses. If you use explicit cell addresses in a function, edit the function arguments to reference a new cell address when you:

Insert or delete rows or columns.
Move or copy the function to another cell.

Specify Spreadsheet Link Function Arguments

You can specify arguments in Spreadsheet Link functions using the variable name or by referencing the data location for the argument.

Note:

Spreadsheet Link functions expect the default reference style (A1) worksheet cell references. The columns must be designated with letters and the rows with numbers. If your worksheet shows columns designated with numbers instead of letters, then follow this procedure:

Select Tools > Options.
Click the General tab.
Under Settings, clear the R1C1 reference style check box.

Variable-Name Arguments

You can directly or indirectly specify a variable-name argument in most Spreadsheet Link functions.
- To specify a variable name directly, enclose it in double quotation marks, for example, =MLDeleteMatrix("Bonds").
- To specify a variable name as an indirect reference, enter it without quotation marks. The function evaluates the contents of the argument to retrieve the variable name. The argument must be a worksheet cell address or range name; for example, =MLDeleteMatrix(C1).

Note

Spreadsheet Link functions do not support global variables. When exchanging data between Excel and MATLAB, the software uses the base workspace. Variables in the base workspace exist until you clear them or end your MATLAB session.

Data-Location Arguments

A data-location argument must be a worksheet cell address or range name.
Do not enclose a data-location argument in quotation marks (except in MLGetMatrix, which has unique argument conventions).
A data-location argument can include a worksheet number such as Sheet3!B1:C7 or Sheet2!OUTPUT.
Tip:
You can reference special characters as part of a worksheet name in MLGetMatrix or MLPutMatrix by enclosing the worksheet name within single quotation marks ('').

Specify MATLAB Function in MATLAB Function Wizard

After you find the MATLAB function or custom function in the MATLAB Function Wizard, you can specify the syntax and arguments. Then, Spreadsheet Link specifies this command for evaluation in the MATLAB workspace.

To execute a MATLAB function with multiple outputs, specify where to write the output.

Specifying a target range of cells using the Optional output cell(s) field causes the selected function to appear in the current worksheet cell as an argument of matlabsub. The matlabsub function includes an argument that indicates where to write the output. For example, the data from A2 is input to the rand function and the target cell for output is B2:
```
=matlabsub("rand","Sheet1!$B$2",Sheet1!$A$2)
```
Although the Function Wizard lets you specify multiple output cells, it does not return multiple outputs. If you specify a range of output cells, the wizard returns the first output argument starting in the first output cell. For example, if a function returns two elements a and b, and you specify A1:A2 as output cells, the Function Wizard displays a in cell A1. The Function Wizard discards element b. If an output is a matrix, the Function Wizard displays all elements of that matrix starting in the first output cell.
For multiple output arguments, see Return Multiple Output Arguments from MATLAB Function.

To execute multiple MATLAB functions or use MATLAB objects, write a wrapper function.

The Function Wizard does not allow simultaneous execution of multiple MATLAB functions. Write a wrapper function instead. For example, to plot historical closing-price data from Bloomberg^®, enter this code in MATLAB and save it as a function.

function plotbloombergdata(s)
% plotbloombergdata is a wrapper function that connects to 
% Bloomberg(R), retrieves historical closing-price data for 
% the year 2015, and plots the prices for a given 
% Bloomberg(R) security s.
    c = blp;
    f = 'LAST_PRICE';
    fromdate = '01/01/2015';
    todate = '12/31/2015';
    d = history(c,s,f,fromdate,todate);
    plot(d(:,1),d(:,2))
    close(c)
end

For details about writing functions, see Create Functions in Files (MATLAB).

Microsoft Excel has no context for MATLAB objects. To work with MATLAB objects, such as connections to service providers, write a wrapper function. The wrapper function executes the functions that create and manipulate these objects.