Take the 2-minute tour ×

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.

share|improve this question
add comment

3 Answers

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.

share|improve this answer
Oh wow - I wasn't really expecting anyone to actually know of one! Thanks Hobbo :) I'll up your response 1 and accept your answer in the next few days if the component turns out to be good - I've emailed the guy behind it. –  MB. Sep 19 '08 at 19:28
I tried it out: it worked, but it was a pretty basic implementation that relied on changes to the Excel Selection. I'm really hoping for something that worked more like the real RefEdit. So I'll leave this question open on the offchance that somebody knows of something else. –  MB. Sep 26 '08 at 8:33
add comment

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

share|improve this answer
I've not tried it in Delphi... I tried it in VB6 and it didn't work, and the MS link I mentioned does say that "The RefEdit Control is designed to work only when placed on a form in an Excel VBA project ... some special interaction between Excel, UserForms, and the control itself is required." –  MB. Sep 20 '08 at 9:31
But I will give it a go just to be sure - clearly if that worked it would be a great solution and would explain the lack of RefEdit alternatives! –  MB. Sep 20 '08 at 9:32
Well, I've given it a go. You can certainly add a TRefEdit control to a form, but I've not been able to find a way to tie it to an ExcelApplication. I think you'd need to do something like that to get the control on the form to do anything useful. –  MB. Sep 23 '08 at 0:21
add comment

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.

share|improve this answer
add comment

Your Answer


