Alternative to Excel's RefEdit control that can be used outside of VBA

Question

The RefEdit control that comes as part of VBA is a little buggy, but it's good for putting on a form when you want people to specify one or more ranges of cells (i.e. Excel.Range objects).

The main problem is that you can only use the RefEdit control on a VBA UserForm (Microsoft states this, and my tests confirm it too). I'm making an Excel add-in using Delphi, and I'm looking for an alternative to the RefEdit control.

Excel.Application.InputBox Type:=8 is one alternative way of selecting a range of cells, but it's not very user-friendly when you need people to select multiple ranges of cells on a single form. The best real alternative I have at the moment is to call a VBA form from my Delphi add-in, but that's far from ideal.

So ideally I could do with a drop-in replacement for RefEdit - one that I can use on a Delphi form. If there is one, it's not easy to find (I've been searching pretty hard, and I've not been able to find a drop-in RefEdit replacement for Delphi, VB6, or .NET).

Failing a drop-in replacement I might try cobbling together my own alternative, but I suspect it would be difficult if not impossible to make one that works as well as RefEdit. RefEdit lets you "select" cells without actually selecting them: it uses marching ants around the cells that you choose instead of highlighting them and changing the Excel.Application.Selection. I don't know of a way to do that by manipulating the Excel object model through VBA, Delphi, or whatever.

Any tips, tricks, hacks, or, if I'm really lucky, pointers to drop-in RefEdit replacements would be most welcome.

Answer 1

I came across this RefEdit control replacement when looking for workarounds to RefEdit's bugs. A third party control wasn't an option for me at the time but it might help you out.

Answer 2

Not sure from your question: Have you tried to import RefEdit into Delphi?

You can import it as an ActiveX control from RefEdit.dll, then drop a TRefEdit control in any Delphi form. and you have the very same RefEdit as in your VBA apps.

Or is it what you tried and it does not work because RefEdit needs some VBA woodoo...?

Answer 3

This post gives you a skeleton to work from. It's C#, but hopefully it'll give you some ideas: How to code a RefEdit Control.

Answer 4

so, if that's the only problem, then why not use a temp var to keep the application.selection intact, like return it to the previous selection once range selection is done..? I haven't tried it yet, but I kind of hit upon ths issue, and as I was browsing thru, found this. Here's my blog where you can find my post on RefEdit... http://onestopanalytics.wordpress.com/