Return Excel Range (Microsoft.Office.Interop.Excel.Range) type object

[Gudguy]

Hi All,

I'm migrating my project from VSTO to ExcelDna. There's just one bit
of a problem which I can't figure out how to solve. I need to return a
Range object type to macro code. I even tried to make a sample example
like below:

[ExcelFunction(IsMacroType = true)]
public static object RunTest()
{
var application =
(Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
Microsoft.Office.Interop.Excel.Worksheet wks =
application.ActiveSheet;
Microsoft.Office.Interop.Excel.Range rng = wks.get_Range("H1");
rng.Value2 = String.Format("Done at {0}", DateTime.Now.ToString());
return rng;
}

and the macro code calling it:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Application.Run("RunTest")
End Sub

but the call to Set rng = Application.Run("RunTest") throws an error

Run-tim error '424'
Object required.

Anyone successfull returning a Range object type from ExcelDna UDF to
macro code?

Thanks and Regards.

[Govert van Drimmelen]

Hi,

You certainly won't be able to return a Range (or any other COM
object) from an Excel-DNA user-defined function. The interface through
which Excel-DNA interacts with Excel does not know about the COM
types.

You could:
1. Return a string from which you can recreate the range in your VBA
code.
2. Port you VBA macro to .NET, so that you can call the other function
directly without going through Excel.
3. Create a .NET library with your code (without using Excel-DNA),
register as a COM library and Tools->Reference it in VBA.

Regards,
Govert

[Gudguy]

Thanks Govert. Migration completed except for this showstopper. I have
the ExcelDna src code (comes along with zipped file) so I can make
little change. Could you pls point me to which project (or class) I
need to tweak so I can support returning Excel Range (COM) object (if
possible). My project came from a VSTO, COM and VBA combinations, and
I move to ExcelDna to drop all those three (feeling lucky). So far so
good except I can't return Range object. Users are using the returned
Range object for further data enrichment (beyond my control).

> > Thanks and Regards.- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Hi,

From an Excel-DNA user-defined function you will not be able to return
a COM type, like the Range.
You could return a string, then the user would use
Application.Range("address") to continue.
In VBA:
address = Application.Run("FunctionReturningRangeAddress", abc)
Set theRange = Range(address)

-Govert

[Jon49]

Not sure if I understand your question correctly but I would think if
you created a library from excel that would do it. It should be able
to act almost like vba after that.

See http://devcity.net/Articles/163/1/Articles.aspx?a=ViewArticle&ArticleID=163&Page=1

[Gudguy]

Thanks Govert. Yes I am returning the Range.Address and construct it
on macro side. Its working perfectly! Really thanks.

> > > - Show quoted text -- Hide quoted text -