Hi everyone,
I am having trouble in controlling the time needed to update my Excel 2010 file through VBA macro.
My VBA macro runs a constant block of instructions in order to copy new data from an external file and integrate with the existing data in my Excel file. My VBA macro briefly add formulas and values to cells, inserts, deletes, cuts, copies, pastes, hides, unhides, vlookups and links cells, columns and rows as well as it inserts and rename new sheets for new data, all on the fly.
Even though I make sure that I set Automatic Update to "Manual" and Screen Update to "Off", while the file gets larger due to new data added through my VBA macro, it takes longer for my VBA macro to complete the standard operations above. The exact amount of VBA code executed and new data added on each run stay unchanged; therefore, I would expect my macro run time should be constant as I have already switched off any likely additional time consuming operations due to the increased size of the Excel file.
In my macro, I merely run Calculate for specific range of cells, for those that I cannot let stay unchanged after new data retrieval.
I suspect, all or part of the operations listed above cause Excel to calculate the whole file.
How can I truely be sure that I switch off automatic calculation and prevent Excel from losing time while it runs my VBA macro. I want Excel just to accept new data, organize the file as per the VBA macro but not calculate anything -except for several cells I intentionally address to calculate thorough "Range.Calculate"?
Many thanks in advance.
I am having trouble in controlling the time needed to update my Excel 2010 file through VBA macro.
My VBA macro runs a constant block of instructions in order to copy new data from an external file and integrate with the existing data in my Excel file. My VBA macro briefly add formulas and values to cells, inserts, deletes, cuts, copies, pastes, hides, unhides, vlookups and links cells, columns and rows as well as it inserts and rename new sheets for new data, all on the fly.
Even though I make sure that I set Automatic Update to "Manual" and Screen Update to "Off", while the file gets larger due to new data added through my VBA macro, it takes longer for my VBA macro to complete the standard operations above. The exact amount of VBA code executed and new data added on each run stay unchanged; therefore, I would expect my macro run time should be constant as I have already switched off any likely additional time consuming operations due to the increased size of the Excel file.
In my macro, I merely run Calculate for specific range of cells, for those that I cannot let stay unchanged after new data retrieval.
I suspect, all or part of the operations listed above cause Excel to calculate the whole file.
How can I truely be sure that I switch off automatic calculation and prevent Excel from losing time while it runs my VBA macro. I want Excel just to accept new data, organize the file as per the VBA macro but not calculate anything -except for several cells I intentionally address to calculate thorough "Range.Calculate"?
Many thanks in advance.
