O'Reilly logo

Excel 2003 VBA Programmer's Reference by Robert Rosenberg, Rob Bovey, John Green, Stephen Bullen, Paul T. Kimmel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

27

SmartTags

When something is typed into a worksheet cell, Excel tries to interpret that entry, converting it to something more meaningful than a random set of characters. This is achieved using rules similar to the following:

  • If the entry contains the characters 0–9, and potentially the local thousand and decimal separators, it's a number that can be used in calculations
  • If the entry contains the characters 0–9 and the local date separator, it might be date that can be used in calculations (and implies a specific cell format)
  • If the entry is #N/A, #Value! etc., it's an error value that should propagate through any calculations that refer to it.
  • If the entry starts with an = sign, it could be a formula to be evaluated or a number
  • Otherwise, it's a text string—an essentially random set of characters

In each case, Excel is recognizing the entry as a specific data type—a number, date, formula, etc.—and by doing so gives that entry a specific set of behaviors, actions, and properties (those behaviors and actions that are defined for entries of that type). For example, all entries of type “Formula” have the following behaviors:

  • They need to be checked for “syntax” errors, such as mismatched brackets, etc.
  • They need to be incorporated into Excel's calculation dependency tree
  • They need to be evaluated
  • The cell should show the result of the evaluation, not the text of the formula

Up until Excel 2002, the rules for recognizing entries, and the list of applicable behaviors and properties ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required