I am told that the Excel object model permits a Range that is not a part of any sheet, yet contains a set of cells and is denoted by a name in the workbook.
Can anyone explain to me how these fit into the Excel object model and how one would go about creating such a thing programatically (either in VBA or .NET source code).
Thanks.
That would be a named range. You can reference a selection of cells, and just type a name where it says 'A1' next to the formula bar. That creates a named range that doesn't change.
Alternatively you can create a named range that is based on a formula, and therefore potentially changes as data in the spreadsheet changes. You do this from the 'Define Name' option (which is in the Formulas Ribbon in Office 2010).
Named ranges can be accessed from VBA, and (I'm pretty sure) from .net.
So you'd access the named range from vba like so:
Range["MyNamedRange"]
Your question is a little vague, but I'll give it a shot.
Well, as Dave describes, you can give a specific range of cells on a sheet a "Range Name" which you can then refer to programatically, but that doesn't sound like what you are asking.
It sounds like you are asking "is there an abstract RANGE of cells available to be used by VBA code that doesn't literally exist on any worksheet?" The answer to this is no, even named ranges are simply a convenient reference to a real set of cells on a real worksheet.
You can, however, programatically hide a worksheet so that the user doesn't see it, and still work with cells and ranges on that sheet. Just do:
Sheets("Sheet1").Visible = xlSheetHidden
Sheets("Sheet2").Visible = xlSheetVeryHidden
Sheets("Sheet3").Visible = xlSheetVisible
What's "VeryHidden", you ask? It means that the user can't go to Format, Sheet, Unhide and make the sheet visible.
So if I'm correctly understanding what you want, just programatically hide one of the sheets, then use Dave's technique to create a named reference to a range on this hidden (or VeryHidden) sheet.