Spy Journal 3.0 Filtering the web for you

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.

And once again clearing my browser of all the interesting tabs I opened this week. Not many – I have been working too hard on deadlines!

Microsoft Technologies

IE8 has been released and being rolled out. I have been using the beta and RC1 for months. Time to get the real thing now. Download IE8 now.

The Windows Experience Blog has some page load time tests for IE8 versus Firefox and Chrome.

Embed your live calendar on your website or blog. Sarah Perez posts a how to.

Sarah also posts on the instructions to setup POP3 for hotmail – now available worldwide.

Some interesting comments over at Daily Dose of Excel about the future of VBA.

Doug posts up a cool excel window minimise /maximise animation piece of VBA.

Gadgets

Doug shows off a portable wireless hotspot in a backpack.

Internet

The Technium posts a treatise on the concept of building a fan base of 1,000 true fans in order to generate a living. There is a follow up with some reality checks on depending on True Fans.

Live side gives us Face book comes to Windows Live Frameit Power Toy.