关于XLL加载项动态加载、卸载的演示及XLL函数自定义类型注册的演示

1、在XLL中，把函数定义成不同的类型，在Excel中的实际效果也不同，具体如下：

pxMacroType value                                                           0     1      2
  Can be called from a worksheet                                      Yes    Yes      No
  Can be called from a macro sheet                                   Yes    Yes     Yes
  Can be called from a defined name definition                    Yes    Yes     Yes
  Can be called from a conditional format expression           Yes    Yes   No
  Listed in the Function Wizard for worksheet functions        No    Yes   No
  Listed in the Function Wizard for macro sheet functions     No    Yes  Yes

 

注册函数需要使用Excel4的xlfRegister参数，其参数说明如下：

pxModuleText XLL名字，可以通过xlGetName获取
pxProcedure  推荐使用字符串，表示在XLL 代码中的函数名
pxTypeText   表示函数参数的类型和函数返回值的类型，字符串
pxFunctionText 函数名，在Excel中显示的名称
pxArgumentText 参数描述字符串
pxMacroType    函数类型： 0，1，2
pxCategory      函数类别
pxShortcutText  命令（类型2）的快捷键，"A" assigns this command to CONTROL+SHIFT+A
pxHelpTopic     帮助的引用字符串
pxFunctionHelp  函数向导中的描述
pxArgumentHelp1 函数参数1描述，后面依次类推

为了直观看到不同函数类型的效果，我用ExcelDna做了个演示文件，在文件中实现了XLL的加载、卸载；函数的注册、反注册等功能。

由于ExcelDna的限制，直接使用xlfRegister更改函数类型，会造成返回值的错误。因此，例子中的MySub函数返回值无意义。

                           工作表函数向导                                                                       宏表函数向导

 2、XLL可以通过另一个XLL进行加载和卸载，在Excel开发里面，这是最方便的升级方式。把俺的DNATool.XLL放到同一目录下，通过加载项里面的菜单可以加载。注意名字必须完全一致，俺的代码只认识这个名字 :)

     

 

 

文件下载   TestXllFunction

 

有什么建议欢迎联系QQ：564955427

 

 ************************************************

Conditional Formatting Formulas Must Have References - If you have a conditional formatting formula that references a UDF defined in another workbook such as an add-in, then you must add a Reference to that workbook before you can reliably use the functions defined in it.  If you don't, you will (sometimes) get the error 'This type of reference cannot be used in a Conditional Formatting Formula'. To add a reference, press Alt+F11 to go to the code editor, then go to Tools, References, and add the reference to this workbook by project name for the project containing the VBA code you need.

 

条件格式里面只能使用工作簿本身的公式，或者在VBE中引用的xla、xlsm等文件里面的公式。加载宏即使已经加载，但没有引用的话，依然不能在条件格式的公式里面使用。

 

 ************************************************

http://msdn.microsoft.com/en-us/library/bb687858.aspx   Accessing XLL Code in Excel

Calling XLL Functions Directly from Excel
Once they are registered, XLL worksheet and macro sheet functions can be called from anywhere a built-in function can be called from:
    A single-cell or array formula on a worksheet.
    A single-cell or array formula on a macro sheet.
    The definition of a defined name.
    The condition and limit fields in a conditional format dialog box.
    From another add-in via the C API function xlUDF.
    From Visual Basic for Applications (VBA) via the Application.Run method.

You can obtain a reference to the calling cell or range of cells within your function using the C API function xlfCaller. If the function was called from the cell’s conditional format expression, you are still returned a reference to the associated cell or cells, so you cannot assume that the cell’s formula contains the XLL function. If your function was called from a VBA user-defined function (UDF), xlfCaller again returns the address of the cells that called the VBA function. For more information, see xlfCaller.

 

Registering Functions and Commands with Excel
Registration tells Excel the following about a DLL entry point:
    Whether it is hidden or, if a function, whether it is visible in the Function Wizard.
    Whether it is callable only from an XLM macro sheet, or also from a worksheet.
    If a command, whether it is a worksheet function or a macro sheet equivalent function.
    What its XLL/DLL export name is, and what name you want Excel to use.
    If it is a function:
        What data types it returns and takes as arguments.
        Whether it returns its result by modifying an argument in place.
        Whether it is volatile.
        Whether it is thread safe (supported starting in Excel 2007).
        What text the Paste Function Wizard and AutoComplete editor should display to help with calling the function.
        Which function category it should be listed under.
This is all achieved using the C API function xlfRegister, equivalent to the XLM function REGISTER.

转载于:https://www.cnblogs.com/Charltsing/p/TestXllUDF.html