Using XLM with Range Names and Conditional Formatting to automatically format spreadsheets according to cell content

Introduction

This Article is a follow-up to my Mappit! Addin Article, it was inspired by an email posting I made to EUSPRIG,

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:
3) A non-VBA approach that could be used to identify inconsistent formulae.

XLM in Excel

Excel 4 macros (XLM for short) was the initial Excel macro language, later superseded by the introduction of VBA in Excel 5.0 in 1993. Currently there is still support for legacy XLM macros in all Excel versions including Excel 2010 (Version 14), although it should be noted that there is no guarantee from Microsoft with respect to future XLM support.

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.

Using Conditional Formatting with XLM Range Names to automatically highlight cell types

The Excel Experts E-Letter is a series of newsletters produced by David Hager between 1999-2001. It is an absolute treasure trove for the budding Excel power user that I highly recommend. This Article uses concepts from Issues 3 and 8 - well worth looking if you have read this far. I first looked at applying these techniques to automatically format spreadsheets back in 2002 but it was impractical at this time as Excel supported only 3 conditional formats (ignoring workarounds) until Excel 2007 (Version 12).

All the screenshots that follow apply to  Excel 2010.

Formatting Cell Content Type

The figure below shows the intended cell automatic formatting

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 formula of each cell available for testing as to whether the cell may link to other closed workbooks (will contain a ".xls") or potentially other worksheets (the cell formula will contain a "!").
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

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("!",GlobalCellRef))),ISERROR(FIND("INDIRECT",GlobalCellRef)))

Detecting Inconsistent Cell Formula

There are at least two VBA methods to identify inconsistent cell formulae:
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 Excel must be run in R1C1 notation

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:
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.xlsm IsFormula.xlsm
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you found the article helpful please click on the "YES" button after the question below.
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.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Useful Links

Expert Excel E-letter
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