firlandsfarm wrote: ↑Wed Nov 20, 2019 6:28 pm
I've had a look at that exchange on MrExcel and frankly it seems to conclude 'don't use EnableEvents'! He comments "You need to be careful with this if your macros depend on the values of formulas AND your code changes the values of the cells those formulas use." and the questioner concludes "Since my macros often include a need for these things to happen, it's probably best if I not use these two features." … mine likewise (and I would have thought most wb tasks) have values that rely on values so if I turn off calculating by whichever means the value that triggers the bet is not changed! I have turned off automatic updating on large ws in the past but mainly for data input when I haven't wanted an update after every cell entry. What actually happens when the Calculate in turned back on again?
Excel is quite efficient firlands so only recalculates a sheet if it needs to. So once BA sends the data to excel, excel will calculate all your formulas and amend them on the sheet, that will then trigger worksheet_change or calculate as appropriate. If our code is re-writing data back to the excel sheet this could obviously case further formulas on the sheet to change re-triggering another worksheet_calculate or if it writes data back to the sheet another worksheet_change so that would be why we turn off events during the period our code runs, once we turn it back on at the end of our code excel has already done any new calculations as they occur i.e when the code puts then back to the sheet which if coded right would occur before we turn events on again
So
Worksheet_Change -
kicked off because a change happens on the sheet
Application.EnableEvents = False -
we now disable events so it doesn't get retriggered if we change the sheet with our code
Run our code now
If our code writes to cell A5 which triggers an excel IF statement. Excel would process this now
If our code now writes to cell B5 which triggers another excel IF statement. Excel would process this now it won't store them up
Our code has finished writing to the sheet and excel has finished any calculations as they occur as is, we can now safely set events to true for the next refresh and end our sub
Application.EnableEvents = True
End Sub
Because excel will react to each change on the sheet we can turn off calculations with
Application.Calculation = xlCalculationManual this will then stop excel continually recalculating every time we write a cell to the sheet and if we haven't been too clever with our coding that may be many times. Once we turn calculations back on excel will recalculate all formulas in one go so we need to have that put before we enable events again if using worksheet_calculate
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
The most efficient way to run your bots is to grab your dataset into an array, do any adjustments to the array in memory , then dump that array back to the sheet. VBA runs much quicker in memory and the data is written back in one lot rather than cells being changed one at a time. Hopefully that makes some sense regarding EnableEvents but like everything the devil is in the details so you need to tailor your code to what you're actually doing. I don't use Worksheet_Calculate or turn calculations off with Application.Calculation because everything is done in VBA so no need for it for me.
I suddenly started to get a Bet Status of "ERROR" on one of the pair of ws for no apparent reason. All worked fine on Monday but having done a little 'tweaking' yesterday morning the problem occurred! Clearly something didn't like being tweaked. 
