Time Formula, time it takes to calculate

[sans]

Hello,

What I'd like to do is to time how long it takes for a formula to finish calculation. I'd like to do this to compare different calculations between Sumproduct vs countif vs sumif etc.

I have found this link

http://msdn.microsoft.com/en-us/libr...ffice.14).aspx


where there's a vba code for measuring calculation time but I cannot get it to work under Excel 2010/64-bit.

Is anybody aware how to get this to work?

Thank you

[cytop]

If you mean the VBA code with the API Declare statements, they are written for a 32 bit system. You need to convert to run on a 64 bit system...

http://www.jkp-ads.com/articles/apideclarations.asp

[Richard Buttrey]

Why not just add
Range("A1") = Time

as the first line of your test process, and conclude it with

Range("A2") = Time

Then the difference between the two is the time the process took.

[sans]

Thank you both for the replies.

I would prefer a vba approach as I could set up a shortcut key which will ultimately be much faster/easier to test.

cytop, I understand that I need to replace the Declare Statements but unfortunately all looks very confusing on how to do it and what to replace exactly. Would it be possible get some extra help on this?

Thank you

[cytop]

Basically, the declarations change from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Any Long types in the declaration may, or may not, change to LongPtr type. Whether they change is determined by what they represent; Function arguments or return values that represent addresses in memory change. This is because a 64-bit Operating System has a memory space that is too large to hold in a Long data type. Arguments or return values that represent data will still be declared Long even in 64-bit.

A good example is the SendMessage API

32 Bit

Please Login or Register  to view this content.

64 bit

Please Login or Register  to view this content.

The hWnd parameter and Return Value change as they are memory addresses whereas the remaining Longs are data...

[sans]

Thank you for the explanation cytop. Just got it to work, only a couple of lines actually needed replacing.

For convenience, in case anyone will find this useful, here's the macro that will work under excel 64-bit

Please Login or Register  to view this content.

Thank you both for the help!

[Richard Buttrey]

Thank you both for the replies.

I would prefer a vba approach as I could set up a shortcut key which will ultimately be much faster/easier to test.

The suggestion I gave you was VBA syntax

[MarmaladeF]

Thank you for the explanation cytop. Just got it to work, only a couple of lines actually needed replacing.

For convenience, in case anyone will find this useful, here's the macro that will work under excel 64-bit...

If you ever read this... thank you