circular referencing of formulae when setting calculation back toautomatic from manual
 

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

circular referencing of formulae when setting calculation back to automatic from manual
 

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.

--

Regards,
Nigel




"Yuvraj" wrote in message
...
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

circular referencing of formulae when setting calculation back toautomatic from manual
 

On Apr 17, 11:45*am, "Nigel" wrote:
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.

--

Regards,
Nigel


"Yuvraj" wrote in message

...



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- Hide quoted text -

- 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