Updating formulas via VBA
Arkana0
Posted messages
1980
Registration date
Status
Moderator
Last intervention
-
Arkana0 Posted messages 1980 Registration date Status Moderator Last intervention -
Arkana0 Posted messages 1980 Registration date Status Moderator Last intervention -
Hello,
I am working with a huge table in Excel 2007
It consists of two sheets, which I will call A and B.
Sheet A is filled with formulas pulling from Sheet B over nearly 1000 rows and a good dozen columns.
Naturally, with automatic refresh, applying a filter takes a considerable amount of time (we must be approaching 5 seconds). I have therefore set it to manual refresh.
However, to avoid mistakes, I was wondering if it was possible to code something in VBA so that the formulas automatically refresh when I make a change on Sheet B and return to Sheet A.
Thank you in advance for your advice, and no, I cannot lighten the table more than its current state ;)
I am working with a huge table in Excel 2007
It consists of two sheets, which I will call A and B.
Sheet A is filled with formulas pulling from Sheet B over nearly 1000 rows and a good dozen columns.
Naturally, with automatic refresh, applying a filter takes a considerable amount of time (we must be approaching 5 seconds). I have therefore set it to manual refresh.
However, to avoid mistakes, I was wondering if it was possible to code something in VBA so that the formulas automatically refresh when I make a change on Sheet B and return to Sheet A.
Thank you in advance for your advice, and no, I cannot lighten the table more than its current state ;)
1 answer
Hello,
I assume that what you call "automatic update" is automatic calculation mode.
If that's the case, you just need to stay in manual mode and trigger the calculation when activating sheet A
--
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
I assume that what you call "automatic update" is automatic calculation mode.
If that's the case, you just need to stay in manual mode and trigger the calculation when activating sheet A
Private Sub Worksheet_Activate() Calculate End Sub
--
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
Yes, that's exactly what it's about: the formulas that update automatically or upon pressing F9.
However, I've just seen that switching to manual calculation wasn't such a good option: it applies to all Excel files... Actually, I would like this "manual" calculation to happen only on this sheet A.
Next, regarding your code: it only covers part of what I'm asking. I don't want the calculations to update every time I go to my sheet A (that would be a first improvement, though). I want them to update on my sheet A only if I've modified a value on my sheet B beforehand.
Maybe we should implement a flag system with a global variable?
In this case, in ThisWorkbook, on Open, you retrieve the calculation mode to return it on close and set it to manual.
In Change of your sheet B, you set your flag as a global variable.
By activating sheet A, you check if the flag is set.