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

Circular reference in an open workbook
 

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

Circular reference in an open workbook
 

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