There are two methods that will allow users to return multiple outputs from a function compiled as an Excel Add-In. If you are working with MATLAB R2020a or newer, you can modify the VBA code that is generated for your Add-In. If you are working in a version of MATLAB older than R2020a, you can use the Function Wizard to assist in returning function output to multiple cells, or ranges of cells, in a worksheet.
Modifying the VBA Code:
For example, say I have compiled a function 'myFunc' as an Excel Add-In and I have already integrated it into Excel. 'myFunc' has the following function signature:
[x, y, z] = myFunc(input1, input2, input3)
With this example in mind, follow along with the instructions below to modify the VBA code of your Add-In:
- Open an Excel Sheet.
- From the Developer tab, click Visual Basic, or press ALT+F11 to open the Visual Basic Editor.\n
- In older versions of Excel, it may be located under Tools > Macro > Visual Basic Editor.
- In the Project - VBAProject window, double-click to expand VBAProject (myFunc.xla).
- Expand the Modules folder and double-click the Module1 module. This opens the VB Code window with the code for this project.
- Scroll through this file until you see a commented section that looks like the snippet below:
' If you want to use all the return values, you can use the following\n' code as a guideline. To use this form, call the function from Excel cell,\n' highlight the cells which will receive the outputs and hit Ctrl+Shift+Enter.
The lines of code directly above this commented out block should look something like the code shown below. Comment out these lines of code by adding an apostrophe at the beginning of the line.
' Call Class1.myFunc(3, x, y, z, input1, input2, input3)\n' myFunc = x
And then uncomment the lines below it
Call Class1.myFunc(3, x, y, z, input1, input2, input3)\nmyFunc = Array(x, y, z)
Then save! Once you have updated the VBA code, you will be able to get multiple outputs from the function by following the instructions in the comments. Highlight as many cells as there are outputs, call the function from within these cells (ex: '=myFunc(2,3,4)'), and hit Ctrl+Shift+Enter.
Using Function Wizard:
Key tasks in using the Function Wizard to achieve this goal are:
1) Install the Function Wizard.
2) Start the Function Wizard.
3) Select the option to incorporate your built COM component into Microsoft Excel.
4) Define the new MATLAB function you want to prototype by adding it to the Function Wizard and establishing input and output ranges.
5) Test your MATLAB function by executing it with the Function Wizard.
6) Create a macro.
7) Execute the macro you created using the Function Wizard.
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.
Detailed instructions for each of these key tasks are available in the MATLAB Compiler documentation:
