Documentation |
On this page… |
---|
Spreadsheet Link EX and Microsoft Excel Functions Types of Spreadsheet Link EX Functions Use Spreadsheet Link EX Functions with Microsoft Excel 2007 and Later |
Spreadsheet Link™ EX functions perform an action, while Microsoft^{®} Excel^{®} functions return a value.
Spreadsheet Link EX function names are not case sensitive; that is, MLPutMatrix and mlputmatrix are the same.
MATLAB^{®} function names and variable names are case sensitive; that is, BONDS, Bonds, and bonds are three different MATLAB variables.
Spreadsheet Link EX functions manage the connection and data exchange between the Excel software and the MATLAB workspace, without your ever needing to leave the Excel environment. You can run functions as worksheet cell formulas or in macros. The Spreadsheet Link EX software enables Excel to act as an easy-to-use data-storage and application-development front end for the MATLAB software, which is a powerful computational and graphical processor.
There are two types of Spreadsheet Link EX functions: link management functions and data management functions.
Link management functions initialize, start, and stop the Spreadsheet Link EX and MATLAB software. You can run any link management function other than matlabinit as a worksheet cell formula or in macros. You must run the matlabinit function from the Excel Tools > Macro menu, or in macro subroutines.
Data management functions copy data between the Excel software and the MATLAB workspace, and execute MATLAB commands in the Excel interface. You can run any data management function other than MLPutVar and MLGetVar as a worksheet cell formula or in macros. The MLPutVar and MLGetVar functions can run only in macros.
This example shows how to use the function mlputranges from the Microsoft Excel Ribbon.
Start Microsoft Excel and start MATLAB.
Name and select a range in the worksheet.
Select Send named ranges to MATLAB using the MATLAB group that appears on the top right of the Home tab in your Excel worksheet. When you select this option, MATLAB executes mlputranges.
Microsoft Excel exports the named range into a MATLAB variable.
This example shows how to use the function mlputranges from a cell in the worksheet.
Start Microsoft Excel and start MATLAB.
Name and select a range in the worksheet.
Right-click a cell to list the MATLAB options.
Select MATLAB > Send named ranges to MATLAB. When you select this option, MATLAB executes mlputranges.
Microsoft Excel exports the named range into a MATLAB variable.
Spreadsheet Link EX functions expect A1-style worksheet cell references, that is, columns designated with letters and rows with numbers (the default reference style). If your worksheet shows columns designated with numbers instead of letters:
Select Tools > Options.
Click the General tab.
Under Settings, clear the R1C1 reference style check box.
Enter Spreadsheet Link EX functions directly into worksheet cells as worksheet formulas. Begin worksheet formulas with + or = and enclose function arguments in parentheses. The following example uses MLPutMatrix to put the data in cell C10 into matrix A:
=MLPutMatrix("A", C10)
For more information on specifying arguments in Spreadsheet Link EX functions, see Work with Arguments.
After a Spreadsheet Link EX function successfully executes as a worksheet formula, the cell contains the value 0. While the function executes, the cell might continue to show the formula you entered.
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 EX functions are most effective in automatic calculation mode. To automate the recalculation of a Spreadsheet Link EX function, add to it a cell whose value changes. In the following example, the MLPutMatrix function executes again when the value in cell C1 changes:
=MLPutMatrix("bonds", D1:G26) + C1
To use MLGetMatrix in manual calculation mode:
Enter the function into a cell.
Press F2.
Press Enter. The function executes.
Spreadsheet Link EX functions do not automatically adjust cell addresses. If you use explicit cell addresses in a function, you must edit the function arguments to reference a new cell address when you do either of the following:
Insert or delete rows or columns.
Move or copy the function to another cell.
This section describes tips for managing variable-name arguments and data-location arguments in Spreadsheet Link EX functions.
You can directly or indirectly specify a variable-name argument in most Spreadsheet Link EX 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 get the variable name. The argument must be a worksheet cell address or range name, for example, MLDeleteMatrix(C1).
Note: Spreadsheet Link EX functions do not support global variables. When exchanging data between Excel and MATLAB, the base workspace is used. 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; for example, Sheet3!B1:C7 or Sheet2!OUTPUT.
Tip: You can reference special characters as part of a worksheet name in MLGetMatrix or MLPutMatrix by embedding the worksheet name within single quotation marks (''). |
The MATLAB Function Wizard for the Spreadsheet Link EX software lets you browse MATLAB folders and run functions from the Excel interface.
List all MATLAB working folders and function categories.
All folders or categories in the current MATLABPATH appear in the Select a category field. Click an entry in the list to select it. Each entry in the list appears as a folder path and a description read from the Contents.m file in that folder. If no Contents.m file is found, the folder or category display notifies you as follows:
finance\finsupport -(No table of contents file)
To refresh the folder/category list, click Update.
Select a particular folder or category, and list functions available for that folder or category.
After you select a folder or category, the Select a function field displays available functions for that folder or category. Click a function name to select it.
Tip: The Function Wizard prohibits access to MATLAB constructors and methods. You can write a wrapper function for a method or a constructor and access that wrapper. See Use the Function Wizard to Access Custom MATLAB Functions. |
Select a function signature and enter a formula into the current spreadsheet cell.
After you select a function, the Select a function signature field displays available signatures for that function. Click a function signature to select it.
View help for the selected function.
The Function Help field displays help for the selected function.
When you click a function signature, the Function Arguments dialog box appears.
This dialog box lets you specify the cells that contain input arguments and the cells where to display outputs. By default, the output of the selected function appears in the current spreadsheet cell using the Spreadsheet Link EX function matlabfcn. In the following example, the output appears in the current spreadsheet cell and generates a MATLAB figure:
=matlabfcn("plot",Sheet1!$B$2:$D$4)
Specifying a target range of cells using the Optional output cell(s) field in the Function Arguments dialog box causes the selected function to appear in the current spreadsheet cell as an argument of the matlabsub function. In addition, matlabsub includes an argument that indicates where to write the function's output. In the following example, the data from A2 is input to the rand function, whose target cell is B2:
=matlabsub("rand","Sheet1!$B$2",Sheet1!$A$2)
Tip 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 separate elements a and b, and you specify A1:A2 as output cells, the Function Wizard displays element a in cell A1. It discards element b. If an output is a matrix, the Function Wizard displays all elements of that matrix starting in the first output cell.
In MATLAB, create and save your function. Create a help header in your function that contains supported function signatures to use with the MATLAB Function Wizard. For example, write the function that computes the Fibonacci numbers and save it in the folder Documents\MATLAB:
function f = fibonacci(n) %FIBONACCI(N) Compute the Nth Fibonacci number. % N must be a positive integer. if n < 0 error('Invalid number.') elseif n == 0 f = 0; elseif n == 1 f = 1; else f = fibonacci(n - 1) + fibonacci(n - 2); end; end
Add the folder where you saved the function to the MATLAB search path. To add the folder to the search path, use the pathtool function or select Set Path in the MATLAB Toolstrip.
In Excel, open the MATLAB Function Wizard and select the folder where you saved your function.
The Function Wizard does not let you access MATLAB constructors and methods. To access a method or a constructor from the Function Wizard, write a wrapper function for that method or constructor. For example, to access the timeseries(DATA) constructor from the Function Wizard, write the following wrapper function:
function TS = timeseries_wrapper(DATA) % timeseries_wrapper(DATA) is a wrapper function % for TIMESERIES(DATA) % TS = TIMESERIES(DATA) creates a time series object TS using % data DATA. By default, the time vector ranges from 0 to N-1, % where N is the number of samples, and has an interval of 1 % second. The default name of the TS object is 'unnamed'. T = timeseries(DATA); TS = T.data; end
These examples show how to manipulate MATLAB data using Spreadsheet Link EX.
For an example of how to exchange data between the MATLAB and Excel workspaces, see Import and Export Data Between the Microsoft Excel Interface and the MATLAB Workspace.
For an example of how to export data from the MATLAB workspace and display it in an Excel worksheet, see Send MATLAB Data to an Excel Worksheet and Displaying the Results.
This example shows how to run MATLAB commands using VBA, send MATLAB data to the Excel software, and display the results in an Excel dialog box.
Start an Excel session.
Initialize the MATLAB session by clicking the startmatlab button in the Spreadsheet Link EX toolbar or by running the matlabinit function.
If the Spreadsheet Link EX Add-In is not enabled, enable it.
For instructions on enabling this Add-In for the Excel 2003 software, see Configure Microsoft Excel 2003 and Earlier.
For instructions on enabling this Add-In for the Excel 2007 software, see Configure Microsoft Excel 2007 and Later.
Enable the Spreadsheet Link EX software as a Reference in the Microsoft Visual Basic^{®} Editor. For instructions, see Work with the Microsoft Visual Basic Editor.
In the Visual Basic Editor, create a module.
Right-click the Microsoft Excel Objects folder in the Project — VBAProject browser.
Select Insert > Module.
Enter the following code into the module window:
Option Base 1 Sub Method1() MLShowMatlabErrors "yes" '''To MATLAB: Dim Vone(2, 2) As Double 'Input Vone(1, 1) = 1 Vone(1, 2) = 2 Vone(2, 1) = 3 Vone(2, 2) = 4 MLPutMatrix "a", Range("A1:B2") MLPutVar "b", Vone MLEvalString ("c = a*b") MLEvalString ("d = eig(c)") '''From MATLAB: Dim Vtwo As Variant 'Output MLGetVar "c", Vtwo MsgBox "c is " & Vtwo(1, 1) MLGetMatrix "b", Range("A7:B8").Address MatlabRequest MLGetMatrix "c", "Sheet1!A4:B5" MatlabRequest Sheets("Sheet1").Select Range("A10").Select MLGetMatrix "d", ActiveCell.Address MatlabRequest End Sub
Run the code. Press F5 or select Run > Run Sub/UserForm.
The following dialog box appears.
Click OK to close the dialog box.
Note: Do not include MatlabRequest in a macro function unless the macro function is called from a subroutine. |
Tip: In macros, leave a space between the function name and the first argument. Do not use parentheses. |
This example uses MLGetMatrix in a macro subroutine to export data from the MATLAB matrix A into the Excel worksheet Sheet1.
Sub Test1() MLGetMatrix "A", "Sheet1!A5" MatlabRequest End Sub
This example uses MLPutMatrix in a macro subroutine to import data into the MATLAB matrix A, from a specified cell range in the Excel worksheet Sheet1.
Sub Test2() Set myRange = Range("A1:C3") MLPutMatrix "A", myRange End Sub
matlabfcn | matlabinit | matlabsub | MLGetMatrix | MLGetVar | MLPutMatrix | MLPutRanges | MLPutVar | pathtool