Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual calculation setting | Excel Discussion (Misc queries) | |||
circular reference fix without manual copy / paste | Excel Discussion (Misc queries) | |||
Setting Manual Calculation on Specific Cells Only | Excel Discussion (Misc queries) | |||
Excel caluclation setting reverts back to manual when I close Exce | Setting up and Configuration of Excel | |||
Help getting around circular referencing | Excel Discussion (Misc queries) |