Dutch Gemini's Weblog Pages

February 3, 2010

Serious Excel bug: Columns Property does not return a full featured Range

Filed under: Excel,Programming,Uncategorized,VBA — dutchgemini @ 4:00 pm

I have found a quite serious bug in Excel, in particularly regarding the ‘.Columns‘ property. This bug is present in Excel 2002/XP (the release some of my customers are using) all the way up to Excel 2007.

Try the following code (you can drop it inside ThisWorkbook):

Public Sub DebugPrintAddress()
    Dim wRange As Range
    Dim wCell  As Range

    Debug.Print "Loop 1 - Using the .Range property..."

    Set wRange = ActiveSheet.Range("A1:B4") ' Select a region with 4 rows and 2 columns
    For Each wCell In wRange
        Debug.Print wCell.Address
    Next wCell

    Debug.Print
    Debug.Print "Loop 2 - Using the .Columns property for the first column of the Range..."

    Set wRange = ActiveSheet.Range("A1:B4").Columns(1) ' From the same region get the first column
    For Each wCell In wRange
        Debug.Print wCell.Address
    Next wCell

    Set wCell = Nothing
    Set wRange = Nothing
End Sub

When you run the code, you get this output in your immediate window:

Loop 1 - Using the .Range property...
$A$1
$B$1
$A$2
$B$2
$A$3
$B$3
$A$4
$B$4

Loop 2 - Using the .Columns property for the first column of the Range...
$A$1:$A$4 <----- Huh?????

As you can see, in the first loop Excel walks through the loop cell by cell, left-to-right and top-to-bottom. In the second loop, however, Excel walks through the loop as if the entire range is a single cell. Indeed, if you try to access the cell’s value, for instance with a ‘Debug.Print wCell.Value‘ statement, Excel will inevitable trigger an run-time error.

According to Excel Help, the ‘.Columns‘ property returns a Range object.

If this were the case, then I should be able to walk through the cells of this range in Loop 2 exactly as in Loop 1, only with less columns.

Apparently, ‘.Columns‘ returns a look-a-like Range, something not having the same behaviour as a regular Range.

For me this qualifies as a bug, but I am happy to learn Microsoft’s point of view on this issue.

Dutch.

Advertisements

6 Comments »

  1. A bug, or the way it’s supposed to work when you specifically use the Columns property?

    If you add this to your code:

    Set wRange = ActiveSheet.Range(“A1:B4”).Columns

    For Each wCell In wRange
    Debug.Print wCell.Address
    Next wCell

    You get each column:

    $A$1:$A$4
    $B$1:$B$4

    Comment by JP — February 5, 2010 @ 8:29 pm | Reply

    • JP,

      This I could accept since you specifically request a collection of columns which in practice is a collection of contiguous areas of cells with a variable number of rows and a fixed width of 1 column (which imho is also a range…). However, in my example I explicitely select the first column and that shall be a range (the documentation says so) and not some kind of pseudo-range.

      Nevertheless, this property continues not to work even when you add a second loop and process the cells of the single column. Try this one:

      Set wColumns = ActiveSheet.Range("A1:B4").Columns
      
      For Each wColumn In wColumns
          Debug.Print "Column: " & wColumn.Address
          For Each wCell In wColumn
              Debug.Print "Cell: " & wCell.Address
          Next wCell
      Next wColumn
      

      Result:

      Column: $A$1:$A$4
      Cell: $A$1:$A$4
      Column: $B$1:$B$4
      Cell: $B$1:$B$4

      You see? Even processing the single column cell-wise does not work.

      The only way to process the column cell-wise is by using a workaround that returns a real range:

      Set wColumns = ActiveSheet.Range("A1:B4").Columns
      
      For Each wColumn In wColumns
          ' Convert the column from a pseudo-range to a real range.
          '
          Set wColumn = wColumn.Parent.Range(wColumn.Cells(1, 1), wColumn.Cells(wColumn.Rows.Count, 1))
          Debug.Print "Column: " & wColumn.Address
          For Each wCell In wColumn
              Debug.Print "Cell: " & wCell.Address
          Next wCell
      Next wColumn
      

      Dutch.

      Comment by dutchgemini — February 8, 2010 @ 9:43 am | Reply

  2. It appears that when you ask for a Range.Columns collection, the smallest unit you can work with is a Column Object. Notice how you have to use the Parent property to “reach back” and get the WorkSheet.Columns object in order to work with cells (not columns).

    Comment by JP — February 8, 2010 @ 10:35 pm | Reply

    • Could be, would it be that a ‘Column’ object does not exist in Excel.

      Apart from being simply absurd having to “reach back” the range via the parent of that range in order to able to browse it by cells, according to the documentation there shall be no difference between a range obtained by direct reference (with .Cells or .Range) or via an item from the Columns collection.

      The only object capable of addressing cells on a worksheet is a ‘Range’ object and this is exactly the type of object the Columns collection contains and returns when accessing the items in the collection. At least, that is what Excel Help (and MSDN) tells us. If Excel’s Help would state this difference, I would not have a problem with it. But it doesn’t, and that’s the big deal.

      It appears instead that an item in the Columns property does not have all the characteristics of a range but there is no way to see this.

      How can you possibly understand whether your range -which you may have passed to a Sub or a Function- has originated from an element of the Columns collection (and I presume also the Rows collection has this magnificent ‘feature’) or from any other property or method in Excel returning ranges.

      This means that each time you receive a range from somewhere, you need to “reach back” the same range before being able to browse by cells. Since we are discussing this issue, shall we do the same for any other native Excel object? Any idea what kind of impact this has on your VBA programs?

      I am more and more convinced that this is simply a bug. And a big one.

      Dutch

      Comment by dutchgemini — February 9, 2010 @ 9:51 am | Reply

  3. Hello Dutch,

    I used to think this was a bug too, but you should use:
    Set wRange = ActiveSheet.Range(“A1:B4”).Columns(1).Cells
    instead of
    Set wRange = ActiveSheet.Range(“A1:B4”).Columns(1)
    Then it works as expected.

    I used to think it was a bug too but I was corrected by a few excellent Excel gurus:
    http://www.asap-utilities.com/blog/index.php/2008/10/23/weird-looping-through-cells/

    Kind regards,
    Bastien

    Comment by Bastien — January 18, 2011 @ 7:58 pm | Reply

    • Thanks Bastien.

      Even though in the opinion of Excel gurus I should have added the “.Cells” to the Column object, this is absolutely not consistent with the documentation.
      According to this, the Column() returns indeed the entire column but in Excel terms the object’s returned type is a Range object. And according to the same documentation, the Range object can be iterated with a For Each on each cell. Unfortunately, this does not work, since the object of type Range is not the same object as for instance “Range()” would return.
      As you advise as a workaround or as a final solution (depends on the point of view), the best to do would be to add the “.Cells” property to each object of type Range that is processed in the code. Very effective indeed, but IMHO unnecessary.
      I continue to think, based on the documentation, that this is a bug and that the Columns() should return a full featured Range that does not require “.Cells” to be appended in order to work.

      Dutch

      Comment by dutchgemini — January 19, 2011 @ 9:04 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

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

Blog at WordPress.com.

%d bloggers like this: