Can someone explain why the use of REPLACE in VBA is lightening fast
relative to normal looping? The marked disparity in the speed of
execution of the following 2 codes highlights the point.


Sub ReplaceFunction()
Range("A1: BQ5000").Replace, "ALPHA", "BETA", xlWhole
End sub

Sub ReplaceViaLoop()
Dim c as Range

For each c in Range("a1:bq5000")
if c.value="ALPHA" then
c,value ="BETA"
end if
next

End Sub

Surely the underlyng code of the REPLACE function must be attempting
some looping, whatever the language used (VB6?). I don't wish to
believe that VBA is intrincically slower than VB6.

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=572341

In the VBA Loop, the process checks one cell at the time, causing the slower
operation time. The REPLACE option locates every instance simultaneously and
makes the replacements all at the same time.
--
Best wishes,

Jim


"Myles" wrote:


Can someone explain why the use of REPLACE in VBA is lightening fast
relative to normal looping? The marked disparity in the speed of
execution of the following 2 codes highlights the point.


Sub ReplaceFunction()
Range("A1: BQ5000").Replace, "ALPHA", "BETA", xlWhole
End sub

Sub ReplaceViaLoop()
Dim c as Range

For each c in Range("a1:bq5000")
if c.value="ALPHA" then
c,value ="BETA"
end if
next

End Sub

Surely the underlyng code of the REPLACE function must be attempting
some looping, whatever the language used (VB6?). I don't wish to
believe that VBA is intrincically slower than VB6.

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=572341

Jim wrote:

The REPLACE option locates every instance simultaneously and
makes the replacements all at the same time.

Jim,

It would seem to me that this is a lofty statement indeed. Is it reall
conceivable for a code to touch on each and every cell in a rang
SIMULTANEOUSLY, in real time one might add. Surely, if the REPLAC
algorithm does not "loop", it must be doing some form of scanning. Bu
then "loop" or "scan", is that not a semantic hood.


myle

--
Myle
-----------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874
View this thread: http://www.excelforum.com/showthread.php?threadid=57234