Excel: array constants

An array constant is a list of elements within curly braces. The elements are separated by either a comma or a semicolon. A comma indicates that the element is in the same row but one cell to the right of the previous element while a semicolon starts a new row.

Comma vs Semicolon

The following VBA example tries to demonstrate the difference between commas and semicolons and how they can be used to create one and two-dimensional arrays.

option explicit

sub main() ' {

    activeSheet.usedRange.clearContents

  '
  ' One dimensional horizontal array constant
  ' Note: the elements are separated by commas, not semicolons.
  '
    range(cells(1,2), cells(1, 6)).formulaArray = "={ ""H1"", ""H2"", ""H3"", ""H4"", ""H5"" }"

  '
  ' One dimensional vertical array constant
  ' Note: the elements are separated by semicolons, not commas.
  '
    range(cells(2,1), cells(6, 1)).formulaArray = "={ ""V1""; ""V2""; ""V3""; ""V4""; ""V5"" }"

  '
  ' Two dimensional array constant
  ' Note: rows are separated by semicolons, other elements by commas.
  '
    range(cells(3,3), cells(5,5)).formulaArray = "={ ""3/3"",""4/3"",""5/3"" ; ""3/4"",""4/4"",""5/4"" ; ""3/5"",""4/5"",""5/5"" }"

end sub ' }

Github repository about-Excel, path: /formulas/array/constants/comma-semicolon.bas

when run, this code produces:

Naming an array constant

option explicit

sub main() ' {

 '
 '  Clear active sheet's data:
 '
    activeSheet.usedRange.clearContents

 '
 '  Name an array constant:
 '
    activeWorkbook.names.add "days", refersTo := "={""Sun"", ""Mon"", ""Tue"", ""Wed"", ""Thu"", ""Fri"", ""Sat""}"

 '
 '  Insert an array formula referring to the named constant:
 '
    range(cells(1,1), cells(1,7)).formulaArray = "=days"

end sub ' }

Github repository about-Excel, path: /formulas/array/constants/naming.bas

Excel: array constants

Comma vs Semicolon

Naming an array constant

See also