Published on 2011-09-15

24,466 Points

I will briefly cover:

1) An overview of how XLM adds significant flexibility and further options to a modeller's toolkit.

2) A non-VBA technique that uses conditional formatting to immediately identify cells that are:

Numeric inputs (Constants)

Formulae

Formulae (resulting in text output)

Links to internal worksheets

Links to external Workbooks

3) A non-VBA approach that could be used to identify inconsistent formulae.While it isn't possible to use XLM directly in spreadsheet formulae, XLM can be deployed via Range Names, or by using the ExecuteExcel4Macro method in VBA. This Article makes use of the Range Name method, please see the Useful Links list at bottom for a link to John Walkenback's "A VBA Function To Get A Value From A Closed File" which is an excellent example of using ExecuteExcel4Macro.

One of the great advantages of XLM was that in prior years was that it provided the ability for advanced users to build relatively complex solutions without alarming users with the "Enable Macros" prompt triggered by VBA. This workaround is unfortunately - or fortunately depending on your viewpoint - obsolete as Microsoft has now built in security warnings to Excel (including older versions) that detect and flag XLM.

All the screenshots that follow apply to Excel 2010.

The following four Range Names underpin this formatting approach.

GlobalRef will be used to link each individual cells content to the formatting options.

GlobalCellRef makes the

GlobalIsText returns True where the cell value is text

IsFormula returns True where the cell contains a formula

The full set of range names are displayed below.

See Laurent Longre's site for A listing of the GET.CELL methods

In descending priority we can now apply the five Conditional Formatting options

IsBookLink =NOT(ISERROR(FIND(".xls",GlobalCellRef)))

IsSheetLink =NOT(ISERROR(FIND("!",GlobalCellRef)))

IsInput =AND(ISNUMBER(GlobalRef),NOT(IsFormula)) [ie, the entry is a numeric constant]

AND(IsFormula,GlobalIsText) [ie, is the cell a formula, and is the output text]

IsFormula =GET.CELL(48,GlobalRef)

A conditional formatting example is shown below.

Note that I did caveat the identification of workbook links and internal worksheet links with a "may", it is possible that false workbook or internal worksheet links will be indentified, the most likely culprit being a INDIRECT formula containing a "!". If this is an issue for your workbooks then the Conditional Formatting tests can be beefed up to exclude these false flags.

For example, using IsSheetLink =AND(NOT(ISERROR(FIND("!",

1) Using the SpecialCells Row_Differences and Column Differences

2) Comparing R1C1 values using ranges or variant arrays (Mappit! approach)

The XLM/Range Name/Conditional Formatting approach provides another method to highlight inconsistent formulae but with a health warning that this method is more cumbersome than the formatting cell content type example.

At this stage I will quickly explain at a high level how Mappit! processes formulae comparisons ...... which should make it easier to understand why this approach is harder to implement with conditional formatting given each user has to deploy the same steps manually.

Mappit! uses VBA to:

1 Identifies formulae ranges (for example from A2:B6, then B10:Z10), and then processes each formulae block,

2 Checks whether the range being examined is either at the top of the worksheet (no cells above), and/or at the far left (no cells to the left) and so on,

3 Checks whether there is a formula above and/or to the left of the formula being examined,

4 Checks whether the cell being examined is inconsistent with formulae that may exist above and to the left,

5 Distinguishes between a "starter" cell at the top left of a range and a cell that is inconsistent with a formulae above or to the immediate left.

To apply the same approach with XLM/Range Name/Conditional Formatting

As per the earlier example a list of the Range Names that will be used with Conditional Formatting is shown below.

There are seven possible formula evaluations

1) The formula does not have a cell with a formula to the immediate left, or a cell with a formula immediately above - no formatting

2) The formula is to the right of a cell that contains a formula

i) Green if consistent

ii) Blue if inconsistent

3) The formula is below a cell that contains a formula

i) Green if consistent

ii) Yellow if inconsistent

4) The formula is to right of a cell that contains a formula and below a cell that contains a formula

i) Green if consistent

ii) Grey at the left if inconsistent with the formula to the left, but consistent with the formula in the cell above

iii) Grey at the top if consistent with the formula to the left, but inconsistent with the formula in the cell above

iv) Dark gray if inconsistent with both the formulae in the cells to the left and above

Formulae that occur in the top row or leftmost column are not formatted as more complicated error handling has not been added in this example to cope with these two conditions (Step 2 in Mapiit!).

The six Conditional Formatting options applied to produce the six colourings are:

2(i),3(i) and 4(i) =AND(IsFormula,OR(AND(TopOnly,TopCell=ActiveCell),AND(LeftOnly,LeftCell=ActiveCell),AND(Both,TopCell=ActiveCell,LeftCell=ActiveCell)))

2(ii) =AND(IsFormula,LeftOnly,LeftCell<>ActiveCell)

3(ii) =AND(IsFormula,TopOnly,TopCell<>ActiveCell)

4(ii) =AND(IsFormula,Formula,Both,TopCell=ActiveCell,LeftCell<>ActiveCell)

4(iii) =AND(IsFormula,Both,TopCell<>ActiveCell,LeftCell=ActiveCell)

4(iv) =AND(IsFormula,Both,TopCell<>ActiveCell,LeftCell<>ActiveCell)

Note the IsFormula test that is present in all of these formatting options, if the cell doesn't contain a formula then this test prevents a Constant cell from being incorrectly coloured.An example screenshot of this result of applying these range names and conditional formatting to a block of cells containing formulae and text is shown below.

The Mappit! audit run on the same worksheet is shown here.

Sample ExcelFiles

CellsR1C1.xlsmIsFormula.xlsm

=-=-=-=-=-=-=-=-=-=-=-=-=-

If you found the article helpful please click on the

This utility has been helpful to me and I hope you find an opportunity to use it as well.

If you didn't or otherwise want to vote No, please leave a comment and give me an

opportunity to respond. It will give me a chance to improve this article and others I

would like to write.

If you liked this article and want to see more from this author, please click here.

=-=-=-=-=-=-=-=-=-=-=-=-=-

A listing of the GET.CELL methods

Migrating Excel 4 Macros to VBA

XL4 Macro Functions In Names

A VBA Function To Get A Value

Ask questions about what you read

If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.