Passing an Excel Range to C++ RRS feed

  • Question

  • Hi all,

    I'm an absolute newbie at COM programming.  I've been getting by reading articles off Microsoft's website and tutorials on the web.  But my Amazon books won't be coming until next week.      In the meantime, I have a question that perhaps some kind soul can answer.

    I have a VBA function that passes a pointer to ActiveSheet and two Excel ranges to a C++ function contained in a DLL.   The VBA function is:

    Code Block

    Private Declare Function runTestMLE Lib _
        "Office Automation 2.dll" ( _
        ByVal wks As Worksheet, ByVal rngVars As Variant, ByVal rngObjF As Variant) As Long

    Private Sub butSolve_Click()
        Dim retVal As Long
        retVal = runTestMLE( ActiveSheet, Range(txtVars.Value), Range(txtObjF.Value) )
    End Sub

    I need to pass the ranges themselves, not their contained values, because the C++ function will change their values and write the new values back into the corresponding cells on the Worksheet where they came from.   I understand that to C++, a Range is a VARIANT with a vt of VT_DISPATCH.

    On the C++ side, this is what my function, so far, looks like:

    Code Block

    int __stdcall runTestMLE( IDispatch *wks, VARIANT vRngVars, VARIANT vRngObjF )
        IDispatch *app = applicationFromWorksheet( wks );

        // Objective function sanity checks.
        isRangeVariant( vRngObjF, "Third argument (vRngObjF) is not a Range VARIANT." );
        hasOneElement( vRngObjF, "The obj function range should only contain one cell." );

        // Variables array sanity checks.
        isRangeVariant( vRngVars, "Second argument (vRngVars) is not a Range VARIANT." );
        hasMoreThanZeroElements( vRngVars, "Vars range should have more than zero cells." );

        if(! vRngVars.parray ) {
            ::MessageBox(NULL, "vRngVars->parray was NULL.", "Error", 0x10010);
            return (4);

        // Determine the array type
        HRESULT lResult;
        VARTYPE vt_type;
        lResult = SafeArrayGetVartype(vRngVars.parray, &vt_type);


    The first sign of trouble is where the text is red.  The return value for SafeArrayGetVartype() is E_INVALIDARG.   Why would the argument be invalid?   The type looks correct, and I know that vRngVars is a valid VARIANT.

    But more importantly, I'm not really sure how to convert vRngVars into an array.    The ultimate goal is to:

    1. Read a range (containing nothing but doubles) off an Excel spreadsheet into a C++ function as an array of doubles.
    2. Stochastically change some of the array element's values in the C++ function.
    3. Write the array back to where the range originally came from on the Excel spreadsheet.
    4. Repeat many times.

    Efficiency is somewhat important here because the C++ function reads / changes / writes  the Excel ranges thousands of times.  So I'd like to know the most efficient way of doing this.

    Thanks so much!
    Monday, November 12, 2007 12:59 AM