一个使用数组和范围的 Excel C++ DLL

• 下载演示 - 23.4 KB
• 下载源代码 - 3.98 KB

引言

本文展示了一种在 C++ 动态链接库中实现用户自定义函数 (UDF) 的简单方法；该函数将一个数组作为参数，并返回一个修改后的数组到 Excel。参数和返回的数组可以是 Range 对象或 VBA 数组（一个 SAFEARRAY），或者两者兼而有之。

背景

作为 Excel 的常用用户，我发现 VBA 编程环境对于扩展 Excel 内置函数的功能非常有用。然而，VBA 是一种解释型语言，这意味着它的指令是在运行源代码时运行或解释的。当运行涉及大量计算的过程或函数时，速度可能会成为一个限制因素。幸运的是，VBA 可以使用包含在 DLL 或 exe 文件中的其他语言的已编译代码。有大量关于构建可以从 VBA 调用的 DLL 的文档。然而，我发现很难找到关于如何构建或使用使用数组（从 VBA 到 VBA）的已编译代码的完整参考。Excel 电子表格中的数组定义为范围，而在 VBA 环境中，它们被操作为 SAFEARRAY。在这里，我提供了一个非常简单的函数，它从 Excel 中获取一个数组，检查它是否是一个 Range 对象或一个 SAFEARRAY，对其数值进行一些非常简单的修改，并将结果作为数组返回到 Excel，该数组可以作为电子表格函数输入或在 VBA 环境中使用。

使用代码

函数 CheckExcelArray 从 Excel 获取数组，无论是 Range 对象还是 VBA SAFEARRAY，并返回一个 VARIANT SAFEARRAY。如果该函数的参数是一个 Range 对象，则其值通过 IDispatch 接口传递给 VARIANT SAFEARRAY。

VARIANT _stdcall CheckExcelArray(VARIANT& ExcelArray)
{
    VARIANT dvout;
    switch(ExcelArray.vt)
    {
        case VT_DISPATCH:
        // if Array is a Range object expose its values through the 
        //IDispatch interface
        {
            EXCEPINFO excep;
            DISPPARAMS dispparams;
            unsigned int uiArgErr;
            DISPID dispidValue;
            LPOLESTR XName = L"Value";

            ExcelArray.pdispVal->GetIDsOfNames(IID_NULL, &XName,
                    1, LOCALE_SYSTEM_DEFAULT, &dispidValue);

            dispparams.cArgs = 0;
            dispparams.cNamedArgs = 0;

            // Invoke PropertyGet and store values in dvout
            ExcelArray.pdispVal->Invoke(dispidValue, IID_NULL,
               LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYGET, 
               &dispparams, &dvout, &excep, &uiArgErr);

            ExcelArray.pdispVal->Release();

            return dvout;
        }
        break;

        default:
        //if the Array is a VBA SAFEARRAY return it as such
            return ExcelArray;
            break;
    }
    VariantClear(&dvout);
    VariantClear(&ExcelArray);
}

一旦数组可用于处理，使用其元素进行的计算结果就可以作为 VARIANT SAFEARRAY 返回到 Excel。函数 SumOneToArray 执行最简单的计算，并将修改后的数组返回到 Excel。

VARIANT _stdcall SumOneToArray(VARIANT sourceArray)
{

    //check if Array is a Range object

    if(sourceArray.vt==VT_DISPATCH)
        sourceArray = CheckExcelArray(sourceArray);

    long ncols, nrows, i, j;

    //get the number columns and rows

    ncols=(sourceArray.parray)->rgsabound[0].cElements;
    nrows=(sourceArray.parray)->rgsabound[1].cElements;

    //dynamically allocate memory for an array to store values

    VARIANT *dArray = new VARIANT [nrows*ncols];

    for(i=0; i<nrows; i++)
    {
        for(j=0; j<ncols; j++)
        {
            long indi[] = {i+1,j+1};
            //store in a VARIANT array for other uses 

            SafeArrayGetElement(sourceArray.parray, indi, &dArray[(i*ncols)+j]);
        }
    }

    //create a VARIANT SAFEARRAY to return modified values

    VARIANT destArray;
    destArray.vt = VT_ARRAY | VT_VARIANT;

    SAFEARRAYBOUND sab[2];
    sab[0].lLbound = 1; sab[0].cElements = nrows;
    sab[1].lLbound = 1; sab[1].cElements = ncols;
    destArray.parray = SafeArrayCreate(VT_VARIANT, 2, sab);

    for(i=0; i<nrows; i++)
    {
        for(j=0; j<ncols; j++)
        {
            long indi[] = {i+1,j+1};

            //check for the type of VARIANT in the array

            switch(dArray[(i*ncols)+j].vt)
            {
                case VT_I2:
                    dArray[(i*ncols)+j].iVal=dArray[(i*ncols)+j].iVal + 1;
                    break;

                case VT_R8:
                    dArray[(i*ncols)+j].dblVal=dArray[(i*ncols)+j].dblVal + 1;
                    break;

                    //Hope the interested reader can perform more interesting operations!
                    //So here You need to add checking for the rest of VARIANT types
                    //there are plenty of them and You need to know what types your
                    //array may hold!

                    default:
                    break;
            }

            //Put the result in the array to be returned to Excel or the VBA env.
            SafeArrayPutElement(destArray.parray, indi, &dArray[(i*ncols)+j]);
        }
    }

    //free memory of the intermediate storage variable
    delete [] dArray;

    //return the modified array to SpreadSheet or VBA environment
    return destArray;

    //free memory of the intermediate VARIANTs

    VariantClear(&sourceArray);
    VariantClear(&destArray);
}

在 VBA 中声明该函数允许从电子表格和 VBA 编程环境中使用它。例如，通过选择单元格 B10:F14 并输入 <CTRL><SHIFT><ENTER> =SumOneToArray(B3:F7)，所选范围将填充一个数组，其中所有元素都比作为函数参数传递的数组大一个单位。这些函数在 VBA 中的声明非常直接。

Public Function CheckExcelArray Lib "ExcelArray" (ByRef x As Variant) As Variant
Public Function SumOneToArray Lib "ExcelArray.dll" (ByVal x As Variant) As Variant

以下代码显示了它在 VBA 编程环境中的使用

Sub UseSumOne()
    Dim Y(5, 1) As Variant
    Dim sum1 As Double, sum2 As Double
    For i = 1 To 5
        Y(i, 1) = i
    Next i
    sum1 = Application.Sum(Y)
    Debug.Print sum1
    sum2 = Application.Sum(SumOneToArray(Y))
    Debug.Print sum2
    Debug.Print sum2 - sum1
    'it should print
    ' 25
    ' 20 
    ' 5
    'in the debugging window

End Sub

要使用演示项目文件 "ExcelArray.xls"，您必须将其放置在与 "ExcelArray.dll" 相同的文件夹中。