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

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

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