The Excel property Precedents.Count is returning a different value within a cell formula than the value shown when calling the same function within the immediate window.

See below for the function and testing code.

Function CountPrecedents()
  CountPrecedents = Range("A1").Precedents.Count
End Function

Sub TestCountPrecedents()
  Range("A1").Formula = "=B1+B2"
  Range("C1").Formula = "=CountPrecedents()"
  Debug.Print "The cell value is " & Range("C1").Value
  Debug.Print "The true function value is " & CountPrecedents()
End Sub

Running TestCountPrecedents in the Immediate window results in the following output:

The cell value is 1
The true function value is 2

Why would the cell function have a different value than the immediate window? I cannot replicate this behavior with other Range properties.


This is a limitation of using this Property in a cell-based UDF().

The same UDF will function correctly if called from within a sub:

Sub MAIN()
    MsgBox CountPrecedents()
End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.