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.
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.
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.
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:
| Full Spreadsheet Link functionality is unavailable. |
Microsoft Excel context menu | Quickly access common Spreadsheet Link functionality in a worksheet cell:
| Full Spreadsheet Link functionality is unavailable. |
Microsoft Excel worksheet cell |
| You cannot execute |
Microsoft Excel VBA macro |
| Requires knowledge of Microsoft Visual Basic®. |
MATLAB Function Wizard |
| Execute a MATLAB function using only the Spreadsheet Link functions |
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.
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.
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.
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.
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 (''
).
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.
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.
matlabfcn
| matlabinit
| MLEvalString
| MLGetMatrix
| MLPutMatrix