Range Selection in Excel add-ins – other good manners for developers
In my yesterday’s article I started to muse on Excel’s Range Selection and what can be considered good practices for Excel add-in developers. Well, there are some other good manners for the Range Selection. And if you have a closer look at the user interface of Microsoft Excel itself and some popular Excel add-ins, you will find out pretty convenient behavior of the Range Selection functionality like follows below.
- One cell is selected
- Continuous area is selected
- Two adjacent cells are selected in a row or column
- Selection is not valid
- Summary
- Using the Range Selection dialog / edit box is mandatory if your Excel add-in works on the selection.
- Expanding the selection to the valid current range automatically is a good manner if the user selects one cell within the range.
- We do not change the selection if the user selects two and more cells.
- We notify the user if they select a large array of data.
- We notify the user if the selection is not valid as soon as we get their selection.
- Optional: we expand the selection on rows / columns if the add-in requires this.
- Optional: Add-in Express for Office and .net might include the Range Selection functionality; something like an Excel-specific dialog / edit box :)
One cell is selected
On its start, a well-behaved and sound Excel add-in will select the current range if the user selected one cell within a valid range. But if that add-in requires just one row or column as input data, it will select the current row or column of the current range, respectively, and display this selection in its Range Selection edit box.
Note! If the range contains a large array of data, a good manner is to notify the user about this fact.
If there is no valid range (Selection.CurrentRange returns the selected cell itself), the add-in prompts the user to select a valid range. This would urge the user to give some thought to what exactly they want to do.
Continuous area is selected
This is the case when Selection.Areas.Count returns 1 and Selection.Count > 1. In this case, the rule is to assume the user knows exactly what they are doing and they deem the selection valid. Naturally, we should consider the add-in’s specificity, what if your add-in requires input in the form of a triangular range or range-circle. For example… :)
However, there may be optional choices here as well. For instance, v-v-v-very smart add-ins exist in nature that provide the following options.
Two adjacent cells are selected in a row or column
In this case, we can surmise that the selection is going to be expanded _only_ to that particular row or column, respectively, even if the add-in requires a valid range as input data. But nothing prevents us from asking the user – “Hey! Maybe we’d better use the entire range after all?” :)
I have also come across the add-in that automatically picks two columns and two rows (crosswise) when a 2×2 area is selected. The same happens in case of 2×3 selection, then two rows and three columns get selected (I am writing “2×3” because Range.Item(RowIndex, ColumnIndex)). But all this is really too much!
Selection is not valid
Sure, validating a selection is up to a particular add-in. A typical developer error peeps out here every time when a selection is not continuous, i.e. when the user selects several areas (Selection.Areas.Count > 1). BTW, the height of perfection is the ability to recognize and pick up a selection of several valid ranges in which just one cell is selected :)
Well, and the “classics of the genre” is to make the user deal with all edit, combo, check boxes and radio buttons first, patiently wait until they click the Run button and only after that announce that their “Selection is not valid for the add-in.” I this case I always think to myself – “Hey guys, couldn't you have warned me earlier?” :)
Don’t dare think this is all!
Oh yes, there may be some other cases related to your particular add-in. Just remember to look at the Excel UI more often – this will give you a good helping hand in designing your add-ins’ UI. And try to foresee the user’s actions. Believe me, they will be very grateful to you for this.
Let’s sum up, shall we?
Finally, an energizing bonus for AbleBits developers
And finally, to keep my conscience virgin clear, let me capture one more screenshot on Windows 8, 150% text size… Oops? :)
Well then, in a month or two we will have a good chance to see what the AbleBits guys will finally come up with :)