Search notes:

Excel Object Model: Name

A name object refers to

A named range
A constant array
… ?

Names can be defined in either a workbook or a worksheet.

Hence, they're stored in these object's respective names collection, for example the workbook.names property of a Workbook object.

Built-in names for ranges are (or include?):

database
print_area
auto_open

Methods and properties

`application`
`category`	Applicable only in Excel 4.0 macro sheets? See also `macroType`.
`categoryLocal`
`comment`
`creator`
`delete()`
`index`
`macroType`	A member of the `xlXLMMacroType` enumeration - ony used for Excel version 4 macro worksheets? See also `category`.
`name`
`nameLocal`
`parent`
`refersTo`	An equal sign followed by the referred formula, in the language of the macro and in A1-style notation.
`refersToLocal`
`refersToR1C1`
`refersToR1C1Local`
`refersToRange`	The `range` object that is referred to by the name object.
`shortcutKey`
`validWorkbookParameter`
`value`
`visible`	A `boolean`: if `false`, the name does not appear in the Define Name dialog box.
`workbookParameter`	A `boolean` that determines if the name object is a workbook parameter.

Create names and use them in function

The following example creates two names (fruits and numbers). The name fruit refers to a range, the name numbers to a (constant) array.

These ranges are then used in index() functions to get the ranges' third element.

option explicit

sub main() ' {

    dim nameFruits, nameNumbers as name

    cells(1,1) = "Apple"
    cells(2,1) = "Banana"
    cells(3,1) = "Coconut"
    cells(4,1) = "Durian"
    cells(5,1) = "Elderberry"

    set nameFruits  = names.add("fruits",  refersTo := range(cells(1,1), cells(5,1))        )
    set nameNumbers = names.add("numbers", refersTo := array("one", "two", "three", "four") )

    cells(2, 3).formula = "= ""Third element in fruits is ""  & index(fruits , 3)"
    cells(3, 3).formula = "= ""Third element in numbers is "" & index(numbers, 3)"

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Name/add-and-use-in-formula.bas

Excel Object Model: Name

Methods and properties

Create names and use them in function

See also