PropertyPut and PropertyGet

The simplest example of a Microsoft Excel OLE Automation Controller is a DLL function that takes an object as an argument, obtains a DISPID for the object's Value property, and then uses PropertyPut or PropertyGet to set or get the value of the object. This simple example does not need the CoCreateInstance and QueryInterface functions, because the function can call the IDispatch interface directly with the object reference.

The CalcCells function is an example of a simple OLE Automation Controller. The function accepts a Range object as its first argument and a variant as its second argument. The function uses the PropertyGet method to obtain the value of the range; if the range contains more than one cell, the value is returned as an array. The function iterates the array, adding the value from each cell to a total (it attempts to coerce any values that are not doubles). Once the total is obtained, the function places the result in the second argument; if the argument specifies another range, the function uses the PropertyPut method to set the range value.

SCODE WINAPI CalcCells(LPDISPATCH *ppdsSourceRange, 
    VARIANTARG *pvtResult)
{
    HRESULT hr;
    EXCEPINFO excep;
    ULONG cElements, i;
    DISPPARAMS dispparams;
    unsigned int uiArgErr, cDims;
    DISPID dispidValue, dispidPut;     
    VARIANTARG vSource, vResult, vTemp, *pvdata;

    LPOLESTR lpszName = L"Value";

    hr = (*((*ppdsSourceRange)->lpVtbl->GetIDsOfNames))
            (*ppdsSourceRange, &IID_NULL, &lpszName, 
            1, LOCALE_SYSTEM_DEFAULT, &dispidValue);
    if (hr != NOERROR)
        goto calc_error; 

    // PropertyGet has no arguments

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

    // Invoke PropertyGet

    hr = (*((*ppdsSourceRange)->lpVtbl->Invoke))
            (*ppdsSourceRange, dispidValue, &IID_NULL, 
            LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYGET, 
            &dispparams, &vSource, &excep, &uiArgErr);
    if (hr != NOERROR)
        goto calc_error;

    // initialize the result variant

    VariantInit(&vResult);
    vResult.vt = VT_R8;
    vResult.dblVal = 0.0;

    // If there is more than one cell in the source range, 
    // it's a variant containing an array. 
    // Access this using the SafeArray functions

    if (vSource.vt & VT_ARRAY) 
    {
        // iterate the dimensions; number of elements is x*y*z
        for (cDims = 0, cElements = 1; 
                cDims < vSource.parray->cDims; cDims++)
            cElements *= vSource.parray->rgsabound[cDims].cElements;

        // get a pointer to the data
        hr = SafeArrayAccessData(vSource.parray, (LPVOID)&pvdata);
        if (hr != NOERROR)
            goto calc_error;

        // iterate the data. try to convert non-double values to double
    for (i = 0; i < cElements; i++) 
    {

        vTemp = pvdata[i];
            if (vTemp.vt != VT_R8) 
            {
                hr = VariantChangeType(&vTemp, 
                    &vTemp, 0, VT_R8);
                if (hr != NOERROR)
                    goto calc_error;
            }

            // add the data. this is where we could 
            // add a more complicated function
            vResult.dblVal += vTemp.dblVal;
        }

        SafeArrayUnaccessData(vSource.parray);
    }
    else 
    {
        // only one cell in the source range. 
        // if it's not a double, try to convert it.
        if (vSource.vt != VT_R8) 
        {
            hr = VariantChangeType(&vSource, &vSource, 0, VT_R8);
            if (hr != NOERROR)
                goto calc_error;
        }
        vResult = vSource;
    }

    // if the result value is an object, 
    // get the DISPID for its Value property

    if (pvtResult->vt == VT_DISPATCH) 
    {
        hr = (*(pvtResult->pdispVal->lpVtbl->GetIDsOfNames))
                (pvtResult->pdispVal, &IID_NULL, &lpszName,
                1, LOCALE_SYSTEM_DEFAULT, &dispidValue);
        if (hr != NOERROR)
            goto calc_error;

        dispidPut = DISPID_PROPERTYPUT;

        dispparams.rgdispidNamedArgs = &dispidPut;
        dispparams.rgvarg = &vResult;
        dispparams.cArgs = 1;
        dispparams.cNamedArgs = 1;

        // Invoke PropertyPut

        hr = (*(pvtResult->pdispVal->lpVtbl->Invoke))
                (pvtResult->pdispVal, dispidValue, &IID_NULL, 
                LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYPUT, 
                &dispparams, NULL, &excep, &uiArgErr);        
        if (hr != NOERROR)
            goto calc_error;
    }
    else 
    {
        // Result is not an object; it's a variable passed by reference.
        // Must free any existing allocation in the variant. 
        // The ReleaseVariant function is in dispargs.c

        ReleaseVariant(pvtResult);
        *pvtResult = vResult;
    }

    return 0;

calc_error:
    return GetScode(hr);    
}

This is a simple example, but it shows the setup for PropertyPut and PropertyGet and how the range value is returned as an array. You could write a more complex data-handling function around this simple example to implement a specialized DLL function. Remember that any variants your DLL function allocates (strings or arrays) must be freed to prevent memory leaks.

The code for this function is included on the samples disk in the SAMPLES\SDISP directory. This directory also includes the make file and module-definition file required to build SDISP.DLL. Once SDISP.DLL is available, you can call this function from Microsoft Excel, as shown in the following example:

Declare Function CalcCells Lib "SDISP.DLL" _
    (source As Range, result As Variant) As Integer

Sub Button1_Click()
    Worksheets(1).Activate
    Range("A4").Clear
    CalcCells Range("A1:B3"), Range("A4")
End Sub