Documentation

This is machine translation

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

Note: This page has been translated by MathWorks. Click here to see
To view all translated materials including this page, select Country from the country navigator on the bottom of this page.

Execute Functions and Create Macros

If your MATLAB® function is ready to be deployed and you have already built your add-in and COM component with the Deployment Tool, follow this workflow to incorporate your built COM component into Microsoft Excel using the Function Wizard. To follow the workflow in this section effectively, you must run Create Excel Add-In from MATLAB.

The Function Wizard also allows you to iteratively test, develop, and debug your MATLAB function. Using this end-to-end workflow assumes you are still in the process of developing your MATLAB function for deployment. See End-to-End Deployment of MATLAB Function for complete instructions for this workflow.

See Choosing Function Deployment Workflow for further details.

Key Tasks for the End User

TaskReference
1. Install the Function Wizard.Installation of the Function Wizard
2. Start the Function Wizard.Function Wizard Start-Up
3. Select the option to incorporate your built COM component into Microsoft Excel.Workflow Selection for MATLAB Functions Ready for Deployment
4. Define the new MATLAB function you want to prototype by adding it to the Function Wizard and establishing input and output ranges.Defining Functions Ready to Execute
5. Test your MATLAB function by executing it with the Function Wizard.Function Execution
6. Create a macro.Macro Creation
7. Execute the macro you created using the Function Wizard.Macro Execution
8. Optionally inspect or modify the Microsoft® Visual Basic® code you generated with the COM component. Optionally, attach the macro you created to a GUI button.Microsoft Visual Basic Code Access (Optional Advanced Task)

What Can the Function Wizard Do for Me?

The Function Wizard enables you to pass Microsoft Excel® (Excel 2000 or later) worksheet values to a compiled MATLAB model and then return model output to a cell or range of cells in the worksheet.

The Function Wizard provides an intuitive interface to Excel worksheets. You do not need previous knowledge of Microsoft Visual Basic for Applications (VBA) programming.

The Function Wizard reflects any changes that you make in the worksheets, such as range selections. You also use the Function Wizard to control the placement and output of data from MATLAB functions to the worksheets.

Note

The Function Wizard does not currently support the MATLAB sparse, and complex data types.

Installation of the Function Wizard

Before you can use the Function Wizard, you must first install it as an add-in that is accessible from Microsoft Excel.

After you install the Function Wizard, the entry MATLAB Functions appears as an available Microsoft Excel add-in button.

Using Microsoft Excel 2010 or newer versions of Excel

  1. Click the File tab.

  2. On the left navigation pane, select Options.

  3. In the Excel Options dialog box, on the left navigation pane, select Add-Ins.

  4. In the Manage drop-down, select Excel Add-Ins, and click Go.

  5. In the Add-Ins dialog box, click Browse.

  6. Browse to matlabroot/toolbox/matlabxl/matlabxl/arch, and select FunctionWizard2007.xlam. Click OK.

  7. In the Excel Add-Ins dialog, verify that the entry MATLAB Compiler Function Wizard is selected. Click OK.

The Home tab of the Microsoft Excel Ribbon should now contain the Function Wizard tile. See The Home Tab of the Microsoft Office Ribbon with Function Wizard Installed.

Using Excel 2007

  1. Start Microsoft Excel if it is not already running.

  2. Click the Office Button () and select Excel Options.

  3. In the left pane of the Excel Options dialog box, click Add-Ins.

  4. In the right pane of the Excel Options dialog box, select Excel Add-ins from the Manage drop-down box.

  5. Click Go.

  6. Click Browse. Navigate to matlabroot\toolbox\matlabxl\matlabxl\arch and select FunctionWizard2007.xlam. Click OK.

  7. In the Excel Add-Ins dialog box, verify that the entry MATLAB Compiler Function Wizard is selected. Click OK.

Using Excel 2003

  1. Select Tools > Add-Ins from the Excel main menu.

  2. If the Function Wizard was previously installed, MATLAB Compiler Function Wizard appears in the list. Select the item, and click OK.

    If the Function Wizard was not previously installed, click Browse and navigate to matlabroot\toolbox\matlabxl\matlabxl folder. Select FunctionWizard.xla. Click OK to proceed.

Function Wizard Start-Up

Start the Function Wizard in one of the following ways. When the wizard has initialized, the Function Wizard Start Page dialog box displays.

Using Microsoft Excel 2007 or newer versions of Excel

In Microsoft Excel, on the Microsoft Office ribbon, on the Home tab, select Function Wizard.

