Intersect Error: Are you kidding me?

Introduction

This is a rant.  There is useful information in it.  (Don't get me started on the inanity of the phrase "useless information"!)

Today I was working on a project.  Many of my projects go this way: Work on it a little each month as that reporting cycle comes around.  The code for this one step worked just fine.  All it did was check to see if the cell that was just changed was part of a named range
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Names("Ready_To_Publish").RefersToRange) Is Nothing Then
        If Names("Ready_To_Publish").RefersToRange.Value = True Then 
            ufPublish.Show
        End If
    End If
End Sub

As you can see I put the code in the Workbook_SheetChange event module.  It worked prior to this.  Today I saw something I had not seen before using the Intersect() function.

Trials and Errors

• Using RANGE() instead of NAMES().
• Being more specific 
• Using Target.Application.Intersect because this project included binding to another Office product.
• ThisWorkBook for Names() and Range().
• Assigned Names().RefersToRange to a Range
  Dim rng as Range
  Set rng = Names("Ready_To_Publish").RefersToRange

Research says...

A quick Google, so quick I saw the problem before I clicked on any provided result: The Intersect() function does not work if the ranges are not on the same worksheet.

Rant 1: Logical Inconsistency!

Of course if two or more ranges are not on the same worksheet they do not intersect.  When two ranges don't intersect the result is Nothing!  Duh!

Rant 2: It's a 3 Dimensional World, Mr Gates!

Yup..Worksheets/spreadsheets are two dimensional: rows and columns.  However, Mr Gates, once you put multiple worksheets into a workbook you've created a three-dimensional object.  

You can select multiple Worksheets and make simultaneous changes to them.  I've screwed up by doing this accidentally!

Excel allows us to create a named range that crosses Worksheet boundaries.  Try it.  

1. Create a Name in a brand new 3 sheet workbook
2. Call it "TopLeft"
3. Define it as "=Sheet1!A1+Sheet2!A1+Sheet3!A1"

You can do this programmatically as well.

Names.Add("TopLeft", "=Sheet1!A1+Sheet2!A1+Sheet3!A1")

But you can't do this...

Dim rng as Range
Set rng = Names("TopLeft").RefersToRange

Unless you want this...

Even though this works fine...
Dim rng as Range
Dim nm as Name
nm = Names.Add("OneSheetOnly", "=Sheet1!A1")
Set rng = Names("OneSheetOnly").RefersToRange

There are 3-D ranges.  These work fine if you want the same cells for each worksheet, but the definition of a Range is one or more blocks of contiguous cells (an "Area" in the Range Object Model).  Logically, a 3-D Range should be definable as a different set of blocks of contiguous cells on different worksheets.

Rant 3: Finish the Job!

What to do

• Modify the Range object type to allow 3-D Ranges with different Area-ranges on each worksheet. 
• Make Intersect() check for common worksheets
• Allow Union() to create 3-D Ranges.
• Modify Union() to allow empty ranges.  Currently when using the Union() function one must first test that neither is Nothing. A Union of something with nothing should be something.
• Add a Subtract function to remove cells from a range

Why do it?

"It's the logical thing to do" is a fine argument for methodologists, philosophers, those with OCD, deep-thinking programmers/designers, and other ideologues.  In the end, "logic" isn't enough, unless that "logic" includes practical advantage, i.e. an improvement over what can currently be done.  Here are some thoughts.

As alluded to by calling the current state illogical and incomplete, finishing the job would inherently make programming simpler by eliminating illogical errors and repetition of code, e.g. checking if all Ranges <> Nothing before using Union() function.  Of course, efficiently using a developer's time isn't the end all and be all.  Simpler code begets less time in maintenance, where more effort is laid out already.

I had one project with Column header rows and Row header columns, as well as Row Total columns and Column Total Rows.  From worksheet to worksheet (and month to month) the endpoints of the headers could be different.  For the total rows and columns, they could not only have different end points for each, but different positions (i.e. columns/rows, respectively),  

The workaround was to create a Name scoped to each worksheet.  A Worksheet-scoped name has the form of <WorksheetName>!<Name>.  As a result, to clear, format or populate each of these commonly named Ranges, a "For Each...in Names" loop was necessary, testing whether each Name ended with the standard string.  It would much simpler to be able to create a Name that references ranges on multiple worksheets then execute a single  .Clear function/method.