I’m not sure how many are aware of the RefEdit Control in excel. Infact, all of us are, only no one knows it by this name!
How many times have you had to select a range from an Excel Sheet? Umpteen! And in all of those, you’d have found this tool that minimizes the window application inside excel, and lets you choose the range while also entering the range dynamically in the TextBox of
the Application used. This is the RefEdit Control and is quite useful, though not entirely!
Here to the right is how this looks like… Now you get it??!
So, now what are the big plusses of this Control?
Actually, I know of just one, and it’s a huge one! 🙂
The thing is, RefEdit lets you select a set of cells/ranges/multiple ranges without without actually activating the selection. Hence, the input window is still active while we select the range of cells, and the selection is highlighted by the marching ants boundary! Hence, the Excel.Application.Selection does not change when RefEdit is used! Largely the one thing every programmer would love to have!
But it doesn’t come without a bag of pitfalls! And it’s quite a big bag!
1. It can only be used with VBA Userform – Not COM Add-in, not anywhere! (You have Microsoft’s word on this!)
2. When used from a Excel 2007 and saved, it creates issues when re-opening from other Excel prior to 2007, or from other locations if the file is publicly accessible/shared. (Solution to this lies in just re-adding the RefEdit reference to the same path eg. C:\Programs
files\Microsoft Office\office11\RefEdit.dll, then it should be solved usually!)
3. The RefEdit contains 2 modes – Enter mode & Edit mode. This is both a boon & a bane. Boon because you can resort to editing the range without having to re-select the range, specially if it’s elsewhere than the current activesheet. Bane, because, handling automated tasks without being manually able to control Enter/Edit separately would be a disaster!
There are some alternatives that I got when I googled. Though, none works as best as this one, along with offseting the negatives. Guess you can’t have the apple AND eat it!
One popular alternative is this –
Excel.Application.InputBox Type:=8. Here the type:=8 signifies a range selection input. But this is not the best way, since one, it selects the range thereby changing the selection and two, it next to impossible to have multiple range selection or other worksheets/workbooks! (I’m currently trying to work around this. Suggestions will be greatly appreciated)
There’s one more tool that I found – http://www.oraxcel.com/downloads/index.htm?product=litrefedit. I haven’t checked this out, but I did see elsewhere that it was a good alternative, but a very nascent one. Needs to evolve more before it can replace RefEdit.
So that said, check out if you want to try out using this anywhere on your forms. And please do come back with comments if you have any on next steps on this. I’ve also quite read some issues trying to be able to handle the data after the selection is done to input it elsewhere. I’ll keep that for future, although comments here, again, are welcome.