Dynamic array formulas

[JanMarvin]

For quite some time now Excel provides dynamic array formulas in MS365. With the upcoming release of Office 2024, these functions will probably appear more and more.

In Excel the cell range is selected dynamically, so the =SORT() function will use as many cells as required. When writing with openxlsx2 this range has to be selected manually. Below an example with openxlsx2 release 1.5:

library(openxlsx2)

wb <- wb_workbook()$
  add_worksheet()$
  add_data(x = c("a", "b", "c"), dims = "A1:C1")$
  add_formula(dims = wb_dims(from_dims = "B3", x = 1:3), x = "_xlfn.TOCOL(A1:C1)", cm = TRUE)
#> Warning in write_data2(wb = wb, sheet = sheet, data = x, name = name, colNames
#> = colNames, : modifications with cm formulas are experimental. use at own risk

if (interactive()) wb$open()