Main Content

Create Macro Using Multiple MATLAB Functions

This example shows you how to create macros with functions that have variable-length inputs and outputs.

Before you begin, verify that you have met all of the MATLAB® Compiler™ Excel® target requirements. For details, see Excel Target Requirements and Limitations for MATLAB Compiler.

Create Add-In In MATLAB

  1. Copy the xlmulti example folder that ships with MATLAB to your work folder:

    copyfile(fullfile(matlabroot,'toolbox','matlabxl','examples','xlmulti'),'xlmulti')

    At the MATLAB command prompt, navigate to the xlmulti folder in your work folder.

  2. Examine the myplot, myprimes, and mysum functions.

    myplot takes a single integer input and plots a line from 1 to that number.

    function myplot(x)
    plot(1:x)

    myprimes takes a single integer input n and returns all the prime numbers less than or equal to n.

    function p = primes(n)
    if length(n)~=1, error('N must be a scalar'); end
    if n < 2, p = zeros(1,0); return, end
    p = 1:2:n;
    q = length(p);
    p(1) = 2;
    for k = 3:2:sqrt(n)
      if p((k+1)/2)
         p(((k*k+1)/2):k:q) = 0;
      end
    end
    p = p(p>0);

    mysum takes an input of varargin of type integer, adds all the numbers, and returns the result.

    function y = mysum(varargin)
    y = sum([varargin{:}])

  3. Build the Excel add-in with the Library Compiler app or compiler.build.excelAddIn using the following information:

    FieldValue
    Library Namexlmulti
    Class Namexlmulticlass
    Files to compilemyplot.m
    myprimes.m
    mysum.m

    For example, if you are using compiler.build.excelAddIn, type:

    buildResults = compiler.build.excelAddIn(["myplot.m","myprimes.m","mysum.m"], ...
        'AddInName','xlmulti', ...
        'ClassName','xlmulticlass', ...
        'GenerateVisualBasicFile','on');

    For more details, see the instructions in Create Excel Add-In from MATLAB.

Create Excel Macro Using Function Wizard

Start Microsoft® Excel on your system and create a new spreadsheet.

If you have not already done so, install and launch the Function Wizard add-in provided with MATLAB Compiler. For instructions, see Install Function Wizard.

Add myplot Function

  1. On the Function Wizard Control Panel, click Add Function in the Setup Functions area. The MATLAB Components dialog box opens.

  2. In the Available Components area of the MATLAB Components dialog box, select xlmulti 1.0 from the drop-down box.

  3. Select the function myplot from the box labeled Functions for Class xlmulticlass.

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

  5. On the Input tab, click Set Input Data. The Input Data for x dialog box opens.

  6. Select Value and enter the value 4. Click OK.

  7. Click Done. If you execute the macro at this point, the function myplot plots a line from 1 through 4 in a MATLAB Figure window. This graphic can be manipulated similarly to the way one would manipulate a figure in MATLAB. Some functionality, such as the ability to change line style or color, is not available.

Add mysum Function

  1. Enter 1 into cell A1.

  2. Select the cells B1:J1. Enter the formula = A1 + 1, then press CTRL+Enter to apply the formula to the selected cells.

    This procedure fills the range B1:J1 with the interval 1–10 incremented by 1.

  3. On the Function Wizard Control Panel, click Add Function and add the function mysum from the xlmulti 1.0 component.

  4. On the Inputs tab, click Add and then Set Input Data. The Input Data for varargin[1] dialog box opens.

  5. Click the button at the end of the Range field and select the cell range A1:J1 in your worksheet. The range Sheet1!$A$1:$J$1 is entered in the text box.

  6. Select Auto recalculate on change to allow Excel to dynamically update the sum if the input cells change.

  7. Click OK. The input argument varargin[1] = Sheet1!$A$1:$J$1 is added to the Select Input Ranges/Values area.

    Optionally, you can add additional cell ranges or values as input arguments. Cell ranges do not need to be the same size, and selected cells do not require a value.

  8. On the Outputs tab, click Set Output Data and select cell A2 in the range field.

  9. Click Done. The function mysum is added to the Active Functions list with the specified input and output arguments.

  10. Once the macro runs, the cell A2 displays the sum 55. Changing any cells in the input range automatically updates the output cell.

Add myprimes Function

  1. Enter 10 into cell A4.

  2. On the Function Wizard Control Panel, click Add Function and add the function myprimes from the xlmulti 1.0 component.

  3. On the Inputs tab, click Set Input Data and select the cell A4 in the range field.

  4. Select Auto recalculate on change to allow Excel to dynamically update when the input cell changes.

  5. Click OK. The input argument n = Sheet1!$A$4 is added to the Select Input Ranges/Values area.

  6. On the Outputs tab, click Set Output Data and select cell B4 in the range field.

    Ensure Auto Resize is checked to enable the output to fill neighboring cells.

  7. Click Done. The function myprimes is added to the Active Functions list.

  8. Once the macro runs, the cells in row 4 display all prime numbers less than the value in A4. Changing this cell automatically updates the output.

Create and Run Macro

  1. In the Create Macros area, enter a name for your macro in the Macro Name field.

  2. Choose where to store your macro by using the dropdown menu in the Store Macro In field.

  3. (Optional) Add a description for your macro.

  4. Click Create Macro to save your macro in the selected workbook.

    The Function Wizard generates VBA code that call your functions with the specified arguments.

  5. To execute the macro, from the main Excel window, open the Macro dialog box by pressing Alt + F8, or by selecting Macros in the Developer tab.

(Optional) Inspect Microsoft Visual Basic Code

  1. From the Developer tab, click Visual Basic, or press ALT+F11 to open the Visual Basic Editor. In older versions of Excel, it may be located under Tools > Macro > Visual Basic Editor.

  2. In the Project - VBAProject window, double-click to expand VBAProject (Book1).

  3. Expand the Modules folder and double-click the matlabMacros module. This opens the VB Code window with the code for this project. You can use this code to develop your own custom VBA macros.

For Additional Examples

The Microsoft Excel spreadsheet xlmulti.xls located in matlabroot\toolbox\matlabxl\examples\xlmulti demonstrates these functions in several ways.

In this example, the myplot and mysum functions are called directly in the worksheet using the custom function syntax. The macro myprimes executes the myprimes function and dynamically resizes the output.

See Also

| (MATLAB Compiler SDK) |

Related Topics