The Home Tab of the Microsoft Office Ribbon with Function Wizard Installed

You can also access Function Wizard from the File tab.

  1. Select File > Options > Add-Ins from the Excel main menu.

  2. Select Function Wizard.

The Function Wizard Start Page Dialog Box

Workflow Selection for MATLAB Functions Ready for Deployment

After you have installed and started the Function Wizard, do the following:

  1. From the Function Wizard Start Page, select the option I have an Add-in component that was built in MATLAB with the Deployment Tool that I want to integrate into a workbook.

  2. Click OK. The Function Wizard Control Panel opens with the Add Function button enabled.

    The Function Wizard Control Panel for Working with MATLAB Functions Ready for Deployment

Tip

To return to the Function Wizard Start Page, click Start Over.

Defining Functions Ready to Execute

  1. Define the function you want to execute to the Function Wizard. Click Add Function in the Set Up Functions area of the Function Wizard Control Panel. The MATLAB Components dialog box opens.

  2. In the Available Components area of the MATLAB Components dialog box, select the name of your component (xlmagic) from the drop-down box.

  3. Select the function you want to execute (mymagic) from the box labeled Functions for Class xlmagic.

  4. Click Add Function. The Function Properties dialog box opens.

    Tip

    The Function Syntax and Help area, in the Function Properties dialog box, displays the first help text line (sometimes called the H1 line) in a MATLAB function. Displaying these comments in the Function Properties dialog box can be helpful when deploying new or unfamiliar MATLAB functions to end-users.

  5. Define input argument properties as follows.

    1. On the Input tab, click Set Input Data. The Input Data for n dialog box opens.

    2. Specify a Range or Value by selecting the appropriate option and entering the value. If the argument refers to a structure array (struct), select the option This is a MATLAB structure array argument. See Working with Struct Arrays for information on assigning ranges and values to fields in a struct array.

      Caution

      Avoid selecting ranges using arrow keys. If you must use arrow keys to select ranges, apply the necessary fix from the Microsoft site: https://support.microsoft.com/kb/291110.

      Note

      Select the Auto Recalculate on Change option to force Microsoft Excel to automatically recalculate the spreadsheet data each time it changes.

    3. Click OK.

    Tip

    To specify how MATLAB Compiler™ for Excel add-ins handles blank cells (or cells containing no data), see Empty Cell Value Control.

  6. Define output argument properties as follows.

    1. On the Output tab, click Set Output Data. The Output Data for y dialog box appears, where y is the name of the output variable you are defining properties of.

      Tip

      You can also specify MATLAB Compiler to Auto Resize, Transpose or output your data in date format (Output as date). To do so, select the appropriate option in the Argument Properties for y dialog box.

    2. Specify a Range. Alternately, select a range of cells on your Excel sheet; the range will be entered for you in the Range field.

      Caution

      Avoid selecting ranges using arrow keys. If you must use arrow keys to select ranges, apply the necessary fix from the Microsoft site: https://support.microsoft.com/kb/291110.

    3. Select Auto Resize if it is not already selected.

    4. Click Done in the Output Data for y dialog box.

    5. Click Done in the Function Properties dialog box.

mymagic now appears in the Active Functions list of the Function Wizard Control Panel.

Empty Cell Value Control

You can specify how MATLAB Compiler processes empty cells, allowing you to assign undefined or unrepresented (NaN, for example) data values to them.

To specify how to handle empty cells, do the following.

  1. Click Options in the Input Data for N dialog box.

    The Input Conversion Options dialog box opens.

  2. Click the Treat Missing Data As drop-down box.

  3. Specify either Zero or NaN (Not a Number), depending on how you want to handle empty cells.

Working with Struct Arrays

To assign ranges to fields in a struct array, do the following:

  1. If you have not already done so, select This is a MATLAB structure array argument in the Input Data for n dialog box and click OK.

    The Input Data for Structure Array Argument n dialog box opens.

  2. The Function Wizard supports Vector and Two-dimensional struct arrays organized in either Element by Element or Plane organization, for both input and output.

    In the Input Data for Structure Array Argument n dialog box, do the following:

    1. In the Structure Array Organization area, select either Element by Element Organization or Plane Organization.

    2. Click Add Field to add fields for each of your struct array arguments. The Field for Structure Array Argument dialog box opens.

  3. In the Field for Argument dialog box, do the following:

    1. In the Name field, define the field name. The Name you specify must match the field name of the structure array in your MATLAB function.

    2. Specify the Range for the field.

      Caution

      Avoid selecting ranges using arrow keys. If you must use arrow keys to select ranges, apply the necessary fix from the Microsoft site: https://support.microsoft.com/kb/291110.

    3. Click Done.

