Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF recalc problem between sheets
My UDF "Func1" accepts a range array as one of its arg's
and passes this to "Func2" for intermediate processing, works fine, except... If I cause a recalc of udf in a formula on Sheet2 correct result is returned. However if I switch to Sheet1, cells with this udf show errors. If I recalc (F9) the errors revert to correct results. Calculation is automatic and nothing "volatile" in the wb. What appears to be happening is when I recalc the udf in Sheet2, udf formulas on Sheet1 are also recalculated, but the array arg that relates to the udf on Sheet2 is passed to "Func2" when the udf's on Sheet1 are being calculated, hence the errors. Recalc on Sheet1 corrects, and udf's in formulas on Sheet2 remain correct. I don't think there is a circular issue. Debug.? .Caller.Address shows udf's in Sheet1 are calculated before those in Sheet2, even if I initiate the recalc in Sheet2. Re-ordering the sheets, and/or renaming in different alphabetical order makes no difference. And finally, initiating a recalc on any other sheet, even one without udf's, causes errors in udf's Sheet1 but not in Sheet2. (Same scenario in XL97 & XL2K) Hope this is not too convoluted! TIA for any suggestions Sandy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF recalc problem between sheets
Hi Sandy
you may have to reference the parent object of your range. Could you post the code of your UDF -- Regards Frank Kabel Frankfurt, Germany "Sandy V" schrieb im Newsbeitrag ... My UDF "Func1" accepts a range array as one of its arg's and passes this to "Func2" for intermediate processing, works fine, except... If I cause a recalc of udf in a formula on Sheet2 correct result is returned. However if I switch to Sheet1, cells with this udf show errors. If I recalc (F9) the errors revert to correct results. Calculation is automatic and nothing "volatile" in the wb. What appears to be happening is when I recalc the udf in Sheet2, udf formulas on Sheet1 are also recalculated, but the array arg that relates to the udf on Sheet2 is passed to "Func2" when the udf's on Sheet1 are being calculated, hence the errors. Recalc on Sheet1 corrects, and udf's in formulas on Sheet2 remain correct. I don't think there is a circular issue. Debug.? .Caller.Address shows udf's in Sheet1 are calculated before those in Sheet2, even if I initiate the recalc in Sheet2. Re-ordering the sheets, and/or renaming in different alphabetical order makes no difference. And finally, initiating a recalc on any other sheet, even one without udf's, causes errors in udf's Sheet1 but not in Sheet2. (Same scenario in XL97 & XL2K) Hope this is not too convoluted! TIA for any suggestions Sandy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF recalc problem between sheets
Could be a problem with the UDF not handling uncalculated cells in the
range. Have a look at http://www.DecisionModels.com/calcsecretsj.htm regds Charles ______________________ Decision Models FastExcel Version 2 now available. www.DecisionModels.com/FxlV2WhatsNew.htm "Sandy V" wrote in message ... My UDF "Func1" accepts a range array as one of its arg's and passes this to "Func2" for intermediate processing, works fine, except... If I cause a recalc of udf in a formula on Sheet2 correct result is returned. However if I switch to Sheet1, cells with this udf show errors. If I recalc (F9) the errors revert to correct results. Calculation is automatic and nothing "volatile" in the wb. What appears to be happening is when I recalc the udf in Sheet2, udf formulas on Sheet1 are also recalculated, but the array arg that relates to the udf on Sheet2 is passed to "Func2" when the udf's on Sheet1 are being calculated, hence the errors. Recalc on Sheet1 corrects, and udf's in formulas on Sheet2 remain correct. I don't think there is a circular issue. Debug.? .Caller.Address shows udf's in Sheet1 are calculated before those in Sheet2, even if I initiate the recalc in Sheet2. Re-ordering the sheets, and/or renaming in different alphabetical order makes no difference. And finally, initiating a recalc on any other sheet, even one without udf's, causes errors in udf's Sheet1 but not in Sheet2. (Same scenario in XL97 & XL2K) Hope this is not too convoluted! TIA for any suggestions Sandy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF recalc problem between sheets
Charles,
I had actually looked at your page before posting, first thing I thought of and thanks. Would not be at all surprised if it contains the answer and I just can't see it for looking. IsEmpty does not flag anything re uncalculated cells, assuming I'm checking correctly. Both my udf/Func1 and Func2 are error handled. Frank, Parent Object, I'll work on this. The input range array is purely to get the row/col size of a single block of cells, not necessarily the actual block I'm going to process. In Func1 I set a new range var of same size but with the address of Cell(1,1) obtained by other means. This new range may or may not be on the same sheet as the udf formula. Then I pass the new range to Func2 which is where the error occurs. All computed ranges relating to my udf in Sheet1 are on same sheet. The udf in Sheet2 processs ranges in different sheets. But it's the udf in Sheet1 that gets the error, unless recalc is initiated in Sheet1 in which case no errors occur anywhere. Maybe I need to be more careful about the Sheet (ie parent). I'm not sure that posting the code would help without seeing it in situ in the wb, which would take an even more convoluted explanation to describe! Thank you both, Sandy -----Original Message----- Hi Sandy you may have to reference the parent object of your range. Could you post the code of your UDF -- Regards Frank Kabel Frankfurt, Germany "Sandy V" schrieb im Newsbeitrag ... My UDF "Func1" accepts a range array as one of its arg's and passes this to "Func2" for intermediate processing, works fine, except... If I cause a recalc of udf in a formula on Sheet2 correct result is returned. However if I switch to Sheet1, cells with this udf show errors. If I recalc (F9) the errors revert to correct results. Calculation is automatic and nothing "volatile" in the wb. What appears to be happening is when I recalc the udf in Sheet2, udf formulas on Sheet1 are also recalculated, but the array arg that relates to the udf on Sheet2 is passed to "Func2" when the udf's on Sheet1 are being calculated, hence the errors. Recalc on Sheet1 corrects, and udf's in formulas on Sheet2 remain correct. I don't think there is a circular issue. Debug.? .Caller.Address shows udf's in Sheet1 are calculated before those in Sheet2, even if I initiate the recalc in Sheet2. Re-ordering the sheets, and/or renaming in different alphabetical order makes no difference. And finally, initiating a recalc on any other sheet, even one without udf's, causes errors in udf's Sheet1 but not in Sheet2. (Same scenario in XL97 & XL2K) Hope this is not too convoluted! TIA for any suggestions Sandy . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF recalc problem between sheets
Hi Sandy
if you like mail me your example file: email: frank[dot]kabel[at]freenet[dot]de -- Regards Frank Kabel Frankfurt, Germany "Sandy V" schrieb im Newsbeitrag ... Charles, I had actually looked at your page before posting, first thing I thought of and thanks. Would not be at all surprised if it contains the answer and I just can't see it for looking. IsEmpty does not flag anything re uncalculated cells, assuming I'm checking correctly. Both my udf/Func1 and Func2 are error handled. Frank, Parent Object, I'll work on this. The input range array is purely to get the row/col size of a single block of cells, not necessarily the actual block I'm going to process. In Func1 I set a new range var of same size but with the address of Cell(1,1) obtained by other means. This new range may or may not be on the same sheet as the udf formula. Then I pass the new range to Func2 which is where the error occurs. All computed ranges relating to my udf in Sheet1 are on same sheet. The udf in Sheet2 processs ranges in different sheets. But it's the udf in Sheet1 that gets the error, unless recalc is initiated in Sheet1 in which case no errors occur anywhere. Maybe I need to be more careful about the Sheet (ie parent). I'm not sure that posting the code would help without seeing it in situ in the wb, which would take an even more convoluted explanation to describe! Thank you both, Sandy -----Original Message----- Hi Sandy you may have to reference the parent object of your range. Could you post the code of your UDF -- Regards Frank Kabel Frankfurt, Germany "Sandy V" schrieb im Newsbeitrag ... My UDF "Func1" accepts a range array as one of its arg's and passes this to "Func2" for intermediate processing, works fine, except... If I cause a recalc of udf in a formula on Sheet2 correct result is returned. However if I switch to Sheet1, cells with this udf show errors. If I recalc (F9) the errors revert to correct results. Calculation is automatic and nothing "volatile" in the wb. What appears to be happening is when I recalc the udf in Sheet2, udf formulas on Sheet1 are also recalculated, but the array arg that relates to the udf on Sheet2 is passed to "Func2" when the udf's on Sheet1 are being calculated, hence the errors. Recalc on Sheet1 corrects, and udf's in formulas on Sheet2 remain correct. I don't think there is a circular issue. Debug.? .Caller.Address shows udf's in Sheet1 are calculated before those in Sheet2, even if I initiate the recalc in Sheet2. Re-ordering the sheets, and/or renaming in different alphabetical order makes no difference. And finally, initiating a recalc on any other sheet, even one without udf's, causes errors in udf's Sheet1 but not in Sheet2. (Same scenario in XL97 & XL2K) Hope this is not too convoluted! TIA for any suggestions Sandy . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF recalc problem between sheets
well, it does sound like either a calculation sequence problem or that the
UDF is somehow referring to the active sheet when it shouldnt be. some things it could be: - you are using Application.evaluate in one of your functions - you have a multi-area range as input - you have duplicate global and local names, or multiple local names - you have a name that starts with ! - INDIRECT? - you have an unqualified range reference somewhere. maybe if you post the UDF code and the formula that calls it ... regards Charles ______________________ Decision Models FastExcel Version 2 now available. www.DecisionModels.com/FxlV2WhatsNew.htm "Sandy V" wrote in message ... Charles, I had actually looked at your page before posting, first thing I thought of and thanks. Would not be at all surprised if it contains the answer and I just can't see it for looking. IsEmpty does not flag anything re uncalculated cells, assuming I'm checking correctly. Both my udf/Func1 and Func2 are error handled. Frank, Parent Object, I'll work on this. The input range array is purely to get the row/col size of a single block of cells, not necessarily the actual block I'm going to process. In Func1 I set a new range var of same size but with the address of Cell(1,1) obtained by other means. This new range may or may not be on the same sheet as the udf formula. Then I pass the new range to Func2 which is where the error occurs. All computed ranges relating to my udf in Sheet1 are on same sheet. The udf in Sheet2 processs ranges in different sheets. But it's the udf in Sheet1 that gets the error, unless recalc is initiated in Sheet1 in which case no errors occur anywhere. Maybe I need to be more careful about the Sheet (ie parent). I'm not sure that posting the code would help without seeing it in situ in the wb, which would take an even more convoluted explanation to describe! Thank you both, Sandy -----Original Message----- Hi Sandy you may have to reference the parent object of your range. Could you post the code of your UDF -- Regards Frank Kabel Frankfurt, Germany "Sandy V" schrieb im Newsbeitrag ... My UDF "Func1" accepts a range array as one of its arg's and passes this to "Func2" for intermediate processing, works fine, except... If I cause a recalc of udf in a formula on Sheet2 correct result is returned. However if I switch to Sheet1, cells with this udf show errors. If I recalc (F9) the errors revert to correct results. Calculation is automatic and nothing "volatile" in the wb. What appears to be happening is when I recalc the udf in Sheet2, udf formulas on Sheet1 are also recalculated, but the array arg that relates to the udf on Sheet2 is passed to "Func2" when the udf's on Sheet1 are being calculated, hence the errors. Recalc on Sheet1 corrects, and udf's in formulas on Sheet2 remain correct. I don't think there is a circular issue. Debug.? .Caller.Address shows udf's in Sheet1 are calculated before those in Sheet2, even if I initiate the recalc in Sheet2. Re-ordering the sheets, and/or renaming in different alphabetical order makes no difference. And finally, initiating a recalc on any other sheet, even one without udf's, causes errors in udf's Sheet1 but not in Sheet2. (Same scenario in XL97 & XL2K) Hope this is not too convoluted! TIA for any suggestions Sandy . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF recalc problem between sheets
Hi
as a follow up: Sandy sent me her file and the original reason for this was the creation of a range without a worksheet qualifier. Therefore the range was always created based on the active sheet. -- Regards Frank Kabel Frankfurt, Germany Charles Williams wrote: well, it does sound like either a calculation sequence problem or that the UDF is somehow referring to the active sheet when it shouldnt be. some things it could be: - you are using Application.evaluate in one of your functions - you have a multi-area range as input - you have duplicate global and local names, or multiple local names - you have a name that starts with ! - INDIRECT? - you have an unqualified range reference somewhere. maybe if you post the UDF code and the formula that calls it ... regards Charles ______________________ Decision Models FastExcel Version 2 now available. www.DecisionModels.com/FxlV2WhatsNew.htm "Sandy V" wrote in message ... Charles, I had actually looked at your page before posting, first thing I thought of and thanks. Would not be at all surprised if it contains the answer and I just can't see it for looking. IsEmpty does not flag anything re uncalculated cells, assuming I'm checking correctly. Both my udf/Func1 and Func2 are error handled. Frank, Parent Object, I'll work on this. The input range array is purely to get the row/col size of a single block of cells, not necessarily the actual block I'm going to process. In Func1 I set a new range var of same size but with the address of Cell(1,1) obtained by other means. This new range may or may not be on the same sheet as the udf formula. Then I pass the new range to Func2 which is where the error occurs. All computed ranges relating to my udf in Sheet1 are on same sheet. The udf in Sheet2 processs ranges in different sheets. But it's the udf in Sheet1 that gets the error, unless recalc is initiated in Sheet1 in which case no errors occur anywhere. Maybe I need to be more careful about the Sheet (ie parent). I'm not sure that posting the code would help without seeing it in situ in the wb, which would take an even more convoluted explanation to describe! Thank you both, Sandy -----Original Message----- Hi Sandy you may have to reference the parent object of your range. Could you post the code of your UDF -- Regards Frank Kabel Frankfurt, Germany "Sandy V" schrieb im Newsbeitrag ... My UDF "Func1" accepts a range array as one of its arg's and passes this to "Func2" for intermediate processing, works fine, except... If I cause a recalc of udf in a formula on Sheet2 correct result is returned. However if I switch to Sheet1, cells with this udf show errors. If I recalc (F9) the errors revert to correct results. Calculation is automatic and nothing "volatile" in the wb. What appears to be happening is when I recalc the udf in Sheet2, udf formulas on Sheet1 are also recalculated, but the array arg that relates to the udf on Sheet2 is passed to "Func2" when the udf's on Sheet1 are being calculated, hence the errors. Recalc on Sheet1 corrects, and udf's in formulas on Sheet2 remain correct. I don't think there is a circular issue. Debug.? .Caller.Address shows udf's in Sheet1 are calculated before those in Sheet2, even if I initiate the recalc in Sheet2. Re-ordering the sheets, and/or renaming in different alphabetical order makes no difference. And finally, initiating a recalc on any other sheet, even one without udf's, causes errors in udf's Sheet1 but not in Sheet2. (Same scenario in XL97 & XL2K) Hope this is not too convoluted! TIA for any suggestions Sandy . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF recalc problem between sheets
Since Frank's last post there have been one or two more
off-line exchanges between us. I'm very grateful to his help. A bit more followup: Frank was absolutely correct about a range not being fully referenced with the sheet. Charles similarly and also for another reason (see below). However this was not something lacking in the code but an inadequate way of passing details of the range to the function. The location of the ranges to be processed are identified as a strings contained in another range of cells which is an input argument. If the range tb processed is on the same sheet as the udf formula, it's top left cell was identified simply as (say) "$A$1". But if on another sheet as (say) "Sheet!3$A$1". My udf formula on Sheet1 was only concerned about processing ranges on same sheet, hence the strings only included the cell address. My formula in Sheet2 used ranges from other sheets, so addresses were fully referenced with Sheet-name ! cell-address. All worked well until I introduced INDIRECT in cell formulas (Charles - my eyes popped out when I read Indirect in your post). This being a Volatile function would trigger recalc on Sheet1 even if recalc initiated on another sheet. Now because my range addresses as strings did not include sheet names in Sheet1, the range became incorrectly referenced to the Active sheet, and hence error only in the Sheet1 udf. Ironically INDIRECT caused a recalc problem which fortuitously highlighted a bad approach to my input method, which otherwise could have gone unnoticed for a long time. Many thanks again to Frank and Charles. Sandy PS Frank alluded to my being a "her" whereas I am in fact a "him" :-) -----Original Message----- Hi as a follow up: Sandy sent me her file and the original reason for this was the creation of a range without a worksheet qualifier. Therefore the range was always created based on the active sheet. -- Regards Frank Kabel Frankfurt, Germany Charles Williams wrote: well, it does sound like either a calculation sequence problem or that the UDF is somehow referring to the active sheet when it shouldnt be. some things it could be: - you are using Application.evaluate in one of your functions - you have a multi-area range as input - you have duplicate global and local names, or multiple local names - you have a name that starts with ! - INDIRECT? - you have an unqualified range reference somewhere. maybe if you post the UDF code and the formula that calls it ... regards Charles ______________________ Decision Models FastExcel Version 2 now available. www.DecisionModels.com/FxlV2WhatsNew.htm "Sandy V" wrote in message ... Charles, I had actually looked at your page before posting, first thing I thought of and thanks. Would not be at all surprised if it contains the answer and I just can't see it for looking. IsEmpty does not flag anything re uncalculated cells, assuming I'm checking correctly. Both my udf/Func1 and Func2 are error handled. Frank, Parent Object, I'll work on this. The input range array is purely to get the row/col size of a single block of cells, not necessarily the actual block I'm going to process. In Func1 I set a new range var of same size but with the address of Cell(1,1) obtained by other means. This new range may or may not be on the same sheet as the udf formula. Then I pass the new range to Func2 which is where the error occurs. All computed ranges relating to my udf in Sheet1 are on same sheet. The udf in Sheet2 processs ranges in different sheets. But it's the udf in Sheet1 that gets the error, unless recalc is initiated in Sheet1 in which case no errors occur anywhere. Maybe I need to be more careful about the Sheet (ie parent). I'm not sure that posting the code would help without seeing it in situ in the wb, which would take an even more convoluted explanation to describe! Thank you both, Sandy -----Original Message----- Hi Sandy you may have to reference the parent object of your range. Could you post the code of your UDF -- Regards Frank Kabel Frankfurt, Germany "Sandy V" schrieb im Newsbeitrag ... My UDF "Func1" accepts a range array as one of its arg's and passes this to "Func2" for intermediate processing, works fine, except... If I cause a recalc of udf in a formula on Sheet2 correct result is returned. However if I switch to Sheet1, cells with this udf show errors. If I recalc (F9) the errors revert to correct results. Calculation is automatic and nothing "volatile" in the wb. What appears to be happening is when I recalc the udf in Sheet2, udf formulas on Sheet1 are also recalculated, but the array arg that relates to the udf on Sheet2 is passed to "Func2" when the udf's on Sheet1 are being calculated, hence the errors. Recalc on Sheet1 corrects, and udf's in formulas on Sheet2 remain correct. I don't think there is a circular issue. Debug.? .Caller.Address shows udf's in Sheet1 are calculated before those in Sheet2, even if I initiate the recalc in Sheet2. Re-ordering the sheets, and/or renaming in different alphabetical order makes no difference. And finally, initiating a recalc on any other sheet, even one without udf's, causes errors in udf's Sheet1 but not in Sheet2. (Same scenario in XL97 & XL2K) Hope this is not too convoluted! TIA for any suggestions Sandy . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't stop recalc | Excel Worksheet Functions | |||
cell does not recalc | Excel Worksheet Functions | |||
Recalc Options in 2003? | Excel Discussion (Misc queries) | |||
Automatic Recalc | Excel Worksheet Functions | |||
recalc question | Excel Programming |