在Matlab中使用Excel的用户定义函数时出错[复制]

Possible Duplicate:
Excel Addin Error #NAME?

可能重复：Excel Addin错误#NAME？

I think it is a follow-up query to my earlier reported issue concerning User defined function in Excel.

我认为这是对我之前报道的有关Excel中用户定义函数的问题的后续查询。

I am able to use the function in Excel when used Manually, but when I write to an excel file using Matlab using xlswrite, it gives an error #NAME?

我可以在手动使用Excel中使用该功能，但是当我使用xlswrite使用Matlab写入excel文件时，它会出现错误#NAME？

I am attaching the screenshots of the both when entered manually and when using the function through Matlab.

我在手动输入和通过Matlab使用该功能时附加了两者的屏幕截图。

Thanks

谢谢

EDIT :

编辑：

Thanks a lot. I have stored the VBA function as an Excel addin here :

非常感谢。我已将VBA函数存储为Excel插件：

C:\Users\Administrator\AppData\Roaming\Microsoft\Addins

C：\用户\管理\应用程序数据\漫游\微软\加载项

Here is what I saw about Excel Add-ins not loaded when used in Automation :

以下是我在自动化中使用时未加载的Excel加载项的内容：

http://www.excelforum.com/excel-programming/472145-calling-excel-macro-from-vb-6-app-problem.html

http://www.excelforum.com/excel-programming/472145-calling-excel-macro-from-vb-6-app-problem.html

I am attaching the small snippet of the code from chi_squared() here :

我在这里附加chi_squared（）的代码的小片段：

Function Chi_Squared(act, exp, Optional df)

This is how I write to an excel file in Matlab :

这是我在Matlab中写入excel文件的方式：

Formula_chisqr={[ '=chi_squared(' 'O2:O22' ',' 'M2:22' ')']};

Formula_chisqr = {['= chi_squared（''O2：O22''，''M2：22''）']};

[status, message] = xlswrite1(ExcelFilename,Formula_chisqr,sheetname, Location_Agg);

[status，message] = xlswrite1（ExcelFilename，Formula_chisqr，sheetname，Location_Agg）;

I also tried giving the complete path as suggested. But it did not work.

我也尝试按照建议提供完整的路径。但它没有用。

Thanks

谢谢

2 个解决方案

#1

You have to specify the workbook where the UDF defined. Even if your function is it your PERSONAL.XLSB file. You don't have to do it only if UDF is defined in the same file where you use it.

您必须指定UDF定义的工作簿。即使你的功能是你的PERSONAL.XLSB文件。只有在使用UDF的同一文件中定义UDF时，才必须执行此操作。

For example,

例如，

='myFunctions.xlsb'!chi_squired(O2:O21,P2:P21)

If myFunctions.xlsb is not opened you may need to specify full path to the file.

如果未打开myFunctions.xlsb，则可能需要指定文件的完整路径。

If you want to be able to call UDF in any file without specifying the file name, you need to save the file as Add-in, and then enable it in Options - Add-ins - Manage Add-ins.

如果您希望能够在任何文件中调用UDF而不指定文件名，则需要将文件另存为加载项，然后在“选项” - “加载项” - “管理加载项”中启用它。

---

Another idea: When you use XLSWRITE provide file name with extension XLSX, like test.xlsx, not just test. By default MATLAB saves files with XLS extention in older format. It looks like when you open such file in newer version of Excel (2007/2010) the compatibility mode does not allow macros or UDFs to run.

另一个想法：当你使用XLSWRITE提供扩展名为XLSX的文件名时，就像test.xlsx一样，而不仅仅是测试。默认情况下，MATLAB以旧格式保存具有XLS扩展名的文件。看起来当您在较新版本的Excel（2007/2010）中打开此类文件时，兼容模式不允许运行宏或UDF。

#2

When you open the Excel as a COM server, the UDF's and Add-ins are not loaded by default. So, you need to first load the add-ins if you would like to use the add-in your Excel file.

当您将Excel作为COM服务器打开时，默认情况下不会加载UDF和加载项。因此，如果要使用Excel文件的加载项，则需要先加载加载项。

Here is the small code snippet in Matlab which loads the add-ins before opening the Excel file.

这是Matlab中的小代码片段，它在打开Excel文件之前加载加载项。

Excel = actxserver ('Excel.Application'); 
Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension'); 
Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1); 

File='C:\YourFileFolder\FileName'; 
if ~exist(File,'file') 
    ExcelWorkbook = Excel.Workbooks.Add; 
    ExcelWorkbook.SaveAs(File,1); 
    ExcelWorkbook.Close(false); 

end 
Excel.Workbooks.Open(File); 

Source : Mathworks

资料来源：Mathworks