Create Excel Add-In Using Struct Array Inputs with Multiple Outputs

R2026b

This example shows how to create an Excel® add-in using a function that accepts a structure input and displays multiple outputs.

In order to call a MATLAB® function using the Microsoft® Excel function syntax (=myfunction(input)), the MATLAB function must return a single scalar output argument. To specify multiple outputs, create a VBA code module or use the Function Wizard to create a macro. For more information, see Integrate Components Using VBA.

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

Write MATLAB Code to Deploy

Write MATLAB code to package into an Excel add-in.

Save the following MATLAB code as twoMagic.m. The function calculates two magic squares with sizes specified by a structure input and returns the output in a structure.

function [y1, y2] = twoMagic(input)
y1 = magic(input.x1);
y2 = magic(input.x2);
end

Test the function at the MATLAB command prompt.

in.x1 = 3;
in.x2 = 4;

[out1, out2] = twoMagic(in)
out1 =

     8     1     6
     3     5     7
     4     9     2


out2 =

    16     2     3    13
     5    11    10     8
     9     7     6    12
     4    14    15     1

Build Excel Add-In

Build the Excel add-in with the Excel Add-in Compiler app or compiler.build.excelAddIn.

Use the following information for your project:

Add-in Name twoMagic
Class Name twoMagicclass
File to compile twoMagic.m

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

buildResults = compiler.build.excelAddIn("twoMagic.m", ...
    AddInName="twoMagic", ...
    ClassName="twoMagicclass", ...
    GenerateVisualBasicFile="on");
For more details, see Create Excel Add-In from MATLAB.

Implement VBA code

After you build your component, implement the VBA code to integrate it into Excel.

  1. Start Microsoft Excel and create a new spreadsheet.

  2. Open your generated Excel add-in.

    From the Developer tab, select Excel Add-Ins.

    In the Add-Ins dialog box, click Browse.

    Browse to twoMagic.xla and click OK.

    The twoMagic add-in appears in the available Add-Ins list with a checked box.

    Click OK to load the add-in.

    This add-in installs a menu item under the Excel Add-ins menu.

  3. From the Developer tab, click Visual Basic or press ALT+F11 to open the Visual Basic Editor.

    Note

    In older versions of Excel, find this option under Tools > Macro > Visual Basic Editor.

  4. In the Visual Basic® Editor, select Tools > References to open the Project References dialog box.

  5. Select the twoMagic 1.0 Type Library entry that corresponds to your generated Excel add-in and MWComUtil X.X Type Library that corresponds to your MATLAB or MATLAB Runtime version. Click OK to add the references.

Create Main VBA Code Module

The add-in requires VBA code to initialize MATLAB Runtime and map the inputs and outputs of the MATLAB function to the specified Excel spreadsheet cells. To manage these tasks, create a Visual Basic code module.

  1. If no module exists under Modules, right-click the VBAProject item in the project window and select Insert > Module.

    A new module appears under Modules in the VBA Project.

  2. In the module property page, change the Name property from Module1 to twoMagicMain. Press F4 to show the property window.

  3. Enter the following code in the twoMagicMain module:

    Note

    Note: Update the MWComUtil.MWArg version number to match your MATLAB or MATLAB Runtime installation.

    'Set input arguments
    Set InputArg1 = CreateObject("MWComUtil.MWArg9.6")
    InputArg1.Value = Application.Sheets.Item("Sheet1").Range("$A$1")
        
    Set InputArg2 = CreateObject("MWComUtil.MWArg9.6")
    InputArg2.Value = Application.Sheets.Item("Sheet1").Range("$B$1")
        
    
    'Set output arguments
    Set OutputArg1 = CreateObject("MWComUtil.MWArg9.6")
    OutputArg1.Value = Application.Sheets.Item("Sheet1").Range("$F$1:$H$3")
    Set OutputArg2 = CreateObject("MWComUtil.MWArg9.6")
    OutputArg2.Value = Application.Sheets.Item("Sheet1").Range("$J$1:$M$4")
    
    'Call the function
    Call theComponent.twoMagic(2, OutputArg1, OutputArg2, InputArg1, InputArg2)

    The code maps the inputs and outputs of the MATLAB function to the specified Excel spreadsheet cells.

Test and Deploy Add-In

To deploy your add-in to end users, see Distribute Add-Ins and Integrate into Microsoft Excel.

After you register the component and install the add-in, you can use the twoMagic function in your spreadsheet.

Test the function by entering the inputs 3 in cell A1 and 4 in cell B1.

When you run the twoMagicMain code module, the spreadsheet displays the values of each magic square according to the cells specified in the VBA code. For instance, the first output is displayed in the cell range F1 to H3.

See Also

|

Topics