How do I return multiple outputs from an Excel Add-in function compiled using MATLAB Compiler?

How do I return multiple outputs from a MATLAB function compiled as an Excel Add-In?

I compiled a function that returns multiple output arguments of various sizes, but when I attempt to call the function in an Excel cell, it only returns the first element of the first output argument.

Answer 1

[MathWorks Support Team]

There are two methods that allow users to return multiple outputs from a function compiled as an Excel Add‑In. In MATLAB R2020a or newer, the generated VBA code for the Add‑In can be modified. In versions of MATLAB older than R2020a, the Function Wizard can be used to assist in returning function outputs to multiple cells or ranges of cells in a worksheet.

Modifying the VBA Code (for MATLAB R2020a and newer):

For example, suppose a function named "myFunc" has been compiled as an Excel Add‑In and integrated into Excel. The example function has the following signature:

[x, y, z] = myFunc(input1, input2, input3)

With this example in mind, follow the instructions below to modify the VBA code of your Excel Add-In:

1. Open an Excel worksheet.
2. From the Developer tab, click Visual Basic, or press ALT+F11 to open the Visual Basic Editor.
   1. In older versions of Excel, it may be located under Tools > Macro > Visual Basic Editor.
3. In the Project - VBAProject window, double-click to expand VBAProject (myFunc.xla).
4. Expand the Modules folder and double-click the Module1 module. This opens the VB Code window with the code for this project.
5. 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
' 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.

The lines of code directly above this commented‑out block should resemble the following. Comment out these lines by adding an apostrophe at the beginning of each line:

' Call Class1.myFunc(3, x, y, z, input1, input2, input3)
' myFunc = x

Then uncomment the lines below:

Call Class1.myFunc(3, x, y, z, input1, input2, input3)
myFunc = Array(x, y, z)

After updating the VBA code in the Excel Add-In, multiple outputs can be retrieved by following the instructions in the aforementioned code comments. Highlight as many cells as there are outputs, enter the function call in the selected cells (for example: "=myFunc(2,3,4)"), and press Ctrl+Shift+Enter. In modern versions of Excel that support dynamic arrays, the results may spill automatically into adjacent cells, so pressing Ctrl+Shift+Enter is not required.

Automate Returning Multiple Outputs (if desired)

The workflow above calls for manual edits since the VBA code must be modified each time the Add-In is rebuilt. If your workflow demands it, it is possible to automate the editing of the VBA code within your Excel Add-In file generated by MATLAB. At a high level, this requires a few extra steps which are detailed below.

1. Generate a standalone ".BAS" file containing the VBA code for your Excel Add-In. This exposes the VBA code for programmatic modification, which is not possible when the VBA code is embedded in the ".XLA" format. The following code snippet shows how to use the "GenerateVisualBasicFile" flag in MATLAB to generate the ".BAS" file: 

   appFile = fullfile(which("myFunc.m"));
   buildResults = compiler.build.excelAddIn(appFile, ...
   'AddInName','myFunc', ...
   'ClassName','myFuncClass', ...
   'GenerateVisualBasicFile','on');

2. Now that you have a standalone ".BAS" file, you can write a file-parsing script to conduct the edits described in the "Modifying the VBA Code" section above. Your script should read the lines of the ".BAS" file and edit the "Call" lines appropriately. The exact pattern matching and edits will depend on your function names and formatting.
3. Finally, the updated VBA file must be reintegrated into the Excel Add‑In. This can be done programmatically by opening the ".XLA" file and replacing the existing VBA module with the modified ".BAS" file. An example MATLAB workflow using Excel automation is shown below:

   excel = actxserver('Excel.Application');
   excel.Visible = false;
   
   wb = excel.Workbooks.Open("<PATH TO myFunc.xla>");
   vbproj = wb.VBProject.VBComponents;
   
   vbproj.Remove(vbproj.Item('Module1'));
   vbproj.Import("<PATH TO myFunc.bas>");
   
   wb.Save();
   wb.Close(false);
   excel.Quit();

Using Function Wizard (for MATLAB R2019b and older):

The following key tasks are involved when using the Function Wizard to achieve this goal:

1. Install the Function Wizard.
2. Start the Function Wizard.
3. Select the option to incorporate the built COM component into Microsoft Excel.
4. Define the new MATLAB function to prototype by adding it to the Function Wizard and specifying input and output ranges.
5. Test the MATLAB function by executing it within the Function Wizard.
6. Create a macro.
7. Execute the macro using the Function Wizard.
8. Optionally inspect or modify the generated VBA code associated with the COM component. Optionally, attach the generated macro to a GUI button.

Detailed instructions for each of these key tasks are available in the MATLAB Compiler documentation:

https://www.mathworks.com/help/compiler/excel/executing-functions-and-creating-macros-using-the-function-wizard.html