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

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?

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.

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?

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.

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

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.

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.

This technology is now available for our custom development services only. Based on the Add-in Express for Office core, it is designed for building custom-tailored Office add-ins with far less coding than you usually have to do. Plus, it includes all Add-in Express features such as True RAD, visual designers, Outlook view and form regions, etc.

Get the best platform for building version-neutral, fast and easy deployable plug-ins by using Add-in Express projects templates, visual designers, components and wizards in combination with a perfect Delphi compiler.

This is an extension for Visual Studio that allows developers to quickly create WiX-based setup projects in a familiar Visual Studio way.

The Designer for WiX Toolset lets you forget the plain Windows Installer XML and concentrate on your deployment logic. It integrates several editors with the Visual Studio IDE and provides a set of vdproj designers to configure the file system, registry, user interface, custom actions, launch conditions and more for your setup projects.

The innovative technology for customizing Outlook views and forms. It is included in all Add-in Express for Office products and can be used to extend Outlook views, e-mail, task and appointment windows, To-Do bar, Reading and Navigation panes with your own custom sub-panes.

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in
the United States and/or other countries. All other trademarks are property of their respective owners.