Names and UDF returning a range

[Bart]

I am wondering if it is possible to have an UDF that returns a range that can be used in a named range.  
At the moment I used the name manager in excel to give the following definition for the field AgeRange: 

='Sheet1'!$A$1:INDEX('Sheet1'!$A$1:'Sheet1'!$EA$1;1;MyAge;0)

I would like to know if I could replace it by: 
= MyUDF('Sheet1'!$A$1, Sheet1'!$EA$1,MyAge)

Where MyUDF returns the same range after with some additional calculations I want tor do in MyUDF in vb.net

[Bart]

The problem I had was that in exce-dna getting a range  with the following statement:
 Dim source As New ExcelReference(7, 15, 3, 3, sheet1.SheetId)
results in a two dimensional array of object. I was expecting a one dimensional array that could be used as a dataseries in a chart

Seeing my solution, I should have figured it out earlier, but excel-dna is stil very new to me.

The beginning of my solution looks like this. I still have google for a way to implement the index function  : 

    Public Function GetRange() As Object()
        Dim rows As Integer
        Dim MyRange As Object()
        MyRange = Nothing
        Try
            'Dim selection As ExcelReference = CType(XlCall.Excel(XlCall.xlfSelection), ExcelReference)

            'Read example
            Dim sheet1 As ExcelReference = CType(XlCall.Excel(XlCall.xlSheetId, "Sheet1"), ExcelReference)
            Dim source As New ExcelReference(7, 15, 3, 3, sheet1.SheetId)
            Dim SelectionContent As Object = source.GetValue()
            Dim values1 As Object(,) = CType(SelectionContent, Object(,))


            rows = values1.GetLength(0)
            ReDim MyRange(rows)

            For i As Integer = 0 To rows - 1
                Try
                    MyRange(i) = values1(i, 0)
                    'MsgBox(CDbl(values1(i, j)))
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

            Next


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        GetRange = MyRange
    End Function

Op maandag 28 augustus 2023 om 11:41:57 UTC+2 schreef Bart:

[Bart]

Hello,

Using the xlfOffset function proves to be a real challange. Probably due to lack of the right knowledge. 

I changed my code as follows but  I get an error that says that system object (,) cannot be converted to type ExcelDNA.Integration.Excelrefference. 

Any help is appreciated

Public Function GetRange1() As Object()

        Dim rows As Integer
        Dim MyRange As Object()
        MyRange = Nothing
        Try
            'Dim selection As ExcelReference = CType(XlCall.Excel(XlCall.xlfSelection), ExcelReference)

            'Read example
            Dim sheet1 As ExcelReference = CType(XlCall.Excel(XlCall.xlSheetId, "Sheet1"), ExcelReference)
            Dim source As New ExcelReference(7, 15, 3, 3, sheet1.SheetId)
            Dim SelectionContent As Object = source.GetValue()
            Dim values1 As Object(,) = CType(SelectionContent, Object(,))

            Dim OffsetRef As ExcelReference = XlCall.Excel(XlCall.xlfOffset, values1, 1, 1, 1, 1)

            MyRange = OffsetRef.GetValue

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        GetRange1 = MyRange
    End Function

[Bart]

After some trial and error I got the code working

    Public Function GetRange1() As Double()
        Dim rows As Integer
        Dim MyRange As Double()

        MyRange = Nothing
        Try
            'Dim selection As ExcelReference = CType(XlCall.Excel(XlCall.xlfSelection), ExcelReference)

            'Read example
            Dim sheet1 As ExcelReference = CType(XlCall.Excel(XlCall.xlSheetId, "Sheet1"), ExcelReference)
            Dim source As New ExcelReference(7, 15, 3, 3, sheet1.SheetId)
            'Dim SelectionContent As Object = source.GetValue()

            Dim OffsetRef As Object = XlCall.Excel(XlCall.xlfOffset, source, 4, 0, 6)
            Dim SelectionContent As Object = OffsetRef.GetValue()

            Dim values1 As Object(,) = CType(SelectionContent, Object(,))

            rows = values1.GetLength(0)

            ReDim MyRange(rows - 1)

            For i As Integer = 0 To rows - 1
                Try

                    MyRange(i) = CType(values1(i, 0), Double)

                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

            Next


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        GetRange1 = MyRange
    End Function

Op donderdag 31 augustus 2023 om 15:30:24 UTC+2 schreef Bart: