Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 07, i don't want circular reference arrows every time I open | Excel Discussion (Misc queries) | |||
UDF not returning proper value - circular reference/multi workbook | Setting up and Configuration of Excel | |||
UDF not returning proper value - circular reference/multi workbook | Excel Programming | |||
Open an existing workbook and reference it | Excel Programming | |||
removal of circular reference error on excel workbook load | Excel Programming |