Circular reference in an open workbook

[Joe Cletcher]

I get the error message:

"Microsoft Office Excel cannot calculate a formula. There is a circular
reference in an open workbook, but the references that cause it cannot be
listed for you. Try editing the last formula you entered or removing it with
the Undo command (Edit menu)."

This occurs when I set the formula in a cell equal to a UDF (shown below).
This is the only cell in the workbook with a formula.

If I remove the commented lines

'Range("ServiceYears").Select
'ActiveCell.Value = CalculatedYearsOfService

and execute the UDF from a user defined subroutine (i.e. a Macro) then
everything works fine. However, running a Macro every time another value in
the workbook changes isn't a very elegant solution.

What is really causing the error message?

=========================================
Public Function CalculatedYearsOfService() As Double

Dim NumberOfMonths As Integer
Dim NumberOfYears As Integer
Dim myServiceDate As Date
Dim myRetirementDate As Date

'Application.Volatile
'Application.EnableEvents = False

Excel.Range("ServiceDate").Select
myServiceDate = Excel.ActiveCell.Value
Excel.Range("RetirementDate").Select
myRetirementDate = Excel.ActiveCell.Value
CalculatedYearsOfService = (myRetirementDate - myServiceDate) / 365.25
NumberOfYears = Int(CalculatedYearsOfService)
NumberOfMonths = Int(12 * (CalculatedYearsOfService - NumberOfYears))
CalculatedYearsOfService = CDbl(NumberOfYears + NumberOfMonths / 12)
'Range("ServiceYears").Select
'ActiveCell.Value = CalculatedYearsOfService

'Application.EnableEvents = True

End Function

 

[Joe Cletcher]

Sorry, I meant uncomment the commented lines so that the following lines
appear in the code:

Range("ServiceYears").Select
ActiveCell.Value = CalculatedYearsOfService

 

[Niek Otten]

You can't change anything in a worksheet from within a function that is
called from a worksheet. You can from a sub. You can't cheat Excel by
calling the sub from a function etc.

Change your function to accept the two dates as arguments:

Public Function CalculatedYearsOfService(myServiceDate As Date,
myRetirementDate As Date) As Double

Dim NumberOfMonths As Integer
Dim NumberOfYears As Integer

'Application.Volatile
'Application.EnableEvents = False

CalculatedYearsOfService = (myRetirementDate - myServiceDate) / 365.25
NumberOfYears = Int(CalculatedYearsOfService)
NumberOfMonths = Int(12 * (CalculatedYearsOfService - NumberOfYears))
CalculatedYearsOfService = CDbl(NumberOfYears + NumberOfMonths / 12)

'ActiveCell.Value = CalculatedYearsOfService
'Application.EnableEvents = True

End Function