How Structure Arrays are Supported.  MATLAB Compiler supports one and two-dimensional MATLAB structure arrays.

The product converts data passed into structure arrays in element-by-element organization or plane organization. See MATLAB Programming Fundamentals for more information about all MATLAB data types, including structures.

Deploying Structure Arrays as Inputs and Outputs.  If you are a MATLAB programmer and want to deploy a MATLAB function with structure arrays as input or output arguments, build Microsoft Excel macros using the Function Wizard and pass them (with the Excel add-in and COM component) to the end users. If you can’t do this, let your end users know:

  • Which arguments are structure arrays

  • Field names of the structure arrays

Using Macros with Struct Arrays.  The macro generation feature of MATLAB Compiler for Excel add-ins works with struct arrays as input or output arguments. See Macro Creation if you have a MATLAB function you are ready to deploy. See Microsoft Excel Add-In and Macro Creation Using the Function Wizard if you are using the Function Wizard to create your MATLAB function from scratch. See Choosing Function Deployment Workflow for more information on both workflows.

Function Execution

In the Execute Functions area of the Function Wizard Control Panel, click Execute to run mymagic. Cells A1:E5 on the Excel sheet are automatically populated with the output of mymagic (a magic square of dimension 5).

Macro Creation

Continuing the example, create a Microsoft Excel macro using the Function Wizard Control Panel:

  1. In the Create Macros area of the control panel, enter mymagic in the Macro Name field.

  2. Select the location of where to store the macro in the Store Macro drop-down box.

  3. Enter a brief description of the macro's functionality in the Description field.

  4. Click Create Macro.

    A macro is created in the current Excel workbook.

Macro Execution

Run the macro you created in Macro Creation by doing one of the following, after first clearing cells A1:E5 (which contain the output of the Magic Square function you ran in Function Execution).

Tip

You may need to enable the proper security settings before running macros in Microsoft Excel. For information about macro permissions and related error messages, see the Errors and Solutions appendix.

Using Excel 2007 or newer versions of Excel

  1. In Microsoft Excel, click View > Macros > View Macros.

  2. Select mymagic from the Macro name drop-down box.

  3. Click Run. Cells A1:E5 on the Excel sheet are automatically populated with the output of mymagic.

Using Excel 2003

  1. In Microsoft Excel, click Tools > Macro > Macros.

  2. Select mymagic from the Macro name drop-down box.

  3. Click Run. Cells A1:E5 on the Excel sheet are automatically populated with the output of mymagic.

Microsoft Visual Basic Code Access (Optional Advanced Task)

Optionally, you may want to access the Visual Basic code or modify it, depending on your programming expertise or the availability of an Excel developer. If so, follow these steps.

From the Excel main window, open the Microsoft Visual Basic editor by doing one of the following. select Tools > Macro > Visual Basic Editor.

Using Excel 2007 or newer versions of Excel

  1. Click Developer > Visual Basic.

  2. When the Visual Basic Editor opens, in the Project - VBAProject window, double-click to expand VBAProject (mymagic.xls).

  3. Expand the Modules folder and double-click the Matlab Macros module.

    This opens the Visual Basic Code window with the code for this project.

Using Excel 2003

  1. Click Tools > Macro > Visual Basic Editor.

  2. When the Visual Basic Editor opens, in the Project - VBAProject window, double-click to expand VBAProject (mymagic.xls).

  3. Expand the Modules folder and double-click the Matlab Macros module.

    This opens the VB Code window with the code for this project.

Mapping a Macro to a GUI Button or Control (Optional)

To attach the macro to a GUI button, do the following:

  1. Click Developer > Insert.

  2. From the Form Controls menu, select the Button (Form Control) icon.

    Tip

    Hover your mouse over the Form Controls menu to see the various control labels.

  3. In the Assign Macros dialog box, select the macro you want to assign the GUI button to, and click OK.

Attaching a Macro to a Button

For More Information

If you want to...See...
  • Perform basic MATLAB Programmer tasks

  • Understand how the deployment products process your MATLAB functions

  • Understand how the deployment products work together

  • Explore guidelines about writing deployable MATLAB code

Write Deployable MATLAB Code
See more examples about building add-ins and COM componentsCreate Macros from MATLAB Functions
Learn how to customize and integrate the COM component you built by modifying the Microsoft Visual Basic codeIntegrate Components using Visual Basic Application