How can I call a function from VBA using MATLAB Builder EX?

The builder works fine for functions that you want to have on the worksheet itself, but if you try to call the function from VBA, you get an excel-generated error:

Compile Error: Sub for Function not defined

You can recreate the error by calling "testSub" in the worksheet "TEST.xls" or by trying to execute the function in VBA. The Add-in that I created for this is contained in the "distrib" subfolder.

Answer 1

[MathWorks Support Team]

This is a VBA referencing issue. This can be caused if any of the following steps is missing:

1) Change the project name:

The first thing to do is give your Add-In a unique VBA Project Name. This is because if there are more than one project with the same name the Visual Basic Editor won't know which one to use.

In the Project Explorer pane select the name of your Add-In. If it isn't already open, display the Visual Basic Editor's Properties Window. You will see that there is only one property, the Name. Type another name and press Enter. You will have to obey the usual naming rules for VBA (i.e. no illegal characters and no spaces). You will see that the name is immediately applied in the Project Explorer.

Now save the changes to your Add-In. Make sure your Add-In is selected in the Project Explorer and choose File >Save.

2) Add a reference to the Add-in:

Open a code module in the workbook in which you want to use the Add-In's functions then go to Tools > References to open the References dialog box. You will have to find your Add-In file by clicking the Browse button on the References dialog. This opens the Add Reference window. Change the Files of type: section to Microsoft Excel Files(*.xls;*.xla) then browse to the folder where the Add-In is stored (eg: xlmagic.xla).

3) Register the corresponding DLL:

Open command prompt and change directory to the folder where the .dll file is stored (eg: C:matlab\work\xlmagic\xlmagic\distrib).

Execute the following line in command prompt:

regsvr32 <xxx>.dll

where xxx is the dll (eg: xlmagic_1_0.dll)

Execute all the 3 steps listed above to call a function from VBA.