This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

Work with MATLAB Functions in Microsoft Excel

Differences Between Spreadsheet Link and Microsoft Excel Functions

  • Spreadsheet Link™ functions perform an action, while Microsoft® Excel® functions return a value.

  • Spreadsheet Link 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 functions.

Spreadsheet Link Function Types

Spreadsheet Link 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 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 functions: link management functions and data management functions.

Link management functions initialize, start, and stop the Spreadsheet Link 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 Functions with Microsoft Excel

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 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 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 functions, see Work with Arguments.

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

After a Spreadsheet Link 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 functions are most effective in automatic calculation mode. To automate the recalculation of a Spreadsheet Link 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 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 functions.

Work with Arguments

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

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 get 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 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 Functions in Macros

About the Examples

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

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 toolbar or by running the matlabinit function.

  3. Enable the Spreadsheet Link Add-In. For details, see Configure Microsoft Excel.

  4. Enable the Spreadsheet Link 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
        MLGetMatrix "c", "Sheet1!A4:B5"
        MLGetMatrix "d", ActiveCell.Address
    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"
    End Sub

      Note:   The MatlabRequest function initializes internal Spreadsheet Link 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?