Eugene Starostin

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
    • 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.

      A good Excel add-in selects the current range if the user selected one cell within a valid range.

      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… :)

      The add-in prompts the user to select the current region.

      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?” :)

      A good Excel add-in selects the entire column if the user has selected a few cells within that column.

      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 :)

      Use valid regions for all areas selected by the user.

      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.

      Rows and columns can be selected.

      Let’s sum up, shall we?

      • 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 :)

      Finally, an energizing bonus for AbleBits developers

       

      Improved UI of Merge Cells Wizard for Excel

      And finally, to keep my conscience virgin clear, let me capture one more screenshot on Windows 8, 150% text size… Oops? :)

      Merge Cells Wizard on Windows 8, 150% text size

      Well then, in a month or two we will have a good chance to see what the AbleBits guys will finally come up with :)

Post a comment

Have any questions? Ask us right now!