Excel的xll加载宏的开发（转）

徒然客

原文：

http://hi.baidu.com/scyangyu/blo ... 7c0d47ac34de1f.html

    一直以来，对于用VBA开发的Excel的xla加载宏的不足感觉不满意，想采用xll加载宏的方式来替换。但是却没有找到比较好的资料来学习如何进行开发。最近在网上收集到了一些相关的资料，这下可以突破了。
   
    参照David Bolton的“Writing Excel Addons”(http://www.aspfree.com/c/a/Windo ... iting-Excel-Addons/)，用Delphi顺利的完成了第一版的WASPCN.xll。
   
    但是还有2个问题未能解决：
   
    (1)各个函数的描述和各参数的描述未能加上。
   
    (2)加载宏卸载后，函数并未能从函数向导列表中清除。

    经测试，发现生成的xll在Office 2000、Office 2002(XP)、Office 2003和Office 2007中均能正常加载。

徒然客

由于用Delphi开发的xll中各个函数的描述和各参数的描述未能加上。进而又参考JChampion的“Sample Excel Function Add-in in C”(http://www.codeproject.com/macro/InterpolationAddin.asp)，在VS2005中完成了C语言的WASPCN.xll第1版。
   
    这次比较顺利地把xll中各个函数的描述和各参数的描述加上。但是，加载宏卸载后，函数仍然未能从函数向导列表中清除。

    经测试，发现生成的xll在Office 2003和Office 2007中能正常加载，但是在Office 2000和Office 2002(XP)中出现加载错误。

徒然客

2007-11-16 18:21    参考C语言版本，发现了Delphi语言版本各个函数的描述和各参数的描述未能加上的原因在于，
   
    EResult := Excel4V(xlfregister,@res,12,   // 准备传递给register函数的参数个数12

    经过修改后，顺利地把各个函数的描述和各参数的描述加上。
   
--------------------------------------------------------------------------------------

Function Register_UDF2Args(Const UDFName,UDFTypeText,UDFFunctionText,UDFArgumentText,
   UDFCategory,UDFDescr,UDFDescrArgs1,UDFDescrArgs2:ShortString;Const UDFMacrotype:Integer):integer;
var
S:Shortstring;
Begin
Res.xltype := xltypeerr;
Res.val.err := xlerrvalue;
S:=GetName;
pxModuleText.SetStr(S);                                // Xll名
pxProcedure.SetStr(UDFName);                    // 函数名
pxTypeText.SetStr(UDFTypeText);              // 返回类型
pxFunctionText.SetStr(UDFFunctionText);     // 函数向导中的函数名称
pxArgumentText.SetStr(UDFArgumentText); // 函数的参数
pxCategory.SetStr(UDFCategory);                // 函数向导中的函数分类名
pxMacrotype.SetNum(UDFMacrotype);        // 函数向导中的函数注册类型            1 = 可见, 0 = 隐藏
pxDescr.SetStr(UDFDescr);                        // 函数的描述
pxDescrArgs1.SetStr(UDFDescrArgs1);       // 函数的参数1的描述
pxDescrArgs2.SetStr(UDFDescrArgs2);     // 函数的参数2的描述


//    function title, argument types, function name, arg names,
//        type (1=func,2=cmd),
//        group name (func wizard), Hot Key, help ID,
//        function description,
//        (repeat) description of each argument

EResult := Excel4V(xlfregister,
                     @res,
                     12,                                                //准备传递给register函数的参数个数
                     [pxModuletext.thelpxloper,             // 模块名(DLL的名称)
                      pxProcedure.thelpxloper,               // 函数名
                      pxTypeText.thelpxloper,                // 返回类型+参数类型
                      pxFunctionText.thelpxloper,         // 函数名
                      pxArgumentText.thelpxloper,         // 函数的参数
                      pxMacroType.thelpxloper,            // 宏类型
                      pxCategory.thelpxloper,                // 分类名称
                      zlpxloper,                                      // 快捷键(Hot Key)
                      zlpxloper,                                     // help ID
                      pxDescr.thelpxloper,                   // 函数的描述
                      pxDescrArgs1.thelpxloper,           // 函数的参数1的描述
                      pxDescrArgs2.thelpxloper            // 函数的参数2的描述
                     ]
                    );
asm pop sink; End;                       // Never Remove
Result := trunc(res.val.num);            // ID Code (4)
End;

徒然客

2007-11-16 18:22    加载宏卸载后，函数并未能从函数向导列表中清除的问题，参考Stephen Bullen的“Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA” Chapter 19. XLLs and the C API，得到解决。
   
    关键在于：
    由于Excel's C API的BUG，不能直接采用
   
        Excel4(xlfUnregister, 0, 1, &xlRegID);
        
    而需要采用以下3步式。
    (1) 更改函数定义数组中的函数向导中的函数注册类型(macro_type)为0，即隐藏。
    (2) 重新用xlfRegister把各个函数注册成隐藏方式。
    (3) 然后再用xlfUnregister把各个函数注销。   

            // Unregister each function.
            // Due to a bug in Excel's C API this is a 3-step
            // process. Thanks to Laurent Longre for discovering
            // the workaround described here.
            // Step 1: Redefine each custom worksheet function
            // as a hidden function (change the macro_type
            // argument to 0).
            xlRegArgs[4].val.str = "\0010";
            // Step 2: Re-register each function as a hidden
            // function.
            // NOTE: The number of xlRegArgs[] arguments passed
            // here must be equal to NUM_REGISTER_ARGS - 1.
            Excel4(xlfRegister, 0, NUM_REGISTER_ARGS + 1,
                &xlXLLName,
                &xlRegArgs[0], &xlRegArgs[1], &xlRegArgs[2],
                &xlRegArgs[3], &xlRegArgs[4], &xlRegArgs[5],
                &xlRegArgs[6], &xlRegArgs[7], &xlRegArgs[8],
                &xlRegArgs[9], &xlRegArgs[10]);
            // Step 3: Unregister the now hidden function.
            // Get the Register ID for the function.
            // Since xlfRegisterId will return a non-pointer
            // type to the xlRegID XLOPER, we do not need to
            // call xlFree on it.
            Excel4(xlfRegisterId, &xlRegID, 2, &xlXLLName,
                                                &xlRegArgs[0]);
            // Unregister the function using its Register ID.
            Excel4(xlfUnregister, 0, 1, &xlRegID);

徒然客

一直以为VS2005开发的xll有什么问题，以致于生成的xll在在其他某些机器上出现加载错误。
   
   后来发现用VS2005编译时，选择的运行时库的选项是：多线程 DLL (/MD)，造成对MSVC2008.dll的依赖，只有存在MSVC2008.dll的机器，才能加载xll成功。

   正确的做法是：
   
   修改“C++/代码生成”中的“运行时库”选项，将 多线程 DLL (/MD) 改为 多线程(/MT)，这样就实现了对MSVC2008.dll运行时库的静态链接，在运行时就不再需要MSVC2008.dll了。

Excel的xll加载宏的开发(6)：给xll添加版本信息和描述信息2007-11-16 18:25    (1)在VS 2005的解决方案资源管理器中，选择“Resource Files”，通过右键菜单，“添加”-“新建项”，选择“资源”-“资源文件(.rc)”,输入资源文件名"waspcn.rc"。

    (2)在资源视图中选择刚刚建立的资源文件“waspcn.rc”，通过右键菜单，“添加”-“添加资源”，选择“Version”。

   3)在VS_VERSION_INFO中的对应各个条目，填写对应的信息。

    另外，可以采用如下的方法更改加载宏在加载管理器中的描述。(不过对xla加载宏有效，对xll加载宏无效。通过对\\Microsoft Office\\OFFICE11\\Library\\Analysis\\ANALYS32.XLL进行分析，其实现的方式的确不是如此，需要在程序中设置。)



--------------------------------------------------------------------------------


Add a Description to the Add-In

It is a good idea to add a description to the Add-In itself. This description will be displayed in the Add-Ins dialog box when you choose an Add-In to install.

First, use?the file manager to locate your Add-In file. Right-click on the file icon and choose Properties from the context menu. In the file properties dialog click the Summary tab. Type a description of your Add-In in the Comments: text box. If you wish you can also type a name for your Add-In in the Title: text box. This is useful if you have chosen a short or cryptic name for your *.xla file but would like to show a more descriptive name in the Add-Ins dialog. Finally click [OK] to accept your changes.

徒然客

2007-11-16 18:26    原本的准备把用户接口(菜单、工具条、定制窗口等)在xll中实现，后来发现这样也不是很方便，最后就决定采用xla+xll混合方式。用户接口用xla，核心计算用xll。office自带的ANALYSIS TOOLPAK也是这种方式(ATPVBACS.XLA+ANALYS32.XLL)。
   
    xla当然是在Excel中用VBA来实现。在xla创建菜单、工具条、定制窗口都非常的容易。但设计与xll混合使用是，就涉及两个关键的问题：
   
    (1)xllL和xla的加载问题
   
    当然应是先加载xll，然后再加载xla。这需要加载两个加载宏。如果只加载了xla会怎么样？可能就会出错。最好能让xla自动判断，如果已加载过xll则好，否则在VBA中动态加载xll。
   
    Dim WASPCNPath As String
   
Sub auto_open()
      VerifyOpen                                \'验证xll是否注册，如果没有注册就注册它
End Sub

\'验证xll是否注册，如果没有注册就注册它
Private Sub VerifyOpen()
      Dim XLLName As String, Quote As String
      Dim theArray As Variant
      Dim i As Integer
      Dim XLLFound As Boolean
      
      XLLName = "WASPCN.XLL"          \'XLL文件名称
      \'Application.RegisteredFunctions 属性返回包含所有已登记函数列表的数组
      theArray = Application.RegisteredFunctions
      If Not (IsNull(theArray)) Then
          For i = LBound(theArray) To UBound(theArray)
              If (InStr(theArray(i, 1), XLLName)) Then          \'列1 动态链接库或代码资源的名称
                  Exit Sub                                      \'需要配套的xll已经注册或加载
              End If
          Next i
      End If
      
      Quote = String(1, 34)
      WASPCNPath = ThisWorkbook.Path
      
      WASPCNPath = WASPCNPath & "\\"
      XLLFound = Application.RegisterXLL(WASPCNPath & XLLName) \'载入 XLL 代码源，并自动登记该代码源中包含的函数和命令。
      If (XLLFound) Then                                          \'如果指定代码源载入成功则本方法返回 True；否则本方法返回 False。
          Exit Sub
      End If
         
      WASPCNPath = "" & "\\"
      XLLFound = Application.RegisterXLL(WASPCNPath & XLLName) \'载入 XLL 代码源，并自动登记该代码源中包含的函数和命令。
      If (XLLFound) Then                                         \'如果指定代码源载入成功则本方法返回 True；否则本方法返回 False。
          Exit Sub
      End If
         
      WASPCNPath = "\\LIBRARY\\WASPCN" & "\\"
      XLLFound = Application.RegisterXLL(WASPCNPath & XLLName) \'载入 XLL 代码源，并自动登记该代码源中包含的函数和命令。
      If (XLLFound) Then                                         \'如果指定代码源载入成功则本方法返回 True；否则本方法返回 False。
          Exit Sub
      End If
      
      \'出错信息
      MsgBox ("Cannot find WASPCN.XLL")
      ThisWorkbook.Close (False)
End Sub
   
   
    (2)在xla如何调用xll中的函数
   
    可以采用
    Application.ExecuteExcel4Macro("WASPCN_GetStd(0)")
    或
    Evaluate("WASPCN_GetStd(0)")
    这两个命令。

徒然客

Excel的xll加载宏的开发(8)：XLL的内存管理2007-11-22 11:03XLL的内存管理

处理XLL的内存管理需要注意的有：

(1)Excel调用XLL中的函数，Excel负责分配和释放它传递的函数的各个参数。

(2)Excel调用XLL中的函数并返回一个简单类型的值的情况，如Double或Integer，这时Excel负责分配和释放这个传递参数。

(3)Excel调用XLL中的函数并返回一个xloper类型的值的情况，返回这种数据最简单的方法是分配一些静态内存并返回指向该内存的指针。

//C的语法
LPXLOPER WINAPI ReturnString(void)
{
static XLOPER x;
x.xltype=xltypeStr;
x.val.str="\004Test";
return (LPXLOPER)&x;
}

//Delphi的语法
Function ReturnString:LPXLOPER;stdcall;
{$J+}
Const x:XLOPER=(xltype:xltypeNil);
{$J-}
Begin
x.xltype:=xltypeStr;
x.val.str^:='Test';
Result:=@x;
End;

(4)如果XLL的函数中需要使用Excel4或Excel4v来调用Excel内置的函数和过程的情况。XLL中的函数将通知Excel调用Excel内置函数的返回值的存储地址。

    如果返回值是没有指针的简单XLOPER，调用者为返回值分配10字节内存，并将指向该XLOPER的指针作为第2个参数传递给Excel4或Excel4v即可。

    如果返回值是具有指针的复杂XLOPER，比如XLOPER包含一个指向内存的指针，而该内存是在调用过程中，由Excel从它自身的内存管理系统分配的。这就需要在使用了该复杂XLOPER后，及时通知Excel释放相应的内存，否则会造成内存泄漏。这通常是通过调用Excel的内置函数xlFree来完成。例如，如果XLOPER的xltype是xltypeStr(字符串)，调用xlFree释放的是该字符串，而不是XLOPER本身。

    安全准则是每次从Excel4或Excel4得到返回值时就调用xlFree。如果是不包含指针的XLOPER传递给xlFree，不执行任何操作。如果xlFree意外地两次调用同一个XLOPER，Excel将忽略第二次调用。

    不要修改从Excel返回的XLOPER。如果那样做，Excel内存管理系统可能无法正常释放内存甚至可能瘫痪。

(5)xlbitXLFree和xlbitDLLFree的区别

    xlbitXLFree用于通知Excel释放从Excel自身的内存管理系统分配的内存，该内存是XLL调用Excel内置函数而有Excel的内存管理系统分配的。
    Excel支持一个称为xlbitXLFree的特定位，可在任意返回到Excel的XLOPER的xltype域中打开该位。这个XLOPER是调用过Excel4或Excel4得到的返回值。如果XLL函数返回一个XLOPER，且它的xlbitXLFree是1，则Excel自动从XLOPER复制出它所需要的数据，并直接释放内存(从Excel自身的内存管理系统分配的)。
   
    xlbitDLLFree用于通知Excel调用xlAutoFree函数来释放XLL中分配的内存，该内存是XLL内的函数自己分配的。
    Excel支持一个称为xlbitDLLFree的特定位，可在任意返回到Excel的XLOPER的xltype域中打开它。使用它，可为使用自己分配内存的XLOPER分配任何额外的内存。这个XLOPER并非是调用过Excel4或Excel4得到的返回值，也就不存在从Excel自身的内存管理系统分配的内存要释放的问题。如果XLL函数返回一个XLOPER，且它的xlbitDLLFree是1，则Excel自动从XLOPER复制出它所需要的数据，然后以该XLOPER作为参数调用XLL中的xlAutoFree函数来释放XLL已分配的内存。

byl2005

这么好的东东支持一下 谢谢版主

hhzjxss

可惜没学过Delphi和VC，要是用VB的话，我倒可以学习一下！

LAOMALIN

学会它可以转行做程序了,不过还是得谢谢楼主的分享