Main Content

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

  1. Type libraryCompiler at the MATLAB command line to open the Library Compiler app.

  2. In the TYPE section of the toolstrip, select Excel add-in as your target type.

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

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

  1. Open Microsoft Excel.

  2. Click the File tab, click Options, and then click the Add-Ins category.

  3. In the Manage box, click Excel Add-ins, and then click Go. The Add-Ins dialog box appears.

  4. Click Browse and locate the add-in mymagic.xla in the for_redistribution_files_only folder.

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

  6. Click OK to close the Add-Ins dialog box

Test the Add-In

  1. Select a grid of 3-by-3 cells in the Excel workbook.

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

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