Create Add-In Containing a Custom Function for Use Within Excel

Supported Platform: Windows^® only.

This example shows how to create a Microsoft^® Excel^® add-in containing a custom function for use within Excel. The custom function called mymagic returns an n-by-n matrix filled with positive integers with equal row and column sums.

The function mymagic is written in MATLAB^® and packaged as an Excel add-in using the Library Compiler app in MATLAB Compiler™.

The add-in can be installed on a machine running Excel using the installer generated by the Library Compiler app. Once installed, you add the add-in to your Excel workbook.

The machine where the add-in is installed does not require an installation of MATLAB. However, it does require an installation of MATLAB Runtime. When installing the add-in on a machine, the installer generated by the Library Compiler app will automatically install MATLAB Runtime.

Create a MATLAB Function

Create a MATLAB function named mymagic that returns an n-by-n matrix filled with positive integers with equal row and column sums. Save the function in a file named mymagic.m.

function y = mymagic(x)

y = magic(x)

Test the function at the MATLAB command line.

m = mymagic(5)

m =

    17    24     1     8    15
    23     5     7    14    16
     4     6    13    20    22
    10    12    19    21     3
    11    18    25     2     9

Create Excel Add-In Using Library Compiler App

Type libraryCompiler at the MATLAB command line to open the Library Compiler app.
In the TYPE section of the toolstrip, select Excel add-in as your target type.
In the EXPORTED FUNCTIONS section of the toolstrip, click to add the file mymagic.m to the project.
- In the Library information section of the app, the library name is automatically updated to mymagic, the class name is updated to Class1, and the method name to [y] = mymagic(x).
- In the Files installed for your end user section of the app, the files installed when the add-in is installed on a machine are automatically displayed. These files include:
  - _install.bat
  - mymagic.bas
  - mymagic.xla
  - mymagic_1_0.dll
Click Package to package the MATLAB function as an add-in.
- In the Save Project dialog box that opens, specify a project name and a location where you want to save the project. Library Compiler saves your project and opens a Package dialog box.
- When the packaging process is complete, three folders are generated in the target folder location: for_redistribution, for_redistribution_files_only, and for_testing.
- The for_redistribution folder contains the installer file MyAppInstaller_web.exe that installs the add-in and the MATLAB Runtime. The for_redistribution_files_only folder contains the files that are installed on an end user's machine. These are the same files that are installed by the installer. It contains the following files:
  - _install.bat
  - GettingStarted.html
  - mymagic.bas
  - mymagic.xla
  - mymagic_1_0.dll
For more information about the folders, see Files Generated After Packaging MATLAB Functions.

Create Excel Add-In Using `compiler.build.excelAddIn`

Note

If you have already created an Excel add-in using the Library Compiler app, you can skip this section. However, if you want to know how to create an add-in from the MATLAB command window using a programmatic approach, follow these instructions.

Build the Excel add-in using the compiler.build.excelAddIn function and the mymagic.m file that you wrote earlier. Use name-value arguments to generate the BAS and XLA files.
```
buildResults = compiler.build.standaloneApplication('mymagic.m',...
    'GenerateVisualBasicFile','on');
```
The compiler.build.Results object buildResults contains information on the build type, generated files, and build options.
The function generates the following files within a folder named mymagicexcelAddIn in your current working directory:
- dlldata.c
- GettingStarted.html
- mymagic.def
- mymagic.bas
- mymagic.rc
- mymagic.xla
- mymagic_1_0.dll
- mymagic_dll.cpp
- mymagic_idl.h
- mymagic_idl.idl
- mymagic_idl.tlb
- mymagic_idl_i.c
- mymagic_idl_p.c
- mymagicClass_com.cpp
- mymagicClass_com.hpp
- mccExcludedFiles.log
- mwcomtypes.h
- mwcomtypes_i.c
- mwcomtypes_p.c
- readme.txt
- requiredMCRProducts.txt
- unresolvedSymbols.txt
Additional options can be specified by using one or more comma-separated pairs of name-value arguments in the compiler.build command.
- 'AddInName' — Name of the generated add-in.
- 'AddInVersion' — System level version of the generated add-in.
- 'AdditionalFiles' — Paths to additional files to include in the add-in.
- 'AutoDetectDataFiles' — Flag to automatically include data files.
- 'ClassName' — Name of the class.
- 'DebugBuild' — Flag to enable debug symbols.
- 'EmbedArchive' — Flag to embed the standalone archive in the generated executable.
- 'GenerateVisualBasicFile' — Flag to generate a Visual Basic^® file (.bas) and an Excel add-in file (.xla).
- 'OutputDirectory' — Path to the output directory that contains generated files.
- 'Verbose' — Flag to display progress information indicating compiler output during the build process.

Test the Add-In in Excel

Note

You may have to enable Trust access to the VBA project object model in Excel for the add-in to work.

Add the Add-In to Excel

Open Microsoft Excel.
Click the File tab, click Options, and then click the Add-Ins category.
In the Manage box, click Excel Add-ins, and then click Go. The Add-Ins dialog box appears.
Click Browse and locate the add-in mymagic.xla in the for_redistribution_files_only folder.
You are prompted to copy mymagic.xla to the Addins folder associated with your user name. You can choose to copy the add-in or run it from the for_redistribution_files_only folder. For this example, select, YES. The add-in is copied and added to your workbook.
Click OK to close the Add-Ins dialog box

Test the Add-In

Select a grid of 3-by-3 cells in the Excel workbook.
Enter the following custom function in the formula bar:
```
=mymagic(3)
```
As you type my in the formula bar, you see mymagic showing up as a custom function in Excel.
Press Ctrl+Shift+Enter on the keyboard.
You see the following output displayed in the cells:
```
8	1	6
3	5	7
4	9	2
```

Documentation