[ExcelDna] Help with Ranges

[Eduardo Amaral]

Hi,

I've just started using ExcelDna and I'm not sure about how to use
Range objects as inputs or outputs of UDFs. All my programming
experience has been with VBA and I am still crawling with C++.

When I use the following UDF with ExcelDNA it returns an error message
in Excel. Does anyone know what's wrong?

Function OffsetD(<ExcelArgument(AllowReference:=False)> r As Object)
As Object
r = r.Worksheet.Range(r.Offset(1),
r.Offset(r.Worksheet.Cells(1).End(-4121).Row - r.Row))
OffsetD = r.Cells
End Function

Thanks,

--
You received this message because you are subscribed to the Google Groups "ExcelDna" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Govert van Drimmelen]

Hi Eduardo,

With ExcelDna you can get an object of type
ExcelDna.Integration.ExcelReference (when you set the
AllowReference=true flag in the ExcelArgument attribute.)
This is mainly useful for calling other Excel functions that need a
reference as input.

In the ExcelDna Contrib library on CodePlex (http://
exceldnacontrib.codeplex.com) there is an ExcelRange class that can
convert the ExcelDna ExcelReference object to an Excel Range object.

It's not clear from your example what you are trying to do.
Would you like to send a UDF that works as you expect in VBA, then we
can try to translate to Visual Basic in ExcelDna?

Regards,
Govert

[Eduardo Amaral]

This UDF gets a range as input and then outputs a range that goes from
one row below the input to the last row of the worksheet according to
the first column. I use that in data tables which number of rows is
always changing. The UDF in VBA would be:

Function OffsetD(r As Range) As Range
Set OffsetD = r.Worksheet.Range(r.Offset(1),
r.Offset(r.Worksheet.Cells(1).End(xlDown).Row - r.Row))
End Function

[Govert van Drimmelen]

Hi Eduardo,

You'd do this in ExcelDna as follows:

Function OffsetDna(<ExcelArgument(AllowReference:=True)> input as
object) as object
If not TypeOf input Is ExcelReference Then
OffsetDna = "!!! Not a reference"
Else
Dim inputRef as ExcelReference = input
Dim outputRef as New ExcelReference( _
input.RowFirst + 1, _
ExcelDnaUtil.ExcelLimits.MaxRows - input.RowFirst - 1, _
input.ColumnFirst, _
input.ColumnLast, _
input.SheetId)
OffsetDna = outputRef.GetValue()
End If
End Function

Note that both the VBA function and the Dna function have the
disadvantage that they are not recomputed automatically when you
update the lower cells in the column - since the contents of the
reference that you send to the function has not changed...

Regards,
Govert

[Eduardo Amaral]

Hi Govert,

Your function helped me a lot to understand how to translate my VBA
UDFs. It's really close to what I meant, but it returns a range that
goes up to the last row of the spreadsheet, including the empty ones.
I'm still trying to figure out how to stop in the last non-empty row.

I do this in VBA with the command Range.End(xlDirection). Any idea how
to translate it to ExcelDNA?

Thanks,
Eduardo