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).

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 forumla 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