question

DangDKhanh-2637 avatar image
0 Votes"
DangDKhanh-2637 asked DangDKhanh-2637 commented

Excel Check hidden property very slow?

Hi,
I created a function call based on this automation tutorial.
My calling function is as follows:


 ----------------------------------------
 //get ws
 IDispatch *pXlSheet;
    {
       VARIANT result;
       VariantInit(&result);
       AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
       pXlSheet = result.pdispVal;
    }


 //get hidden
 VARIANT result;
 AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRow, L"Hidden", 0);
    
 ------------------------------------------------
 for (int i = 1; i <= 100.000; i++)
 {
   IDispatch *row= getrow(pXlRows,i);           //get dispatch row
   Bool b = gethidden(row)                       //get row hidden property
   vector[t++] = b;
       
 }

then compare with:

 Public Function test(rng As Range) As Variant
     With ActiveSheet
         k = rng.Row
         rc = k + rng.Rows.Count - 1
         Dim a() As Integer
         ReDim a(1 To rc, 1 To 1)
         For i = k To rc
             a(i - k + 1, 1) = IIf(.Rows(i).Hidden, 1, 0)
         Next
     End With
     test= a
 End Function

I tested 100k rows and the result from the first method take me 5s and using vba run in 2s on Excel365 64bit, Win64bit core-I5

unbelievable, Is it still slower than vba? (I mean when working with the IDispatch*)

Thank you!



















c++
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

RLWA32-6355 avatar image
0 Votes"
RLWA32-6355 answered DangDKhanh-2637 commented

VBA code executes within the Excel process. Since it executes in-process there is no marshaling required for data or interfaces and the VBA code accesses Excel directly.. Automating Excel from a C++ application means that Excel is an out-of-process server. Consequently, the COM runtime is needed to marshal interfaces, method calls and data between the C++ application and Excel. This is much less efficient than accessing a COM Server in-process.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Thank you for the explanation.
This is really sad news for me now :(

0 Votes 0 ·