UDF Allow Circular Reference (Or only calculate once)

[MrKowz]

Good afternoon!

I'm in the process of optimizing a spreadsheet and have an unconventional need to cause a circular reference.

In my UDF, I have one argument that must be above a certain date if another condition in my formula holds true. If it is not above a certain date, it is automatically adjusted to the date.

To explain the logic, I have a variable "DOC" and a variable "LOC". If LOC contains "SEA", "BOARDWALK", or "CYPRESS", then DOC must be greater than or equal to 12/1/89. If DOC is not greater than or equal to 12/1/89, then DOC needs to be adjusted to that date when the above condition is met.

DOC and LOC are dimmed as range.

    If DOC <= DateSerial(1989, 12, 1) And (InStr(UCase$(LOC), "SEA") > 0 Or InStr(UCase$(LOC), "BOARDWALK") > 0 Or InStr(UCase$(LOC), "CYPRESS") > 0) Then
        DateOfParticipation = DateSerial(1990, 1, 1)
        DOC.Value = DateSerial(1989, 12, 1)
        MsgBox "The Date of Credited Service has been adjusted to 12/1/1989", vbOKOnly, "Date of Credited Service"
    Else

When I try to run this code, I get a #Value! error, but if I change the first line to be If DOC < DateSerial(1989,12,1) And ..., the code works fine.

I'm wanting to avoid running this as a Sub, but I'm wanting to see if anyone knows a workaround.

Thanks in advance!

 

[VoG]

Perhaps Tools > Options, Calculation tab, set Iterations to 1.

 

[MrKowz]

Tried that, sadly still returns #Value!. (Neither scenario threw up a circular-reference error btw).

 

[Scott Huish]

Why do you need a circular reference though?

 

[MrKowz]

See if trying it out gives you a better idea of what I'm needing to do:

Option Explicit
Public Function DateOfParticipation(DOB As Range, DOE As Range, DOC As Range, LOC As Range)
Dim uDOB        As Date, _
    uDOE        As Date, _
    uDOC        As Date, _
    TempPart    As Date, _
    i           As Long
If DOB.Value = vbNullString Or DOE = vbNullString Or DOC = vbNullString Then
    DateOfParticipation = ""
    Exit Function
End If
uDOB = DateSerial(Year(DOB - 1) + 21, Month(DOB - 1) + 1, 1)
uDOE = DateSerial(Year(DOE - 1), Month(DOE - 1) + 1, 1)
uDOC = DateSerial(Year(DOC - 1), Month(DOC - 1) + 1, 1)
 
TempPart = Application.Max(uDOB, uDOE, uDOC)
If TempPart< DateSerial(2008, 11, 18) Then
    If DOC<= DateSerial(1989, 12, 1) And (InStr(UCase$(LOC.Value), "SEA") > 0 Or InStr(UCase$(LOC.Value), "BOARDWALK") > 0 Or InStr(UCase$(LOC.Value), "CYPRESS") > 0) Then
        DateOfParticipation = DateSerial(1990, 1, 1)
        MsgBox "The Date of Credited Service has been adjusted to 12/1/1989", vbOKOnly, "Date of Credited Service"
        DOC.Value = DateSerial(1989, 12, 1)
    Else
        DateOfParticipation = TempPart
    End If
Else
    DateOfParticipation = DateSerial(2999, 12, 31)
    MsgBox "Client is not eligible for pension." & vbLf & "Their participation date of " & TempPart & " is beyond the change in control date of 11/18/2008."
End If
End Function

Under the following scenario, B3 (Date of Credited Service) should be changed to 12/1/89, Date of Participation should read 1/1/90, and a message box should pop up indicating that the change was made. However, when I try the code, I get the message box, but no changes.

Excel Workbook
	A	B
1	Date of Birth	12/8/1967
2	Date of Employment	11/23/1987
3	Date of Credited Service	7/1/1988
4	Location	Sea
5	Date of Participation	#VALUE!
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B5	=dateofparticipation(B1,B2,B3,B4)

Thanks!

Edit - @Hotpepper - My choice of words in Circular Reference may not have been the best way to explain this situation. Hopefully the above will clarify my situation.

 

[jasonb75]

That reads as though you're trying to edit the content of another cell, which is a no-no with a Function, same as trying to invoke a message box if I remember correctly.

Maybe a calculate event to pick up on the error? That would allow the message box, mikerickson did provide a workaround method for the cell value change within a Function, but there would be no way to make the user aware.

 

[MrKowz]

That reads as though you're trying to edit the content of another cell, which is a no-no with a Function, same as trying to invoke a message box if I remember correctly.

Maybe a calculate event to pick up on the error? That would allow the message box, mikerickson did provide a workaround method for the cell value change within a Function, but there would be no way to make the user aware.

Hmm - I hadn't thought about the scenario when it stays 12/1/89, and the worksheet recalculates. Ugh - looks like I'm going to have to do this with a sub.

Thanks for the insight!

 

[jasonb75]

Just found that 'workaround' that mikerickson provided, post #2

http://www.mrexcel.com/forum/showthread.php?t=347940

may be of some use.