Hello,

I am a novice at intententional circular references...so I have a
question. I currently have the following coded function and I am
passing certain values into the function, and have followed the
execution of the function to see that it executes correctly up to the
point where the function returns the value (in this case 35). This
seems to be working correctly when tracing the variables within the
VBA editor. The problem arises when I return to the Excel sheet
calling the function, the content in the L13 cell then says zero all
the time, even though it seems that the function is correctly
returning 35.

I have a cell L13 that contains the function call of
=DetermineBalanceValue(D13, L13, L$31, 'Core Tasks Used'!$H$7, L$10, L
$11, L$12,$L23), where the values passed in are (35, <<empty first
time, 0,0.18292, 5, 68, 45, 10)

Does someone recommend something I can read, or try perhaps
differently? I have reviewed all references within the Excel group
and have not found this particular case.....

Much appreciated
regards,
Enzo


The function is defined as follows:

Public Function DetermineBalanceValue(lOldValue As Range,
lModifiedValue As Range, lBalanceValue As Range, lPercentValue As
Range, l1Value As Range, l2Value As Range, l3Value As Range, l4Value
As Range) As Integer
'
''''Application.Iteration = True
Dim updatedvalue As Integer
Application.EnableEvents = False
On Error GoTo ws_exit
' Determine the row for the key indicator required
lOld = CInt(lOldValue.Value)
lBalance = CInt(lBalanceValue.Value)
lPercent = lPercentValue.Value
lModified = lModifiedValue.Value
l1 = l1Value.Value
l2 = l2Value.Value
l3 = l3Value.Value
l4 = l4Value.Value

If (IsEmpty(lModified)) Then
'do nothing use original Old value
Else
If (lOld < lModified) Then
' use the already updated value - or it will revert back
to the original values
lOld = lModified
End If
End If

updatedvalue = CInt(Round(lPercent * (l1 + l2 + l3 + l4), 0))
If (lBalance = 0) Then
If (lPercent * (l1 + l2 + l3 + l4) = lOld) Then
DetermineBalanceValue = updatedvalue
Else
DetermineBalanceValue = lOld
End If
Else
If (lPercent * (l1 + l2 + l3 + l4) = lOld) Then
DetermineBalanceValue = lOld
Else
DetermineBalanceValue = updatedvalue
End If
End If

ws_exit:
Application.EnableEvents = True

End Function