Before using the cell’s formula string, you should always test if Excel considers it a formula. Sounds silly, but I’ve seen a lot of examples about manipulating Excel cell formulas where this simple rule is not considered.

As a matter of fact, Excel considers the formula in a cell *a formula* when the **first** character in the formula string is the equal sign “=”.

To be sure you are dealing with a formula, **Do**:

- test for
**Cell.HasFormula** (True there is one, False there is none), or
- test if the first character in the formula string:
**VBA.Left$(Cell.Formula, 1) = “=”**

but **Do Not**:

- test for
**VBA.Len(Cell.HasFormula) > 0**

Why? Simply testing if the length of the formula is greater than zero is not enough. In fact, when reading the formula string from a cell without a formula, Excel does not return an empty string -as one could expect- but a stringified value of the cell’s contents, in other words, it does indeed return non-zero length strings even when no formula is present.

In summary, test if you are in the presence of a formula (Cell.HasFormula is the quickest) before using the formula string.

Dutch

### Like this:

Like Loading...

*Related*

## Leave a Reply