Cell commenting performance

I never like to guess on performance, its been proven many many times that devs are rubbish at finding performance bottle necks and fixing them. The only valid approach is to test, so I have done some testing on our commenting discussion.
I used a workbook with 57k formulas and calced it 100 times in VBA.

Heres the results:

 

calc times seconds % of Orig NR effect
Original – no comments 5
Choose using cell address – return calc 8 160%
Choose using cell address – return comment 1 6 120%
Choose using cell address – return comment 2 7 140%
Choose using named range – return calc 11 220% 138%
Choose using named range – return comment 1 8 160% 133%
Choose using named range – return comment 2 9 180% 129%
Adding +N(“some comment…”) 6 120%

So using choose could virtually double the calc time, I’m surprised its so expensive. I’m sure a UDF specifically for this purpose could be made significantly faster.
The biggest surprise though is the effect of using a named range, not only does it slow calc time down by 30% but it also bloated the file from 8mb to 11.
Interestingly adding N() with about a 30 character string only slowed things down by 20% (longer strings were worse), and thats using it on all 57,000. On that basis I think occasional use of N() would have no noticeable impact on performance.
I can’t see me ever using it in something I build, but for emergency sticky tape repairs of other peoples stuff, I can totally imagine using it to minimise changed cells.

I used the VBA code at codematic for the timer

anyone got a better approach? Having heard from Charles Williams over at Decision Models (Fast Excel) that whether the calc gets triggered from Excel or from VBA has a massive impact on performance, I wonder if my timer code is a bit too blunt. Maybe its ok for rough stuff?

cheers

Simon

Advertisements

6 Responses to “Cell commenting performance”

  1. Ross Says:

    “…gets triggered from Excel or from VBA has a massive impact on performance. ”

    I did not know that.

    PED has a dll that you can use for timing things – i have seen API code to get a better res timer – the VB one is a bit pants. But i think if your talking in seconds then it should be ok.

    I would like to find out more about the cal from VB or Excel thing though, off to do that now!

  2. Simon Says:

    Ross
    It was discussed at the Excel User conf last year – not sure if it was in the event or in the pub.
    The PED VBA performance dll is really good. dead easy to profile code, install and uninstall and stuff, and I think it does milli secs.
    cheers
    Simon

  3. Ross Says:

    Hi Simon,

    If you’re in the boozer taking about the different effects of calculations in Excel workbooks you have issues my friend! ;-)

    I did go and have a look, but I could not find anything addressing the difference – is Charles around?

    I found his excellent article on MSDN again (http://msdn2.microsoft.com/en-us/library/aa730921.aspx)

    I have been working today on a little tool that wangels his API code together with a bit of a UI, I’ll post it on my blog when I finish it – not sure if the API code is unstable, but Excel fell over a few times during dev efforts today, anyway, all good stuff.

  4. sam Says:

    Hi Simon,
    In a recent project, I converted about 50 odd dynamic names defined using offset/counta combination in to dynamic names using Index/counta combination after learning that offset is in fact a volatile function…. I found a significat increase in calculation speed and a slight reduction in file size…..

    does any one else have a similar experiece….

  5. Simon Says:

    Ross
    Can’t really argue with that, the pub conversations I remember were at lot more lively than calc speed.

    Thanks for the link, this is the sort of content his presentation covered, all really good usable stuff.

    Sam
    Charles’ paper discusses volatile v non-volatile, but I don’t recall anything specific about named ranges. Did you know named ranges use array style evaluation rules? that could impact speed.
    cheers
    Simon

  6. sam Says:

    Hi Simon,
    The paper on the msdn site pointed by Ross has a section on the performance issues of dynamic names defined using Offset and Counta

    I thougth the Index / Couta option would be more efficient.
    =Index($A:$A,counta($a:$a))

    Regards
    Sam

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: