Circular reference in an open workbook

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

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

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


"Joe Cletcher" wrote:

> 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
>

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


--
Kind regards,

Niek Otten


"Joe Cletcher" <Oak Ridge National Laboratory> wrote in message
news:[email protected]...
> Sorry, I meant uncomment the commented lines so that the following lines
> appear in the code:
>
> Range("ServiceYears").Select
> ActiveCell.Value = CalculatedYearsOfService
>
>
> "Joe Cletcher" wrote:
>
>> 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
>>