circular referencing of formulae when setting calculation back toautomatic from manual

[Yuvraj]

Public Function jsPW(ByVal pWk As Integer) As String

Dim pd%, wk%

' Supply pWk as LIP Calendar number (1 - 156)

On Error GoTo hndErr

wk% = 1 + ((pWk - 1) Mod 4)
pd% = 1 + (Int((pWk - wk%) / 4) Mod 13)

jsPW = "Pd." & pd% & " Wk." & wk%

Exit Function

hndErr:
jsPW = "-"

End Function


Problem: This function is used in many sheets of the application where
in the formulae bar we have =jspw($E$645) and the cell 645 has some
value. this formulae is used in 8 cells.

When in the code i set back to automatic generally this loop runs 8
times for other reports but this sepecific report keeps on running
this and hangs.

I am not knowing how to get the answer as i do not know why it is
continuosly looping.

How to break the code to cells which are calling it and using it when
the calculation is set to automatic.

Regards,

Prince

 

[Nigel]

I am not sure it is the function causing the error?

There are no loops and the function simply converts the value passed into a
string.

Check your sheet formulae and references. Excel should highlight / warn
ciruclar references in sheet formula.

 

[Yuvraj]

I am not sure it is the function causing the error?

There are no loops and the function simply converts the value passed intoa
string.

Check your sheet formulae and references.  Excel should highlight / warn
ciruclar references in sheet formula.

--

Regards,
Nigel
(e-mail address removed)


















- Show quoted text -

Hi Nigel,

Circular referencing is what i m thinking.
But ideally this is happening only in excel2003 and not in excel2000.

Same code loops only 8 times when calculation is set to autamatic
after manuial, but in excel 2003 it goes to infinite loop