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

Spreadsheet support

One of the common issues customers highlight is the lack of on-going support available for their custom spreadsheets.

I see this as a real barrier quite often, in fact previous clients have insisted I avoid VBA to enable their in-house team to provide on-going maintenance. If you have to twist and contort some horrible formulas to do something that is trivial in VBA then this doesn’t necessarily hold true of course.

I always support my own work, and Codematic is now specifically offering to support and maintain other peoples spreadsheets, no matter who wrote them and what state they are in. (this can be quite entertaining!)

What I would like to see though is some sort of cooperative group of developers where we are willing and able to look after each others work (at a price of course). I think being able to go to a client and say:

“If I build this system for you, you have the security of knowing 20/30/40 (or more?) other professional developers are ready willing and able to keep it working for the rest of its useful life”

This was what I had hoped for with PODA early on, but its aims are maybe more general. I think there is still space for a specialised organisation.

I dont really have any firm ideas on what form this might take, but I think it would have to be free/cheap for developers to join. It would be handy if there were a forum for advice and best practice too.

So my questions are:

Have you experienced this lack of ongoing support barrier?

If you were a customer would this offer of on-going support reassure you?

Do you think this is a good idea/ workable idea, and would you join in?

Any other thoughts?

cheers

Simon

New Excel security vuln

Take care out there:

http://www.theregister.co.uk/2007/02/05/0-day_office_flaw/

[Dodgy spreadsheets can crash your pc and/or compromise security]

This one is already being exploited (hence zero day), so watch what you are downloading. If some of the others are anything to go by, this is exploitable no matter your Excel macro security settings. A malformed string somewhere in the workbook does the damage, not any code. This type of attack is getting more popular.Virus definitions will probably not detect it yet, so don’t rely on a scan.

In a related area, I wanted to test security at a clients so I created a few different workbooks with auto open code in.

I stuck a link to them here (near the bottom) -(given the above don’t feel you have to try them!):

http://www.codematic.net/Excel-development/Excel-dev-general/excel-security.htm

net result for the client- nil point, all ran without warning, meaning any potentially harmful VBA (or XLM) malware could have run too. The internal office support team weren’t interested, prefering to rely on virus scanning (see above for the dangers of this approach). Another vote for the Dale Carnegie course?

Anyone else seen a place that forces office macro security to low by policy?

Cheers

Simon

Commenting spreadsheet formulas

One complaint often aimed at spreadsheets is that formulas in cells can’t have helpful comments embedded. Unlike VBA code for example. I’ll save my thoughts around code commenting for another post, and focus on spreadsheet formulas here. Ideally formulas should be so short and simple and well described by text in cells nearby that further description is not required. However that may not always be possible, especially when modifying some monster spreadsheet someone else developed.

One possible approach is to use the CHOOSE worksheet function, this takes an index number as the first argument, and then a list of choices for up to 29 (or so) further arguments. CHOOSE will return the item in the list found at the index given as the first argument.

This could be used like this:

=CHOOSE(1 2 or 3 ; My horrible formula; “short comment”; “long comment”) (I did this example in Open Office Calc hence the ‘;’ rather than Excels ‘,’ argument separator)

 

Then depending on whether the first value (a named range called ‘view’ in this case) is 1, 2 or 3 you get either the formula (calculated as normal), short comment, or long comment .

I’ve uploaded an example to codematic here, I probably could have thought up a better example, but this one illustrates the point. There is an Open Office (v2.0) format file (.ods) and an Excel one (.xls). I tested the xls in Gnumeric too.

There is also a separate XLM macro file to provide the FORMULA worksheet function that OO has that Excel doesn’t, called FORMULAwsfxl.xls). You don’t need that to see the CHOOSE example, but I’ve found it to be a very handy formula. It takes one argument, a cell reference and returns the formula in that cell as a string. If you want to use it just copy the macro sheet into the chooseeg.xls workbook and all the #NAME errors at the bottom should go away. I left this out of chooseeg.xls, as some people may not want to open files with XLM macros in. I could have done the function in VBA but XLM is much faster. Its on my list of things to convert to an xll.

I have no idea of the performance impact of putting loads of CHOOSE formulas in a big workbook, but I wouldn’t be surprised if it was very minor. If people think its a decent approach I’ll do some proper testing. I didn’t try it wth array formulas either.

Please have a look, and leave a comment with what you think, is it a good approach? Are comments better? Would you/do you use it? Any better ideas? Did it work right? I think it would work better on a non tabular example, are there any other limitations you see?

cheers

Simon

The joy of text (part 2 of 2 hopefully)

This follows on from joy of text (http://smurfonspreadsheets.wordpress.com/2007/01/22/the-joy-of-text-long/#comments)
I didn’t explain well enough what I was on about, so here is an example that might clear things up.

www.codematic.net/files/joyoftext2.zip
Download this zip from codematic, open the .xls and the .xll, try and change C7 to be treated consistently as a number, whilst maintaining its text numberformat. Column E contains the UDF CellDataType, that tells you how Excel is treating the cells currently. This is defined in the xll.

Note this is a specific issue around the text numberformat, and quite a different issue from imported data that gets incorrectly treated as text temporarily. The solutions proposed last time around fix the latter case, but not as far as I can see the former. Unless you know different??

The purpose of this couple of posts is to highlight the issue, I don’t see it as a bug in Excel at all. If you tell Excel to treat a cell as text and it does, how can that be wrong? But it might be worth using the tools that Excel provides just to make sure you didn’t do it by mistake.

I wrote the xll a while ago, as I couldn’t find the ‘.datatype’ property of a range, that I was looking for. However a quick google the other day and I found John Walkenbach had a VBA UDF to do the same thing. Oh well, I’m sure the xll version will be faster.

http://j-walk.com/ss/excel/tips/tip62.htm

cheers
Simon