WorksheetFunctions vs. VBA Code

[marklenel]

Hi folks,

One stage in a piece of data analysis I'm doing involves ranking an array of (double-precision) values. The array is anywhere between 2 and 2500 items long.

So far I am implementing this by writing the values to a worksheet and using the WorkbookFunction.Rank function to return the rank of all the values. Due to repeated use of the function it slows down my code fairly significantly.

So, three questions:-

a) Does anyone know if using single- vs. double-precision values will speed up the function? I'll test this myself anyway...
b) Does anyone have a simple code example to return the rank of an array of values?
c) [Most importantly] is there any evidence to say that a block of code with no worksheet interaction will work faster than the WorkbookFunction.Rank function?

Thanking you all in advance!

Mark

 

[SkipVought]

Hi,

I generated a list of nearly 4000 random values and used the Rank function. The value returns virtually instantaneously.

I guess I am not understanding your problem.

Are you using VBA? If so, please post.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[marklenel]

Hi Skip,

Yes I'm using VBA. This particular WorksheetFunction is used withing a lot of other VBA coding & data manipulation, and is the only thing I can't straightforwardly program in code.

The Rank function is used in the context of a lot of looping, so I believe that the repeated writing of data to the spreadsheet and the repeated referencing of the worksheet function are slowing things down.

I may well be wrong. Maybe it's the sheer amount of data manipulation going on... Just at the moment I'm estimating about 7 days worth of processing power on a 2.4Ghz machine, and I want to get it going a little faster! (Maybe I should learn C++ or something?!?)

Cheers,
Mark

 

[SkipVought]

I can't try help you if you refuse to post your code in response to my question.

Sorry!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[marklenel]

Whoa there! There wasn't a refusal to post code - just didn't realise you'd asked - sorry! To be honest there's too much to code to post here (750+ lines), and the context surrounding the use of the Rank function is more complicated than can be explained in a short forum message.

Perhaps I should make a general request for advice / FAQs / Top-Tips to help make code fast and efficient.

 

[SkipVought]

faq707-4105 How Can I Make My Code Run Faster?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com