CODEWITHSUNDEEP
exceluser-defined-functionscircular-referencevba
Joe_Schmoe
Joe_Schmoe

Reputation: 1484

User defined function returning circular reference

in excel i have the following
a1 = "a" b1:b3 = {=UDFtest(A1:A3)}
a2 = "b"
a3 = "c"

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' and 'myLabels' will be provided to me from an api call. My goal is to return the myData, after checking that the user has the correct labels.

The above code works as expected, (because I have commented out the extra lines). However if I uncomment those lines excel gives me a circular reference error.

How can I fix the circular reference?

Upvotes: 1

Views: 911

Answers (1)

Jerry Sullivan
Jerry Sullivan

Reputation: 126

The circular reference is being triggered by Application.Caller.

You can read the values of the UDF arguments more simply by reading the input range into an array. 

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

Upvotes: 1

Related Questions