Spy Journal 3.0 Filtering the web for you

I came across an interesting issue yesterday. I found a limit that I was previously unaware of in Excel 2003.

I had written some code that was taking data from a large data set into an array, filtering for some specific information and writing the results to another array. Then I was attempting to paste that array data into a range in the spreadsheet as a report. All was working well until I selected one specific set of inputs and ran the report. Suddenly it stopped pasting halfway though the paste. That is it would paste some of the data and not the rest and the code would break on the paste function. After some analysis I determined that the particular cell it kept breaking on had 1400 characters in it. Some research on Google helped. I discovered two things.

First that Excel 2003 itself will allow the entry into cells of more than the character limit of 911. However it stops displaying the characters on the screen. In addition the VBA code will not paste an array that contains cells with more than the 911 characters in them. However there is a workaround, it will paste these cells individually, not a very nice work around – in the words of Jon Peltier:

"To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time."

Kind of negates the speed benefits of array-to-range, doesn't it?

I can think of an alternative, though. Make a second array the same dimensions as the first. Populate array 2 with the long elements, and remove them from array 1. Dump array 1 to the range as before, then only populate the cells that have long elements in array 2.

There is some official Microsoft notification of this problem:

You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003

And from the Microsoft Excel Team Blog the limit has been increased in Excel 2007 to 32,000 characters.

While on that blog I want to point you to a 3 part series on Excel 2010 slicers written by Karen Cheng:

• Part 1 Introducing Excel 2010 Slicers
• Part 2 Interacting with Slicers
• Part 2 Dressing up your Slicers

Here is another good post on Excel 2010 Pivot Table What If Analysis

Finally Office 2010 goes out of technical preview and into beta shortly – and the beta will be publicly available. Watch for news on the Office 2010 site.