VBA Application.xlCalculationManual
Hello all
I know that setting calculation mode to xlManual will speed Excel VBA up. But are there any situations where it can cause incorrect results etc. Say I have a range of cells that I need to loop through twice (or more than once anyway). Once to test cell contents & then possibly change some cell values. Now I need to loop through the same range again to do something else after this first test. Can I have calculation set to xlManual for the whole routine or will this prevent the changes from the "first" loop being used in the second loop? Basically, I'm doing a "find & replace" first time through the loop, then looping through again to do something else based on the cell contents AFTER the find & replace. Does this create a problem or not? Hope I have explained myself correctly. Thanks Tony McGee |
VBA Application.xlCalculationManual
Here is an example.
Sub AABB() Range("A1").Formula = "=B1 + 5" Range("B1").Value = 10 Application.Calculation = xlManual s = Range("A1").Value Range("B1").Value = 20 s = s & "," & Range("A1").Value Debug.Print s Application.Calculation = xlAutomatic End Sub It returns 15,15 when I comment out the Calculation = xlManual Sub AABB() Range("A1").Formula = "=B1 + 5" Range("B1").Value = 10 'Application.Calculation = xlManual s = Range("A1").Value Range("B1").Value = 20 s = s & "," & Range("A1").Value Debug.Print s Application.Calculation = xlAutomatic End Sub it returns 15,25 So if you need to get the value of a cell that is the result of calculation, then it could have an adverse affect. -- Regards, Tom Ogilvy "Tony McGee" wrote in message ... Hello all I know that setting calculation mode to xlManual will speed Excel VBA up. But are there any situations where it can cause incorrect results etc. Say I have a range of cells that I need to loop through twice (or more than once anyway). Once to test cell contents & then possibly change some cell values. Now I need to loop through the same range again to do something else after this first test. Can I have calculation set to xlManual for the whole routine or will this prevent the changes from the "first" loop being used in the second loop? Basically, I'm doing a "find & replace" first time through the loop, then looping through again to do something else based on the cell contents AFTER the find & replace. Does this create a problem or not? Hope I have explained myself correctly. Thanks Tony McGee |
VBA Application.xlCalculationManual
As long as none of the cells in your range contain formulas (or are the
result of calculations) that depend on the cell values you changed - you should be okay. If you are dealing with hardcoded data, VBA will use the new values. Tom showed VBA did not recognize the new value of cell A1 (which was a calculation based on cell B1). But VBA should recognize the value of B1 was changed. To slightly modify his example: Sub AABB() Range("A1").Formula = "=B1 + 5" Range("B1").Value = 10 Application.Calculation = xlManual s = Range("B1").Value Range("B1").Value = 20 s = s & "," & Range("B1").Value Debug.Print s Application.Calculation = xlAutomatic End Sub will return 10, 20 "Tony McGee" wrote: Hello all I know that setting calculation mode to xlManual will speed Excel VBA up. But are there any situations where it can cause incorrect results etc. Say I have a range of cells that I need to loop through twice (or more than once anyway). Once to test cell contents & then possibly change some cell values. Now I need to loop through the same range again to do something else after this first test. Can I have calculation set to xlManual for the whole routine or will this prevent the changes from the "first" loop being used in the second loop? Basically, I'm doing a "find & replace" first time through the loop, then looping through again to do something else based on the cell contents AFTER the find & replace. Does this create a problem or not? Hope I have explained myself correctly. Thanks Tony McGee |
All times are GMT +1. The time now is 11:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com