Documentation

Work with MATLAB Functions in Microsoft Excel

Differences Between Spreadsheet Link EX and 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.

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

Spreadsheet Link EX Function Types

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

Use Spreadsheet Link EX Functions with Microsoft Excel 2007 and Later

Execute a Function from the Microsoft Excel Ribbon

This example shows how to use the function mlputranges from the Microsoft Excel Ribbon.

  1. Start Microsoft Excel and start MATLAB.

  2. Name and select a range in the worksheet.

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

Execute a Function from a Microsoft Excel Cell

This example shows how to use the function mlputranges from a cell in the worksheet.

  1. Start Microsoft Excel and start MATLAB.

  2. Name and select a range in the worksheet.

  3. Right-click a cell to list the MATLAB options.

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

Use Worksheets

Enter 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. Select 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 Work with Arguments.

    Caution:   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 that 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.

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

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

    Caution:   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, edit the function arguments to reference a new cell address when you are:

  • Inserting or deleting rows or columns.

  • Moving or copying 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.

Work with Arguments

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

    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.

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.

      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 ('').

Use Spreadsheet Link EX Functions in Macros

About the Examples

These examples show how to manipulate MATLAB data using Spreadsheet Link EX.

Send MATLAB Data to an Excel Worksheet

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.

  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.

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

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

Import and Export Data Between Microsoft Excel 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
    

See Also

| | | | | | | |

Related Examples

Was this topic helpful?