Can anyone explain to me why built in Cells.Find() is faster than a loop?

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

When coding in VB, why are the built in applications, particularly
Cells.Find(), faster than loops?
 
If you had to check 64k or 1MB cells in a column for a certain string that
appears 3 times, .find would be lots faster.

If you that string appears in every cell, then I bet the loop would be just as
fast or faster (not tested, though).
 
Part of the reason is because they're fully compiled and generally
pretty well optimized. VB code is interpreted at runtime, which is
usually slower.
 
Hi Will,

There is a significant overhead in transferring data from Excel to VBA, but
when you are dealing with methods like .Find that operate directly on Excel
ranges then the data does not need to be transferred. so you avoid the
overhead.
Also Excel's methods such as FIND are written in C language rather than VBA,
which tends to be faster in execution.

(Even faster than Cells.Find is using WorksheetFunction.Match)

When coding in VBA there are generally many different ways of coding the
same task, and some are very much faster than others.

(Come to my session on writing fast User-defined Functions at the UK User
Group Conference to find out more!)

Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html
 
Back
Top