CODEWITHSUNDEEP
vbaexcelcircular-reference
Istvan Heckl
Istvan Heckl

Reputation: 1101

Circular reference is user defined function VBA excel

My aim is to add the values of certain columns using a user defined function in the actual row. The columns are given in another table. I am reading the name of rows, calculating the actual value and sum them. This function called once from excel but executed 4 times. At the end it indicates a circular reference error. There is no such error in the excel file, I checked if the udf return just 42 then there is no error. First I suspected Application.Caller, but ruled out.

Function SumColumnsWithSuffix(suffix As String, rowNumber) As Integer
    'can be used only in Összesíto table
    Dim myTable As Excel.ListObject
    Dim mySheet As Excel.Worksheet
    Dim myRow As Excel.ListRow

    Set mySheet = ThisWorkbook.Worksheets("összesíto")
    Set myTable = mySheet.ListObjects("Számlák")
    Dim columnName As String

    result = 0

    For Each myRow In myTable.ListRows
        columnName = Intersect(myRow.Range, myTable.ListColumns("Oszlop név").Range)
        columnName = "Összesíto[" & columnName & " " & suffix & "]"
        'actualRow = Application.Caller.row
        'rowName = actualRow & ":" & actualRow
         rowName = rowNumber & ":" & rowNumber
         myRowRange = Range(rowName)
         actualValue = Intersect(Range(columnName), Range(rowName))
         result = result + actualValue
     Next myRow

    SumColumnsWithSuffix = result
End Function

Upvotes: 0

Views: 675

Answers (1)

Comintern
Comintern

Reputation: 22185

myRowRange is not explicitly declared (or used, actually) so it is implicitly a Variant. That means your assignment here...

myRowRange = Range(rowName)

...is also making an implicit call to .Value. That call will evaluate the results of every single cell in Range(rowName) to populate the array of Variant that it returns. If any of those cells contains a call to SumColumnsWithSuffix, you'll get a circular reference.

Upvotes: 1

Related Questions