Пользовательская функция, возвращающая циклическую ссылку

Question

В Excel у меня есть следующее
a1 = "a" b1: b3 = {= UDFtest (A1: A3)}
a2 = "b"
а3 = "с"

Public Function UDFtest(labelsRange As Range)
'    myCaller = Application.Caller
'    outputNumRows = UBound(myCaller, 1) - LBound(myCaller, 1) + 1
'    outputNumCols = UBound(myCaller, 2) - LBound(myCaller, 2) + 1

    myNumRows = 3
    myData = Array(Array(1), Array(2), Array(3))
    myLabels = Array("a", "b", "c")

'    If myNumRows = outputNumRows Then
        For i = 0 To myNumRows - 1
            If labelsRange.Cells(i + 1, 1).Value <> myLabels(i) Then
                myData(i, 0) = xlErrRef
            End If
        Next i
'    Else
'        UDFtest = xlErrRef
'        Exit Function
'    End If

    UDFtest = myData
End Function

"myData" и "myLabels" будут предоставлены мне после вызова API. Моя цель - вернуть myData после проверки того, что у пользователя есть правильные метки.

Приведенный выше код работает, как и ожидалось (потому что я закомментировал дополнительные строки). Однако, если я раскомментирую эти строки, Excel выдаст ошибку круговой ссылки.

Как я могу исправить круговую ссылку?

Answer 1

Циклическая ссылка запускается Application.Caller.

Вы можете прочитать значения аргументов UDF более просто, считав входной диапазон в массив.

Public Function UDFtest(labelsRange As Range)
 Dim bHas0Base As Boolean
 Dim outputNumRows As Long, outputNumCols As Long, i As Long
 Dim myData As Variant, myLabels As Variant, labelValues As Variant

 Const NUM_ROWS As Long = 3

 If TypeName(Application.Caller) <> "Range" Then
    UDFtest = CVErr(xlErrRef)
    Exit Function
 End If

 '--myData and myLabels will be returned from API calls
 myData = Array(Array(1), Array(2), Array(3))
 myLabels = Array("a", "b", "c")
 '--to allow either Option Base
 bHas0Base = LBound(myLabels) = 0

 '--read range values into 1-based 2D array
 labelValues = labelsRange.Value
 If Not IsArray(labelValues) Then
     ReDim labelValues(1, 1)
     labelValues(1, 1) = labelsRange.Value
 End If
 outputNumRows = UBound(labelValues, 1)
 outputNumCols = UBound(labelValues, 2) 'not used yet

 If outputNumRows = NUM_ROWS Then
   For i = 1 To outputNumRows
      If labelValues(i, 1) <> myLabels(i + bHas0Base) Then
           myData(i + bHas0Base)(0) = CVErr(xlErrRef)
       End If
   Next i
   Else
      UDFtest = CVErr(xlErrRef)
      Exit Function
   End If
 UDFtest = myData
End Function