This project has moved. For the latest updates, please go here.

ISERROR and handling Excel formulae

Oct 14, 2013 at 1:10 PM
Hello there,

First off thanks for a fantastic wrapper for OpenXML it has made my life much easier as the application I develop for is not natively .NET and doesn't support a lot of the necessary functions to make OpenXML work, and the supplied OpenXML wrapper from Microsoft for the application is woeful.

The problem I have is that on occasion our users (external, I have no direct influence on them unfortunately) will occasionally not enter in all of their required data into the Excel workbook resulting in formulae evaluating to #DIV/0 or other error values.

In the old world of Automations what I used to do when reading the Excel worksheet is set a field to =ISERROR(cellReference) and evaluate the result, allowing me to display a message to the user along the lines of "Cell A1 formula is evaluating to an invalid value, please correct". Now if I try to use ISERROR I end up with a syntax error. Looking at the list of supported formula I think this is expected. Unfortunately if I try to call Cell.Value this results in an overflow and bombs the application.

I have tried using an inbuilt command ISNULL(Cell) before running anything but this does not work as the Cell reference is not actually null. I have tried using Cell.ValueCached but this returns an empty string regardless of whether I am on a valid Cell with a valid Value, a valid Cell with a valid Value evaluated from a formula, or on an invalid Cell with an invalid Formula.

Is there any way to workaround this issue? If I have to release the code with an obscure error message it's not too much of a problem but I'd prefer to give the users some better information to save my support team some call investigation time!

Thanks and regards,
Dan