Как передать результат формулы массива в пользовательскую функцию?
У меня есть пользовательская функция, которая вычисляет линейно интерполированные значения с определенным базовым значением, учитывая это базовое значение и уменьшая общую сумму до такой же, как если бы базовое значение было 0. Я вычисляю ее значение t внутри круглых скобок аргумента, и это кажется быть проблемой.
Я хочу, чтобы она работала в формулах массива, но функция отказывается работать, если ее значение t вычисляется в круглых скобках аргумента. Это работает, если я предварительно вычисляю значения t в отдельном столбце и просто ссылаюсь на этот диапазон, но мне бы хотелось избежать необходимости делать это для большого количества разных мест, в которых мне нужно использовать эту функцию.
Вот рассматриваемая функция, она адаптирована из этого решения:
Function BasedLerp(a As Single, b As Single, t As Variant) As Variant
Dim r() As Single
Dim i As Integer
'THIS FUNCTION APPROXIMATES THE SAME SUM OF VALUES AS IF IT WAS A 'Lerp(0,b,t)' FUNCTION, BY ACCOUNTING FOR 'a' BEING NON-ZERO
aVal = (a - (b / 2)) * 2 'The Function works on the [-1 ,1] range, this adjust a [0,1] input
x = (aVal - b) / 2
If Not (isArray(t)) Then
BasedLerp = (aVal + ((b - aVal) * t)) - x
ElseIf TypeOf t Is Excel.Range Then
ReDim r(1 To t.Cells.Count)
For i = 1 To t.Cells.Count
r(i) = (aVal + ((b - aVal) * t.Cells(i).Value)) - x
Next i
If t.Rows.Count = 1 Then
BasedLerp = r
Else
BasedLerp = Application.Transpose(r)
End If
Else
ReDim r(LBound(t) To UBound(t))
For i = LBound(t) To UBound(t)
r(i) = (aVal + ((b - aVal) * t(i))) - x
Next i
BasedLerp = r
End If
End Function
Я хочу использовать его так:
=BasedLerp([base value];1;(H14:H36/I14:I36))
И выдает #Value ошибки
Если бы я изменил его на
=BasedLerp([base value];1;(H14:H36))
Это сработало бы, но это потребует от меня предварительного расчета значений t в рассматриваемом диапазоне, это добавит десятки ненужных столбцов в мой лист.
Возможно ли это? В настоящее время я предполагаю, что возвращаемое значение (H14:H36/I14:I36) не учитывается в сценарии, но я не могу найти никакой документации о том, каким будет тип возвращаемого значения и его формат.
Попробуйте изменить эту строку r(i) = (aVal + ((b - aVal) * t(i,1))) - x
Когда t является результатом формулы массива, обычно это двумерный массив размером n x m, а не одномерный вектор (исключением являются литеральные массивы, но здесь это не имеет значения). Способ проверить это — установить точку останова в первой строке кода функции и проверить/наблюдать за параметром t.
Ответы 2
Чтобы выявить ошибку, необходим глубокий анализ кода.
Итак, когда функция вызывается с помощью «H14:H36» для t, t становится диапазоном. Расчет выполняется ветвью ElseIf и возвращается вертикальный вектор.
Когда функция вызывается с помощью «H14:H36/I14:I36» для t, Excel вычисляет выражение и t становится двумерным массивом (вертикальным вектором). Вычисление переходит в ветвь Else, и t(i) вызывает ошибку, поскольку t не является одномерным массивом. Исправить это можно таким образом:
Else
ReDim r(LBound(t) To UBound(t))
For i = LBound(t) To UBound(t)
r(i) = (aVal + ((b - aVal) * t(i, 1))) - x
Next i
BasedLerp = Application.Transpose(r)
End If
Да, кажется, это работает! Большое спасибо!
У меня мало опыта работы с VBA, но после просмотра вашего вопроса я также задался вопросом об использовании параметра массива. В (ограниченном) тестировании итерация с помощью For Each, кажется, работает - вы можете попробовать, если вы еще этого не сделали:
2024-08-19: Спасибо ротабору за указание на ошибки - я их исправил.
Function BasedLerp2(a As Single, B As Single, t As Variant) As Variant
Dim r() As Variant
Dim values As Variant
Dim i As Integer
Dim item As Variant
'THIS FUNCTION APPROXIMATES THE SAME SUM OF VALUES AS IF IT WAS A 'Lerp(0,b,t)' FUNCTION, BY ACCOUNTING FOR 'a' BEING NON-ZERO
aVal = (a - (B / 2)) * 2 'The Function works on the [-1 ,1] range, this adjust a [0,1] input
x = (aVal - B) / 2
If Not (IsArray(t)) Then
BasedLerp2 = (aVal + ((B - aVal) * t)) - x
Else
If TypeOf t Is Range Then
values = t.Cells
Else
values = t
End If
ReDim r(LBound(values) To UBound(values))
i = LBound(values)
For Each item In values
r(i) = (aVal + ((B - aVal) * item)) - x
i = i + 1
Next item
If TypeOf t Is Range Then
If t.Rows.Count = 1 Then
BasedLerp2 = r
Else
BasedLerp2 = Application.Transpose(r)
End If
Else
BasedLerp2 = r
End If
End If
End Function
Ваш код не обрабатывается =BasedLerp([base value];1;H14:H36).
Функция успешно обрабатывает item как горизонтальный вектор и возвращает также горизонтальный вектор, что не является ожидаемым результатом.
Спасибо @rotabor, я впервые работаю с Excel VBA;) и моя цель — правильно обнаружить и обработать массив или диапазон. Если у вас есть время, пожалуйста, проверьте обновление — я буду признателен за ваши отзывы. Да, я знаю, что ByRef стоит по умолчанию. Но я видел, как некоторые открыто говорили об этом.
@rotabor, еще раз спасибо за указание на «возвращаемый горизонтальный вектор», это тоже исправил.
Хммм, а как насчет того, чтобы просто вызвать это как
BasedLerp([base value];1;(H14:H36),(I14:I36))и вы сможете выполнить деление в функции?