More efficient way to use WorksheetFunction in loop?

[tpoynton]

Greetings,

I read johnske's article on how to improve the efficiency of VBA code, but am struggling with how to make this quicker.

    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]

Firstly, this line will slow everything right down to a crawl

Cells(iRow, iColCount).Value = ""

change it to

Cells(iRow, iColCount).ClearContents

Secondly, 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 If

i.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]

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]

Not a prob, glad to be able to assist.

Regards,
John