Spreadsheet Link™ EX

About Functions

How Spreadsheet Link EX Functions Differ from Microsoft Excel Functions

• 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. Standard MATLAB function names are always lowercase; for example, plot(f).

Note   Excel operations and function keys may behave differently with Spreadsheet Link EX functions.

Back to Top

Types of Spreadsheet Link EX Functions

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 theExcel product 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.

For more information about Spreadsheet Link EX functions, see Function Reference.

Back to Top

Using Worksheets

Entering Functions into Worksheet Cells

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:

1. Click Tools > Options.

2. Click the General tab.

3. 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 Working with Arguments in Spreadsheet Link EX Functions.

Note   Do not use the Excel Function Wizard. It can generate unpredictable results.

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, click Excel Tools Options > Edit > Move Selection after Enter. This action provides a useful confirmation for lengthy operations.

Automatic Calculation Mode Vs. Manual Calculation Mode

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 reexecutes when the value in cell C1 changes:

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

Note   Be careful to avoid creating endless recalculation loops.

To use MLGetMatrix in manual calculation mode:

1. Enter the function into a cell.

2. Press F2.

3. 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.

Note   Pressing F9 to recalculate a worksheet affects only Excel functions. This key does not operate on Spreadsheet Link EX functions.

Back to Top

Working with Arguments in Spreadsheet Link EX Functions

This section describes tips for managing variable-name arguments and data-location arguments in Spreadsheet Link EX functions.

Variable-Name Arguments

• 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).

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; for example, Sheet3!B1:C7 or Sheet2!OUTPUT.

  Note   You can reference special characters as part of a worksheet name in MLGetMatrix or MLPutMatrix by embedding the worksheet name within single quotation marks ('').

Back to Top

Using the MATLAB Function Wizard for the Spreadsheet Link EX Software

The MATLAB Function Wizard for the Spreadsheet Link EX software allows you to browse MATLAB directories and run functions from within the Excel interface.

You can use this wizard to:

1. Display a list of all MATLAB working directories and function categories

   All directories or categories in the current MATLABPATH display in the Select a category field. Click an entry in the list to select it. Each entry in the list displays as a directory path plus a description read from the Contents.m file in that directory. If no Contents.m file is found, the directory/category display notifies you as follows:

   finance\finsupport -(No table of contents file)

   To refresh the directory/category list, click the Update button.

2. Choose a particular directory or category, and list functions available for that directory or category

   After you select a directory or category, available functions for that directory or category display in the Select a function field. Click a function name to select it.

3. Parse a specified function signature and enter a formula into the current spreadsheet cell

   After you select a function, available function signatures for the specified function display in the Select a function signature field. Click a function signature to display the Function Arguments pane.

   

   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 displays 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)

4. Display online help headers for functions

   After you select a function signature from the Select a function signature field, its help header appears in the Function Help field.

Back to Top

Examples: Using Spreadsheet Link EX Functions in Macros

About the Examples

This section contains examples that 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 Importing and Exporting 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 Sending MATLAB Data to an Excel Worksheet and Displaying the Results.

Importing and Exporting Data between the Microsoft Excel Interface and the MATLAB Workspace

• 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

  Note   The MatlabRequest function initializes internal Spreadsheet Link EX variables and enables MLGetMatrix to function in the subroutine.

• 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
  

Sending MATLAB Data to an Excel Worksheet and Displaying the Results

In this example, you run MATLAB commands using VBA, send MATLAB data to the Excel software, and display the results in an Excel dialog box.

1. Start an Excel session.

2. Initialize the MATLAB session by clicking the startmatlab button in the Spreadsheet Link EX toolbar or by running the matlabinit function.

3. 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 Configuring Version 2003 and Earlier Versions of the Microsoft Excel Software.

   • For instructions on enabling this add-in for the Excel 2007 software, see Configuring Version 2007 of the Microsoft Excel Software.

4. Enable the Spreadsheet Link EX software as a Reference in the Microsoft^® Visual Basic^® editor.

   1. Open a Visual Basic^® session.

      • If you are running the Excel 2003 software, click Tools > Macro > Visual Basic Editor.

      • If you are running the Excel 2007 software, click the Visual Basic button, , or press Alt+F11.

   2. In the Visual Basic toolbar, click Tools > References.

   3. In the References — VBA Project dialog box, select the SpreadsheetLinkEX check box.

   4. Click OK.

5. In the Visual Basic editor, create a module.

   1. Right-click the Microsoft Excel Objects folder in the Project — VBAProject browser.

   2. Select Insert > Module.

6. 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
   

   Tip   Copy and paste this code into the Visual Basic editor from the HTML version of the documentation.

7. Run the code. Press F5 or click Run > Run Sub/UserForm.

   The following dialog box appears.

   

8. 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.

Back to Top

Starting and Stopping the Spreadsheet Link EX Software

Working with Dates