Executing Functions and Creating Macros Using the Function Wizard

On this page…
What Can the Function Wizard Do for Me? Installation of the Function Wizard Function Wizard Start-Up Workflow Selection for MATLAB Functions Ready for Deployment Defining Functions Ready to Execute Function Execution Macro Creation Macro Execution Microsoft Visual Basic Code Access (Optional Advanced Task) For More Information

End User

Role	Knowledge Base	Responsibilities
End user	No MATLAB experience Some knowledge of the data that is being displayed, but not how it was created	In Web environments, consumes what the front-end developer creates Integrates MATLAB code with other third-party applications, such as Excel

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 already be running the Magic Square example in Getting Started.

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 be in the process of developing your MATLAB function for deployment. See End-to-End Deployment of a MATLAB Function Using the Function Wizard for complete instructions for this workflow.

See Choosing the Appropriate Workflow for further details.

Key Tasks for the End User

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

Back to Top

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 Office 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 install_root\toolbox\matlabxl\matlabxl\arch and select FunctionWizard2007.xlam. Click OK.

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

Using Office 2010

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 install_root/toolbox/matlabxl/matlabxl/arch, and select FunctionWizard2007.xlam. Click OK.

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

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

Using Office 2003

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

2. If the Function Wizard was previously installed, MATLAB® Builder™ EX 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 install_root\toolbox\matlabxl\matlabxl folder. Select FunctionWizard.xla. Click OK to proceed.

Back to Top

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 Office 2007 or Office 2010

In Microsoft Excel, on the Microsoft Office ribbon, on the Home tab, select MATLAB Functions.

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

Using Office 2003

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

2. Select Function Wizard.

The Function Wizard Start Page Dialog Box

Back to Top

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 want to incorporate MATLAB Component Files Into Microsoft Excel .

2. Click OK. The Function Wizard Control Panel opens.

   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.

Back to Top

Defining Functions Ready to Execute

1. Define the function you want to execute to the Function Wizard. Click Add 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. The Function Properties dialog box opens.

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 Argument is a Struct Array. 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: http://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 Done.

   Tip   To specify how MATLAB Builder EX 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 Builder EX 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: http://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 Builder EX 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.

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 Argument is a MATLAB Structure Array in the Input Data for n dialog box and click Done.

   

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

   

2. The Function Wizard supports struct arrays organized in the following manner:

   • Vector struct arrays in element-by-element organization (input and output)

   • Two-dimensional struct arrays in element-by-element organization (input and output)

   In the Input Data for Structure Array Argument n dialog box, click Add 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 name of 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: http://support.microsoft.com/kb/291110.

   3. Click Done.

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

The product converts data passed into structure arrays in element-by-element organization. If you need to work with structure arrays in plane organization, first define the function to use structure arrays in element-by-element organization as the function's inputs and outputs. Then, convert structure arrays in MATLAB code from element-by-element to 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 Builder EX 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 the Appropriate Workflow for more information on both workflows.

Back to Top

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

Back to Top

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.

Back to Top

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 error messages occuring from executing macros, see the Troubleshooting appendix.

Using Office 2007 or Office 2010

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 (a Magic Square of dimension 5).

Using Office 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 (a Magic Square of dimension 5).

 Watch a Video Demo

Back to Top

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 Office 2007 or Office 2010

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 VB Code window with the code for this project.

Using Office 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

Back to Top

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	MATLAB Code Deployment
See more examples about building add-ins and COM components	Microsoft Excel Add-in Creation, Function Execution, and Deployment
Learn how to customize and integrate the COM component you built by modifying the Microsoft Visual Basic code	Microsoft Excel Add-in Integration

Back to Top

The Function Wizard

End-to-End Deployment of a MATLAB Function Using the Function Wizard

© 1984-2012- The MathWorks, Inc.    -   Site Help   -   Patents   -   Trademarks   -   Privacy Policy   -   Preventing Piracy   -   RSS