IntroductionThis 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
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
Rant 1: Logical Inconsistency!
Rant 2: It's a 3 Dimensional World, Mr Gates!
- Create a Name in a brand new 3 sheet workbook
- Call it "TopLeft"
- Define it as "=Sheet1!A1+Sheet2!A1+Sheet3!A1"
Set rng = Names("TopLeft").RefersToRange
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?
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),