Spy Journal 3.0 Filtering the web for you

Dick Kusleika writes:

The Excel object model provides the Application.CalculationInterruptKey property to control how/if calculation can be interrupted. xlAnyKey is the default and it pauses whenever the user starts working. xlEscKey only pauses when the escape key is pressed. It’s used when you want to allow the user to interrupt calculation, but you want to make sure they do it explicitly and not by accident. xlNoKey prevents interruption. It seems that any time you calculate in code, you should set this property to xlNoKey, then set it back.

Full article here

My stats for June. Not quite as good as J-Walks – though my bounce rate is better!

My apologies for no Excel tips in here lately. I have been asked to write some specific things that will take some time to put together as they involve explaining complex conceptual approaches to solving the problem as well as the specific excel solution. However they are coming – thanks Nicola and Traci. Anybody else want to send me ideas for posts or requests for help feel free.

Today I am just emptying my browser from a whole bunch of other peoples Excel posts I had found interesting and wanted to share.

This one actually answers one of Nicolas questions – kind of – what are the differences between Excel 2003 and 2007. While not specifically answering this question, Allen explains how to find the differences in VBA. Excel 2007 VBA differences

One of the most common problems we have in Excel is using dates and times in relation to calendar years and financial years. In Australia the financial year ends June 20 so financial years cross two calendar years. This also causes issues with the financial year quarters. DataPig Technologies has a post on the BaconBits Blog about Choosing Quarters in Excel.

Jan Karel Pieterse, a long time Excel MVP I have read for a long time, has a extensive Excel website. His comprehensive series on Using Range Names is very useful.

While working on a major coding project we needed to remove sensitivity to case. This article from OzGrid provided the answers we needed. Stop Excel VBA Macro Code Being Case Sensitive & Compare Text. Excel VBA Case Sensitive.

I have had a bunch of pretty cool Excel things to post up – and finally got around to clearing my flagged items and browser windows.

Conditional Formatting

I have written a couple of articles on Conditional formatting in Excel 2007 with lots of readers comments and requests for help. They are the two most read articles on this site.

• Conditional formatting in Excel 2007 - entire row colours
• Excel 2007 Conditional Formatting

I was very interested then to come across this article on Joseph’s site by Amit Velingkar where he shows you how to change the automatic colour ranges that are used in Excel 2007 for conditional formatting. He even includes some VBA code for this.

Ok so that title was a little contrived – and only because my wife bought the classic Jules Verne book yesterday along with Mark Twain’s Tom Sawyer, Louisa Alcott’s Little Woman and Jack London’s Call of the Wild.

This article has a bunch of cool Microsoft Excel related content from all around the world.

10 worst Microsoft Excel practices. Michiel has created this list, but I certainly wouldn’t rank them the same way.

My Top three would be:

• Linked spreadsheet files (workbooks)
• Merged Cells
• Combining reports and data tables on the same worksheet – Michiel’s number 9. The other two don’t even make his list.

Allen Wyatt has a great article providing some VBA code to detect errors in conditional formatting formulas. This is a very practical solution to a very real problem.

Doug Klippert has posted some code from Peter Beach an Excel MVP that will create a list in Excel of all the folders on a drive and their sizes.

Andrew Engwirda has added some cool chart tools to his free Excel Tools. (News flash – he might come back to work for me! – Stay tuned)

Dick’s Daily Dose of Excel has two interesting posts (well lots actually but two I picked out) – the first written is on displaying image galleries in the ribbon written by Ron de Bruin, and the second titled Elle’s birthday.

Joseph has a very good explanation written for the new Excel 2007 tables of how not to break your summing formulas. The same concepts apply in Excel 2003 for the auto sum function using the Sigma button the toolbar.

Oh and that’s a photo of Kitty pretending to be interested in spreadsheeting techniques. Kitty is a business analyst working for Jethro.

I have learnt the hard way over many years ways of speeding up VBA code. Mostly these lessons have come from necessity – the old “go and have a cup of coffee while it runs” comment doesn’t always endear your code to the customer. We have in the past written thousands of lines of code that sometimes takes minutes or even 10s of minutes to run.

Some of the quicker ways to speed things up are in turning off things in excel like calculation, screen updating and the like. Others involve using arrays in code to handle slabs of data and manipulate them in code before returning the results back to the spreadsheet. Others are little things like not selecting ranges etc before using them.

Chad Rothschiller has written a great article on Excel VBA Performance Coding Best Practices going through all these items and explaining how to optimise your code for efficiency. These are beginning tasks. There are certainly lot more complex ways to deal with some of these things but for anybody who started writing VBA by using the record macro function, as I did way back in the early 90’s these tips certainly allow you to speed up some of the very inefficient code that that method creates.

I have highlighted some of them here and added some additional notes.