When setting Values or Formulas (and the sisters of the Formulas property), according to the docs we're supposed to pass an array of Any[][]:
This means that code like this fails to compile in VSCode:
async function run() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const rng = sheet.getRange("A1:A10");
rng.formulas = "=B1";
await context.sync();
});
}
If you change the "=B1" to [["=B1"]], the code compiles but fails with a runtime error because the # of array elements doesn't match the # of rows of the range.
HOWEVER, the code shown above runs just fine in Script lab and produces the expected result where A1 gets the formula =B1, A2 gets =B2 , and etcetera.
Setting one formula to an entire range is a very frequent task in Excel automation and should be made as simple as it is in VBA, .NET and in Script lab.
I am asking for a change in the limitations imposed by the Office parts of VS Code (whatever those are called) that disallow setting a single value to the formulas and values of an entire range.
1 Comment
FYI: The code in my example runs successfully when ts-ignore is added:
async function run() { await Excel.run(async (context) => { const sheet = context.workbook.worksheets.getActiveWorksheet(); const rng = sheet.getRange("A1:A10"); //@ts-ignore rng.formulas = "=B1"; await context.sync(); }); }
