Range Selection in Excel add-ins – good manners for developers
Having been fully absorbed in developing our frameworks for a while, I lost any connection with the other part of our business – ablebits.com. Doh, I shouldn’t have allowed this to happen. I clearly realized that on last Saturday evening when, to the wailing of snow storm Xavier raging across this country, I installed the latest version of the Excel Ultimate Suite on my laptop.
Note! Before explaining the reasons for this sorrow of mine, I must say that I am not a UI/UX guru, but I am definitely a power user of Office. That is why, though my contemplations might not be the final truth, hopefully they will give some good ideas to my readers. As for the developers from the AbleBits team, all this will be a blueprint for action. I would agree if someone considers everything written below trite and commonplace. But as it turned out not all our developers know these here banalities.
Okay, let’s get to the subject of this article. Now I am going to write down one sentence in irritatingly large letters. It is worth re-reading a few times, chewing the cud on this and only after that you can continue reading. Here is the sentence:
If your Excel add-in works with/on/over the selection, using the Range Selection dialog or edit box is not optional – it is MANDATORY!
Your users long ago got accustomed to the fact that if they see a Range Selection edit box like this…
They understand the selection is under their control. They, your users, got used to this control ages ago and they know that most often this is the range to which the transformations made by your add-in are supposed to be applied. For me the add-in I am doing to describe further is an example of a disgusting UI, which confirms the sentence you have read above.
What happens if the Range Selection is ignored?
Here is the add-in in question – Merge Cells Wizard. It performs a plain transformation – merges multiples cells into one row-by-row or column-by-column separating the values with a specified delimiter. For example, if you have first names, last names and middle names in 3 different columns, you can get one column with full names. You can find the complete description here and I will show you a screenshot in a moment. BTW, I have Excel 2013 installed, but everything written below concerns add-ins for all Excel versions. So, here comes the screenshot…
Oh no, I am not going to mention the abundance of borders, surprisingly flat colorlessness and evident bugs in the UI, which make this add-in look repulsive per se. It is not this issue that puzzled me at the very first start. I asked myself another question – where can I change the selection? “Merge selected cells row by row” is there while a customary “Select range” is nowhere to be found. But why? Why on earth do I have to close the add-in, select the cells I need and run it again? Could it really be so hard to do it in this way (my Photoshop did a nice job)?
In this case there’s no need to close the dialog. Our user will click 
– the button with a red arrow pointing up toward the top left corner – in a usual way and select everything they need. Everyone remembers what this “button with a red arrow pointing to the top left corner” does, don’t they?
Range Selection dialog box
This button hides the main window and shows the familiar Range Selection dialog box (I took it from the “Create Pivot Table” dialog):
Agree, it takes some pains. But along with the Merge Cells Wizard I can see some really nice add-ins in that Excel Suite that provide such functionality. So, “Not pass”!