Return Multiple Outputs from an Excel Add-In Function

Create MATLAB Function

Create a MATLAB function with multiple output arguments. For this example, create a function named mystats.m that returns the minimum, maximum, and mean of the input data.

function [mn, mx, avg] = mystats(data)
    mn = min(data);
    mx = max(data);
    avg = mean(data);
end

Build Excel Add-In

Build the Excel add-in using compiler.build.excelAddIn. Set GenerateVisualBasicFile to on so that the build produces a .bas file you can inspect and modify.

buildResults = compiler.build.excelAddIn('mystats.m', ...
    'AddInName', 'mystatsAddin', ...
    'ClassName', 'mystatsClass', ...
    'GenerateVisualBasicFile', 'on');

The build generates a VBA file named mystatsAddin.bas in the output folder. This file contains the VBA function that calls your packaged MATLAB code.

Modify VBA Code to Return All Outputs

By default, the generated VBA returns only the first output argument. The .bas file contains commented code that shows how to return all outputs.

Open the .bas file or the VBA editor in Excel (Alt+F11). Locate the function body, which looks like this:

' By default, MATLAB Excel Add-in returns just one output for MATLAB functions
' that have more than one output.

Call mystatsClass.mystats(3, mn, mx, avg, data)
mystats = mn

' If you want to use all the return values, you can use the following
' code as a guideline. To use this form, call the function from Excel cell,
' highlight the cells which will receive the outputs and hit Ctrl+Shift+Enter.
'
' Call mystatsClass.mystats(3, mn, mx, avg, data)
' mystats = Array(mn, mx, avg)

To return all three outputs:

The first argument (3) is the number of output arguments. The Array(mn, mx, avg) expression returns all outputs as a VBA array that Excel can spread across cells.

Call Function in Excel

After modifying the VBA code and installing the add-in:

The three cells display the minimum, maximum, and mean of the input data.