VBA: проблема с функцией

[Irbis]

Короче, такая беда.
Написал функцию линейного интерполирования

Код: Выделить всё

Function Agp(xt(), yt(), x, n As Integer)
Dim i As Integer, j As Integer, j1 As Integer
For i = 0 To n
If x <= xt(i) Then
GoTo a25
End If
Next i
i = n
a25: j1 = i - 1
     j = i
dy = (x - xt(j1)) / (xt(j) - xt(j1)) * (yt(j) - yt(j1))
Agp = yt(j1) + dy
End Function

Когда вполняю ее как функцию в ячейке экселя - ввожу диапазон данных и переменные - в результате выдается #ЗНАЧ!

В самом же VBA эта функция работает (вот пример её вызова и проверки значений)

Код: Выделить всё

Public xt(0 To 20), yt(0 To 20), n, x
Sub dan()
Open "D:\rez.txt" For Output As #1
For i = 0 To 20
xt(i) = Cells(i + 5, 1)
yt(i) = Cells(i + 5, 2)
Next i
Lint1 = Agp(xt(), yt(), 5.5, 21)
Print #1, Lint1
Close #1
End Sub

Почему так?
Как мне заставить работать функцию?

[Irbis]

Пришел к выводу, что таким образом не передается массив из ячеек...
По одной переменные передаются. а массивом - нет...

Как же мне передать в функцию массив?

[Sokl]

Диапазон ячеек - двумерный массив...
Можно с этим поэкспериментировать:

Код: Выделить всё

Function My(myArray As Range) As String
    Dim myArrayV As Variant
        myArrayV = myArray
        My = "[" & LBound(myArrayV, 1) & "x" & UBound(myArrayV, 1) & "]" & "[" & LBound(myArrayV, 2) & "x" & UBound(myArrayV, 2) & "]"
End Function

[Irbis]

Хм... но ведь одну ячейку мы не описываем как одномерный массив!
Почему тогда несколько сразу становятся двумерным? Разве в функцию не значения самих ячеек передаются?

[Genyaa]

Разве в функцию не значения самих ячеек передаются?

В Excel на уровне формул в ячейках обычно передаются именно ссылки (адреса) на ячейки и области ячеек. Но в случае несоответствия типов передаваемых данных в аргументах, Excel преобразует данные, так, чтобы функция смогла работать. Именно поэтому, хотя в функциях, созданных пользователем, может быть указано значение переменной в качестве входящего аргумента функции, однако при вызове этой функции на входе ее может быть указана ссылка на ячейку - в этом случае Excel сопоставляет типы данных аргументов и сам решает, что вместо ссылки на ячейку нужно передать ее значение.

Точно так же, в качестве результата довольно многие встренные функции Excel возвращают не значение, а ссылку (адрес). Так функция ИНДЕКС возвращает именно ссылку, а не значение ячейки. Поэтому такую функцию можно использовать в качестве аргумента для передачи в другую функцию диапазона ячеек. Например, так:

=СУММ(A1:ИНДЕКС(A2:A500;B2))

- в этом случае область (размер диапазона) суммирования будет зависеть от того, какое значение в ячейке B2.

[Irbis]

Ну,, речь и идет только о функциях, определенных пользователем.
Sokl - тво йрпимер что-то не понял...

[Genyaa]

Возможно, Sokl хотел сказать, что стоит Вашу функцию сделать приблизительно такой:

Код: Выделить всё

Function Agp(xt as Range, yt as Range, x as Single, n As Integer)  'возможно n аргумент вообще не нужен.
Dim i As Integer, j As Integer, j1 As Integer 
For i = 1 To xt.Cells.Count
If x <= xt.Cells(i).Value Then 
GoTo a25 
End If 
Next i 
i = xt.Cells.Count
a25: j1 = i -1
     j = i 
dy = (x - xt.Cells(j1).Value) / (xt.Cells(j).Value - xt.Cells(j1).Value) * (yt.Cells(j).Value - yt.Cells(j1).Value) 
Agp = yt.Cells(j1).Value + dy 
End Function

Саму работу функции я не проверял. Просто продствил структуры операндов с областями ячеек (Range). Попробуйте.

[Irbis]

Genyaa, Спасибо огромное, все понял! (Кстати, заработала с первого раза! )

[Sokl]

Irbis, пример мой показывает:
При передаче в пользовательскую функцию аргумента типа Range можно работать с объектом Range, как говорит Genyaa, т.е. xt.Cells(...).Value, а можно "проглотить" диапазон в переменную Variant и иметь в ней двумерный массив значений исходного диапазона Range. Работать с двумерным массивом, а не с объектом Range.

[Sokl]

Вот ещё несколько примеров, можно на досуге поразбираться:

Код: Выделить всё

Function f(x) ' В качестве аргумента - ссылка на ячейку или значение (текст, число)
    Dim item
        If (IsArray(x)) Then
            For Each item In x
                f = f & CStr(item) & ";"
            Next
        Else
            f = x
        End If
End Function

Function s(x) ' В качестве аргумента - ссылка на дипазон, сумму в которо следует подсчитать, либо число
    Dim item
        If (IsArray(x)) Then
            For Each item In x
                If IsNumeric(item) Then s = s + item
            Next
        Else
            If IsNumeric(x) Then s = x Else s = CVErr(xlErrNum)
        End If
End Function

Function a(ParamArray args() As Variant) ' В качестве аргумента - ссылка на диапазон, сумму в котором следует подсчитать или массив чисел.
    Dim arg
        For Each arg In args
            a = a + s(arg)
        Next
End Function