CODEWITHSUNDEEP
formulasspreadsheetgear
Martin_W
Martin_W

Reputation: 1797

In SpreadsheetGear, how do I check for formula or calculation errors?

I'm using SpreadsheetGear to copy a range from on worksheet into an "input" range of another worksheet and the manually recalculating the WorkbookSet. Is there a way to easily check if there are any formula or calculation errors after recalculating?

The destination workbook is complex with many formulas spanning the worksheets that depend on the copied, so a simple global way to locate any calculation errors would be helpful.

Upvotes: 1

Views: 1757

Answers (2)

Csaba Toth
Csaba Toth

Reputation: 10729

I have a few advice for such a transfer.

  1. Somewhat off-topic, but set workbookSet.Calculation = SpreadsheetGear.Calculation.Manual; and trigger calculation explicitly manually whenever you need with workbookSet.Calculate();
  2. @Tim Andersen's answer shows how you access IValue. From there you can read the Text or Number property to read out the right type of value
  3. You can say SetText or SetNumnber if you want to set.
  4. If the cell is empty, the IValue will be simply null!

The Advanced interface is different than the regular, I haven't figured out how to address cells by range names. Looks like the interface doesn't allow that, you can index cell by 0-based row-column indexes.

Upvotes: 0

Tim Andersen
Tim Andersen

Reputation: 3184

By "error" I presume you mean an Excel formula error value, such as #NUM!, #VALUE, #NAME?, etc? Unfortunately, there is no property you can check to determine whether a workbook contains one or more of these after a recalculation. About the only way to do this would be to manually iterate through any relevant cells in the workbook yourself and check for a ValueType of Error. For a large and complex workbook, I understand this might not be feasible.

Regardless, for such an approach my best suggestion would be, if possible, to limit this error checking to a smaller range or set of ranges that you are interested in. Secondly, you might consider using the "high performance" API under the SpreadsheetGear.Advanced.Cells namespace to squeak out a little better performance for this routine, versus using the traditional IRange interface, which would likely perform a bit slower. Below is an example that demonstrates this sort of approach. Feel free to modify as needed for your requirements:

using SpreadsheetGear.Advanced.Cells;
...
private bool RangeContainsErrors(IRange range)
{
    // We obtain a "high performance" IValues object by casting a worksheet
    // to IValues.
    IValues values = (IValues)range.Worksheet;

    // Setup some indexes to help clarify and shorten the for loops.
    int startRow = range.Row;
    int startCol = range.Column;
    int endRow = startRow + range.RowCount;
    int endCol = startCol + range.ColumnCount;

    // Loop through rows of desired range.
    for (int row = startRow; row < endRow; row++)
    {
        // Now loop through columns of desired range.
        for (int col = startCol; col < endCol; col++)
        {
            // Get an IValue object for this cell.
            IValue val = values[row, col];

            // Check to ensure this cell even as a value and if so, check
            // for an Error value type.
            if(val != null && 
               val.Type == SpreadsheetGear.Advanced.Cells.ValueType.Error)
            {
                return true;
            }
        }
    }

    // If we made it this far there weren't any errors.
    return false;
}

Upvotes: 1

Related Questions