PDA

View Full Version : [SOLVED] More efficient way to use WorksheetFunction in loop?



tpoynton
01-16-2006, 01:51 PM
Greetings,

I read johnske's article on how to improve the efficiency of VBA code, but am struggling with how to make this quickerhttp://vbaexpress.com/forum/images/smilies/banghead.gif.



For Each cell In dataRange
If cell.Value Like "*#*" Then
Cells(iRow, iColCount).Value = WorksheetFunction.PercentRank(dataRange, cell.Value) * 100
Else: Cells(iRow, iColCount).Value = ""
End If
iRow = iRow + 1
Next cell


I discovered that there is a variable of type "worksheetfunction" and tried using that, but it requires the variables to have data when setting it; in this case, i dont know what cell.value is until it is encountered in the loop. There may not be a way to improve the efficiency, but it was worth asking! I noticed that if the datarange contains more than a couple thousand rows, this takes a VERY LONG TIME to complete, and excel appears frozen.

I use similar looping procedures in other places, and the code works relatively quickly - the only thing making this procedure different is the use of the worksheetfunction inside of it.

any thoughts are appreciated!

johnske
01-16-2006, 04:21 PM
Firstly, this line will slow everything right down to a crawl
Cells(iRow, iColCount).Value = ""change it to
Cells(iRow, iColCount).ClearContentsSecondly, your If test has to check every cell regardless of whether the cell contains any data or not, if there are many empty cells in the range, check to see if the cell contains anything first by putting your If statement inside another that checks for this e.g.
If Cell <> Empty then
'your If statement
End Ifi.e. test for the simplest condition first before going on to the more complex 'Like' condition.

Third, like VBA functions, some worksheet functions are inherently slow and some are fast.

EDIT: The Find function is also MUCH faster than using a 'Like' condition in a loop, so consider using Find instead...

HTH,
John :)

tpoynton
01-17-2006, 07:23 AM
Thank you for the tips! I will work on implementing them...with the testing i have been doing, i have not had any blanks, so I think that a big part of it is just that the percentrank function is just plain slow. However, you have provided me with other ways to improve my code, so THANK YOU! tim

johnske
01-17-2006, 10:23 PM
Not a prob, glad to be able to assist.

Regards,
John :)