Search notes:

Excel Object Model: define a name for an array constant

This example tries to demonstrate how a name object can be created that refers to an array constant.

After such a name is created it can be used in worksheet functions such as index().

option explicit

sub main() ' {

    activeSheet.names.add "romanNumbers", refersTo := "={""I"", ""II"", ""III"", ""IV"", ""V"", ""VI"", ""VII"", ""VIII"", ""IX"", ""X"", ""XI"", ""XII""}"

    cells(2,2) =  7
    cells(3,2) =  5
    cells(4,2) = 11
    cells(5,2) =  3
    cells(6,2) =  6

    range(cells(2,3), cells(6,3)).formulaR1C1 = "= index(romanNumbers, RC[-1])"

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Name/array-constant.bas

Index