Dutch Gemini's Weblog Pages

February 25, 2009

Excel VBA Oddity: SpecialCells() considers Sheet and not selected Range

Filed under: Excel,Programming,VBA — dutchgemini @ 11:04 am
Tags: ,

Excel provides a nice function that allows selecting, within a range on a worksheet, only a particular type of cells. This function is available interactively when you activate the Go To dialogue (F5) and then click the Special… button. So far so good.

In VBA this function is accessible through the .SpecialCells method (from Excel VBA Help):

expression.SpecialCells(Type, Value)

The expression refers to a Range collection.

When the method is used in VBA, it copies the behaviour of the interactive activation. That means that when the selected range on the worksheet is a single cell then the method considers expression to be the entire worksheet and not the single cell. Even when you explicitely declare a single cell as a range, it behaves like that.

To test this, do the following:

  • In a blank workbook fill a cell, for instance “A1”, with a formula ‘=AVERAGE(1,2,3)
  • Select another empty cell (I took “B10”).
  • Open VBE and in the Immediate Window type:
  • ActiveSheet.Range("B10").SpecialCells(xlCellTypeFormulas).Select

    You will notice that the cell with the formula (in “A1”) gets selected.

  • On the sheet select another cell and in VBE’s Immediate Window now type (watch the range):
  • ActiveSheet.Range("B10:B10").SpecialCells(xlCellTypeFormulas).Select

    You will notice that again the cell with the formula (in “A1”) gets selected.

  • On the sheet select another cell and in VBE’s Immediate Window now type (watch the range):
  • ActiveSheet.Range("B10:C11").SpecialCells(xlCellTypeFormulas).Select

    This time you get an error message from Excel (what I expected in the first 2 cases also to occur).

The error message is triggered because the explicit range declaration covers more 2 or more cells. With only one cell covered, no error message is triggered at all (except if no cell on the worksheet contains or is what you are looking for).

This may be an intended behaviour of SpecialCells, but when you program in VBA and you do not know on the forehand how big the selected range of cells will be, it may have a dramatic impact on your workbook if, for instance, you issue a .Clear or a .Delete method immediately after having selected a range using SpecialCells.

When programming with cells or ranges, you expect that each function, property or method is consistent with the object or expression it is used with, independent of the number of cells or the size of the range. If it is not, then you expect to find at least a glimpse on any discrepancy in the documentation. In the case of SpecialCells, VBA’s Help does not, and even Excel’s Help is incomplete if not incorrect on the subject.

To minimise the damage, you will need to test if the selection has only 1 row and 1 column before using SpecialCells.

Another challenge consists in working with the method and the (only) selected cell is part of a merged range of cells. In this case the .Row and .Column properties return the original, un-merged, size. You can use the MergedCells property to test if the cell coordinates are in a range of merged cells and eventually skip any action on the cells that are not the first one (at 1,1) because it may un-merge cells automatically without any warning!


Leave a Comment »

No comments yet.

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: