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.

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.

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

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

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

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