Avoiding COM exceptions

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

Avoiding COM exceptions
 
Andrei Smolin


Add-in Express team


Posts: 14096
Joined: 2006-05-11
Alas, I don't see any other solution.

Have a great weekend.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 May, 2017 10:25:12 Top
Andrei Smolin


Add-in Express team


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


OliverM writes: The changed code delivers a correct result but for a single scenario only.
Andrei Smolin writes: 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


Here's the link: https://social.msdn.microsoft.com/Forums/en-US/614501e2-0cb2-44ed-886b-cca5eac34179/bug-in-excel-2010-2013-and-2016-incorrect-rangeprecedents-and-rangedirectprecedents?forum=exceldev

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 16 May, 2017 10:21:36 Top
Andrei Smolin


Add-in Express team


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

They provided a workaround: they suggest using foreach insteadof for! This works for me:

private void ScanPrecedents(Excel.Range r) {
    System.Diagnostics.Debug.WriteLine("!!! " + r.Address);
    if (( bool )r.HasFormula == true) {
        foreach (Excel.Range rng in r.DirectPrecedents) { 
            ScanPrecedents(rng);
        }
    } 
}


Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 17 May, 2017 04:05:27 Top
OliverM




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

Using a foreach loop was my initial approach. As the method is called recursively it will definitely fail with a COMException, as mentioned in my 1st post, as soon as a cell contains a formula which points to preceding cell not being on the same sheet or in the same book.

As a result of our discussion we both came independently to the same conclusion. If you want to do a drill down on any given cell having a formula, the best way is to parse the formula. This allows you to get ALL preceding cells WITHOUT getting any exception.

To me, this thread is a perfect example why I am a happy Add-In Express customer.
- My query has been taken seriously (Query is specific to Excel, not to Add-In Express)
- Counterpart knowledge level is mine or better
- Discussion leads to new ideas
- New ideas lead to new feature 'BAR', product 'FOO' would happily like to add
- While writing this post 'BAR' has passed 80+% of unit tests
Kind regards
Oliver
Posted 17 May, 2017 11:05:14 Top
Andrei Smolin


Add-in Express team


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

Thank you very much.

So, I test this code:

private void ScanPrecedents(Excel.Range r) {
    System.Diagnostics.Debug.WriteLine("!!! " + r.Address);
    if (( bool )r.HasFormula == true) {
        foreach (Excel.Range rng in r.DirectPrecedents) {
            ScanPrecedents(rng);
        }
    }
}


I can't reproduce your original issue when the range being scanned is the C1 cell; see below:

Sheet 1:
A1 - 1
B1 - 2
C1 - =A1+B1+Sheet2!A2 (result is 13)
Sheet 2:
A2 - 10

It appears that r.DirectDependents (r.Dependents) contain only A1 and B1; Sheet2!A2 isn't mentioned at all. I've noticed that the description of Range.DirectPrecedents Property at https://msdn.microsoft.com/en-us/library/office/ff839667.aspx contains a remark: The DirectPrecedents property only works on the active sheet and can not trace remote references. This remark is missing at https://msdn.microsoft.com/en-us/library/office/ff196936.aspx (Range.Precedents Property). Still, this doesn't seem to relate to the issue as r.DirectPrecedents.Count = r.Precedents.Count = 2 and r.DirectPrecedents.Areas.Count = r.Precedents.Areas.Count = 1.

Then at https://colinlegg.wordpress.com/2014/01/14/vba-determine-all-precedent-cells-a-nice-example-of-recursion/, I've found an explanation and possible solution.

The explanation is as follows:

The output in the immediate window tells us it works with this simple example but what this example and the help file donít tell us is that it doesnít return precedents on other sheets or other workbooks. This restriction is enforced by the definition of the Range.Precedents property because it returns a Range object and Range objects canít reference ranges across different worksheets.


I confirm this: I can't construct a range which is a union of two ranges located on different sheets. I use ExcelApp.Union(). This works if the source ranges are located on the same sheet.

The solution they propose is to use the Range.ShowPrecedents() method to show the navigation arrows and then call the Range.NavigateArrow() method for each of the arrows.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 18 May, 2017 07:17:07 Top
OliverM




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

In the above scenario no exception will be thrown and my original issue can't be reproduced. This is because C1 contains 2 preceding cells on the same sheet. Unfortunately the 3rd reference pointing to Sheet2!A2 is completely ignored. To reproduce the issue change the formula in C1 to =Sheet2!A2. Now you will end up with a proper COMException when trying to access the DirectPrecedents object.

As you pointed out, the fact that a range can not be constructed from source ranges located on different sheets is the technical explanation.

The suggested approach found on many websites, to first show the navigation arrows and iterate through them is poor for the following reasons:

- Awful performance
- Does not work for protected sheets
- Does not work for hidden sheets
- Does not work for referenced cells in different workbooks

Either way you choose, you're between devil and deep blue sea.

Parsing the formula does not have any of the above mentioned disadvantages and it performs very well.

In a test scenario where A1 contains a formula which utilizes a mix of 25 preceding cells, the parser approach took 1 millisecond to correctly collect all cells wherever they were located and create a hierarchical tree with A1 as root node.

For the same scenario Excel took 0.7 sec just to show the arrows (having screen update disabled).
Kind regards
Oliver
Posted 29 May, 2017 08:07:55 Top
Andrei Smolin


Add-in Express team


Posts: 14096
Joined: 2006-05-11
Thank you, Oliver! Great explanation!

Good luck with your project.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 29 May, 2017 08:23:28 Top