Thursday, December 12, 2013

WIP: Add by Names

As I thought about the functions & subroutines to use Tags to control processing in VBA last night, I came to believe the same principles can be applied to a worksheet function that searches the Names collection.  

  • This would not interfere with other Naming of ranges because a range (cell or set of cells) can have multiple names.  For example,
    • A worksheet creator develops a worksheet then uses the Create Names using Labels function.
    • These Names are used in functions and VBA.
    • Names can then be added using the Names define function, even though they refer to the same ranges.  These names, following a standard 
  • The cell entry would look something like =SumNames(<identifier>,<position>,<MatchCase>,<scope>) 
  • <Identifier> would be a character string common to the Named ranges.  It could include reference to the labeling cell, i.e. the cell to the left.  For example, if the labeling cell were A2 and equaled "Total" the <Identifier> could be "A2" and the Names of the contributing cells/ranges would include "Total".  (See below for <Position> and using wildcards.
  • <Position> could be used to establish were the string would occur, e.g. as a suffix or prefix, or in the sames where someone might want to use names like "Data1", "Data2". Valid Values could be
    • Left, meaning the character string is the start of the Name
    • Right, meaning the character string is at the end of the Name
    • Leftx and Rightx, where "x" signifies the number of characters before (Leftx)or after (Rightx). 
  • An alternative to the <Position> parameter would be to use wildcards , e.g. "?" for a single character, "*" for unknown multiple, etc.
  • <MatchCase> True/False.  Default True.
  • <Scope> would signify whether the search through the Names collection should select the Names local to the current worksheet, workbook-level Names, Names local to other worksheets in the workbook, and combinations of these.  
    • Valid values might be 
      • "All", including all Names, whether scoped to the workbook or a specific worksheet
      • "Workbook", excluding all worksheet scoped Names
      • "Worksheet", those scoped to this worksheet
      • "WorksheetAndWorkbook", eliminating Names scoped to other worksheets
    • Default: "Worksheet"
    • Alternatively, this could be multiple comma-separated values, similar to the Sum() function in which the names of specific worksheets could be used as well as "All", "Workbook", and "Worksheet"
The same principles could be used for any other function where the order of the elements is not important, e.g. multiplying or performing statistical analysis (e.g. Mean, Mode, Standard Deviation).