Excel Script - not calculating as expected

Question

Hi folks....I'm hoping somebody can shed a bit of light on what I'm doing wrong here. We have a simple holiday tracker - it has a small amount of VBA in it which I figured would be a good candidate to migrate over to an Office/Excel script so that it could work in the browser version of the application. The file has over 300 columns, a column for each day of the year. All the macro does is move the view forwards or backwards in the year by toggling the visibility of the column. Above each column, in a hidden row is a simple formula which results in either Show or Hide depending on the current month. It has 2 buttons, one to increease the month number and one to reduce it. The show/hide value updates based on this number. The VBA version works pretty well but given it's fairly basic, I assumed it would be a good cadidate for my learning in Excelscripts!

So after much effort I came up with the script below.....

function main(workbook: ExcelScript.Workbook) {

// Get the active cell and worksheet.

const CalcMode = workbook.getApplication().getCalculationMode();

console.log(CalcMode);

// TODO: Write code or use the Insert action button below.

let ws = workbook.getWorksheet("Refs");

let rng = ws.getRange("rngCurMonth");

let wsY = workbook.getWorksheet("Year");

let rngY = wsY.getRange("rngShowHide");

let rngYVals = rngY.getValues();

let colCount: number = rngY.getColumnCount();

let monthNum: number = rng.getValue();

wsY.getRange("B:NG").setColumnHidden(true);

if (monthNum > 1) {

monthNum = monthNum - 1;

rng.setValue(monthNum);

workbook.getApplication().calculate(ExcelScript.CalculationType.full);

monthNum = rng.getCell().getValue();

console.log(monthNum);

for (let coll = 0; coll <= colCount; coll++) {

//console.log( coll + " is " +rngYVals[0][coll]);

if (rngYVals[0][coll] == "Show") {

rngY.getColumn((coll)).setColumnHidden(false);

}

I have linked this script to a button. When it's clicked the value relating to the month number changes correctly. The script hides all the columns and then unhides the ones where the value is "Show". The problem is that the formula relating to this Show/Hide value only seems to update once the script has completed. It's not recalculating after the value is changed. I have added a line to try to force the recalc mid process, but it's not working. Any ideas? There are a few console.log lines in there which I need to take out, like the start where I'm checking that Excel is in automatic calc mode....which it is!! Also, after I set the monthNum variable, I have got the script to pull the value from the range holding the month number from the worksheet, to show that's it's updated correctly

I'm stumped. I find it difficult to learn these scripts partially because the editor is woeful!

Regards,

Tony

sergeibaklan · Answer

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
}
&nbsp;

m_tarler · Answer

guess i don't get why you need that hidden column.&nbsp; why not just do the calculation and show/hide columns in the script

tonyenglish · Answer

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

sergeibaklan · Answer

You get Show/Hide values at very beginning, later you change MonthNumber, Show/Hide within grid are recalculated, but in array for show/hide are still old values. You need to get it from the grid again, or get just befor you will use it.
Not sure I understood entire logic, thus tried to reproduce as
function main(workbook: ExcelScript.Workbook) {

const sheetRefs         = workbook.getWorksheet("Refs")
    const rangeCurrentMonth = sheetRefs.getRange("CurrentMonth")
    const sheetYear          = workbook.getWorksheet("Year")
    const rangeShowHide      = sheetYear.getRange("ShowHide")

let MonthNumber: number = +rangeCurrentMonth.getValue()

MonthNumber &gt; 1 ? MonthNumber -= 1 : ''
    rangeCurrentMonth.setValue(MonthNumber)
    sleep(0.1) // in general not necessary

const rangeShowHideValues = rangeShowHide.getValues()
    const colCount: number = rangeShowHideValues[0].length

for( let col of Array.from(Array(colCount).keys() ) ) {
        rangeShowHide
            .getColumn(col)
            .setColumnHidden( rangeShowHideValues[0][col] === "Show" ? false : true ) 
    }
}
// Delay in seconds
function sleep(seconds: number) {
    const waitUntil = new Date().getTime() + seconds * 1000
    while (new Date().getTime() &lt; waitUntil) { }
}
for the attached file. Not sure if scripts waits for the greed recalculation or not, added some delay. However, it works exactly the same without it.&nbsp;

Forum Discussion

Excel Script - not calculating as expected

4 Replies

Resources