Avoiding COM exceptions

Add-in Express™ Support Service
That's what is more important than anything else

Avoiding COM exceptions
 
OliverM




Posts: 144
Joined: 2015-08-13
I am using a recursive loop to collect all preceding cells for a given base cell holding a formula.
While traversing down the tree it can happen that one of the preceding cells does not utilize other cells (e.g it does not have a formula or the cell it refers to is on a different sheet or in a different workbook).
In such a case a COMException is thrown

{System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException: Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
   --- End of inner exception stack trace ---
   at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.Dynamic.IDispatchComObject.GetMembers(IEnumerable`1 names)}


I never know how wide a tree of preceding cells will spread and how many times the COMException is thrown when starting to examine the base cell. Is there a way to avoid the expensive overhead of throwing exceptions?
Kind regards
Oliver
Posted 11 May, 2017 04:28:47 Top
Andrei Smolin


Add-in Express team


Posts: 14140
Joined: 2006-05-11
Hello Oliver,

If a client computer has the English version of Excel, and the locale for the current user is configured for another language (e.g. German or French), Excel may fire the "Old format or invalid type library" exception with error code 0x80028018 (-2147647512). The error usually occurs when the COM add-in code attempts to execute a method or get / set some property.


Is this your case? If so, please check https://www.add-in-express.com/creating-addins-blog/2009/02/13/old-format-invalid-type-library/

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 May, 2017 04:54:59 Top
OliverM




Posts: 144
Joined: 2015-08-13
Hi Andrei,

I know about this issue and and always set CultureInfo accordingly. I think the stack is a bit misleading as this exception finally translates to "No cells were found" (which is correct if cell does not have a formula assigned). Also Interop and VBA behavior is equal except that VBA translates the COMException to a 1004 runtime error.

What I am looking for is a way to apply a TryGet pattern when checking for preceding cells in order to avoid the cost of the exception if there is none. Is there a way to achieve this using reflection?
Kind regards
Oliver
Posted 11 May, 2017 05:30:29 Top
Andrei Smolin


Add-in Express team


Posts: 14140
Joined: 2006-05-11
Oliver,

OliverM writes:
System.Dynamic.IDispatchComObject.GetMembers(IEnumerable`1 names)}


Do you use foreach?

The code below doesn't produce the issue for me:

private void adxRibbonButton1_OnClick(object sender, IRibbonControl control, bool pressed) {
    Excel.Range r  = ExcelApp.Selection as Excel.Range;
    MessageBox.Show("Formula count = " + ScanCells(r).ToString());
}

private int ScanCells(Excel.Range r) {
    int result = 0;
    System.Diagnostics.Debug.WriteLine("!!! " + r.Address);
    if (( bool )r.HasFormula == true) {
        result = 1;
        Excel.Range directPrecedents = r.DirectPrecedents;
        for (int i = 1; i <= r.Count; i++) {
            Excel.Range subRange = directPrecedents[i] as Excel.Range;
            result += ScanCells(subRange);                    
        }
        return result;
    } else
        return 0;
}


Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 May, 2017 08:12:07 Top
OliverM




Posts: 144
Joined: 2015-08-13
Your code sample does not reproduce the issue because it is not doing what it is supposed to do ;-)
I'll come back on this tomorrow.
Kind regards
Oliver
Posted 11 May, 2017 12:46:00 Top
OliverM




Posts: 144
Joined: 2015-08-13
Andrei,

Consider the following test scenario:
A1=B1+C1+D1 (where B1,C1 and D1 contain values only). The expected result is 3 preceding cells B1,C1,D1.
Your code returns 1 but it should be 3
Changing
for (int i = 1; i <= r.Count; i++) 

to
for (int i = 1; i <= directPrecedents.Count; i++)

only seems to fix the issue. The changed code delivers a correct result but for a single scenario only.
Preceding cells got to be lined up on the same axis (A1=B1+C1+D1 or A1=B1+B2+B3)
Try changing the formula to A1=B1+C1+D2. The code returns B1,C1 and B2 as preceding cells.
Try changing the formula to A1=B10+C1+P25. The code returns B10,B11 and B12 as preceding cells.
Kind regards
Oliver
Posted 12 May, 2017 04:40:10 Top
Andrei Smolin


Add-in Express team


Posts: 14140
Joined: 2006-05-11
Hello Oliver,

Ah, sorry for the mistake.

OliverM writes:
The changed code delivers a correct result but for a single scenario only.


Really nice. I reproduce the issue using VBA. Well, I'm going to write about it on the Excel for Developer forum. I'll provide a link. Not sure whether this will help.

Mmm, where do we stand with your original issue?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 May, 2017 06:57:33 Top
OliverM




Posts: 144
Joined: 2015-08-13
Lets take another example which better illustrates the issue.
Le's assume the formula on Sheet1 in cell A1 is =Sheet2!B2
The HasFormula property for cell A1 would return true but when trying to get the DirectPrecedents we end up with

at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.get_DirectPrecedents()
at Precedents.AddinModule.CollectPrecedents(Range source) 


Excel only knows about DirectPrecedents which are defined as preceding cells on the same sheet but Excel happily ignores IndirectPrecedents which are preceding cells on any other sheet whether in the same book or even worse in a different book.

If one could test whether or not the ForwardCallToInvokeMember will succeed all the exceptions caused by IndirectPrecedents could be avoided.
Kind regards
Oliver
Posted 12 May, 2017 08:11:23 Top
Andrei Smolin


Add-in Express team


Posts: 14140
Joined: 2006-05-11
You can scan (parse) Range.Formula: such a formula must have this format [Book2]Sheet2!B2.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 May, 2017 09:45:35 Top
OliverM




Posts: 144
Joined: 2015-08-13
Yep, I am currently working on an implementation based on a formula parser. This approach would make the use of the DirectPrecedents object redundant (as we have learned, it does not deliver reliable results anyway).
The unfortunate task of creating patterns covering all possible scenarios for linked cells in open foreign books, closed foreign books, cells with clean sheet names, cells with clumsy sheet names is the challenge here.
Kind regards
Oliver
Posted 12 May, 2017 10:01:29 Top