Dutch Gemini's Weblog Pages

August 31, 2009

Excel’s cell formula is not always a formula

Filed under: Excel,Programming,VBA — dutchgemini @ 2:40 pm

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: