Forum Discussion
Excel Script - not calculating as expected
In the current file it uses VBA to do the hide/show bit. It uses formulas on the sheet to handle the logic for that, mainly because it was easier that way. So there's no date manipulation going on in the code. It works perfectly in VBA because Excel updates the formulas as soon as the month number cell is updated. The problem with the script is that it doesn't update the formulas until after the script has finished....by which time it has already done the hiding/displaying columns. But because the monthnumber cell has not been updated, the columns set to show are still relating to the last month.
I guess I'll have to do as you say and have the script determine the month....it's easy enough to do as each cell relates to a date and all I have to do is have it check that the month number equals the month of the date. My point was more in general as to why doesn't excel update things while the script is running....It has functionality to handle the calculation mode but it doesn't seem to be working in this case
Excel updates things. If you change some cells by the script, grid is recalculcating. Howver, if within script you get some values before recalculation, they'll be as they are. To get recalculated values you need to pick them up from recalculated range again. Small sample
function main(workbook: ExcelScript.Workbook) {
// Assume in A1 is 2, and in B1 = A1*3
const source = workbook.getActiveWorksheet().getRange("A1")
const target = workbook.getActiveWorksheet().getRange("B1")
const result = target.getValue()
console.log( result ) // returns 6
source.setValue( +source.getValue()+1 ) // Now in A1 is 3 and in B1 is 9 (3*3)
console.log( result ) // returns 6 (value is gotten before recalculation)
console.log( target.getValue() ) // return 9, result of recalculation
}