Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect any Volatile UDF
If a UDF in any open workbook contains Application.Volatile, my code breaks off to complete the UDF when my code does anything that causes a recalc, eg putting a value in a cell (even into ThisWorkbook which does not contain any UDF). This causes problems: - Sometimes when doing the UDF my code terminates. This is more serious than a normal failure - code does not get reset leaving unwanted global variables. (Comments in this NG suggest this occurs when there is an error in the UDF. That's certainly true, but sometimes and inconsistently occurs even without any apparent error.) - A lot of UDF's could slow my code, not so serious but inconvenient. Temporarily setting calculation to manual prevents problems. However I neither need nor want to do this except when absolutely necessary. But I do need to cater for the unknown possibility of volatile UDF's lurking. These problems occur in XL97, I gather (this NG) later versions are less susceptible, if I could be confident(?) these do not present an issue I would code for different versions. So what I would like to do is test for the existence of anything volatile before setting calculation to manual. But how? I've thought of early in my code to put a dummy cell value into ThisWorkbook, and try to: - Detect if my code has broken off to do any UDF, or - Detect if Excel is doing a recalc (apart from the dummy cell input). However I don't think it's possible to detect either. Any other ideas or suggestions! TIA for any advice, Sandy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect any Volatile UDF
Hi Sandy,
I do not know of a way of detecting volatile UDFs. Since you are using XL97 you will solve a number of your problems if you add both an error handler and an ISEMPTY handler to your UDFs: for details and examples see http://www.DecisionModels.com/calcsecretsj.htm Another approach you might consider is to set a global flag in your code, and then check the flag in each UDF. But IMHO the best and simplest approach is to control application.calculation in your code: not sure why you do not want to do this. regards Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Sandy V" wrote in message ... If a UDF in any open workbook contains Application.Volatile, my code breaks off to complete the UDF when my code does anything that causes a recalc, eg putting a value in a cell (even into ThisWorkbook which does not contain any UDF). This causes problems: - Sometimes when doing the UDF my code terminates. This is more serious than a normal failure - code does not get reset leaving unwanted global variables. (Comments in this NG suggest this occurs when there is an error in the UDF. That's certainly true, but sometimes and inconsistently occurs even without any apparent error.) - A lot of UDF's could slow my code, not so serious but inconvenient. Temporarily setting calculation to manual prevents problems. However I neither need nor want to do this except when absolutely necessary. But I do need to cater for the unknown possibility of volatile UDF's lurking. These problems occur in XL97, I gather (this NG) later versions are less susceptible, if I could be confident(?) these do not present an issue I would code for different versions. So what I would like to do is test for the existence of anything volatile before setting calculation to manual. But how? I've thought of early in my code to put a dummy cell value into ThisWorkbook, and try to: - Detect if my code has broken off to do any UDF, or - Detect if Excel is doing a recalc (apart from the dummy cell input). However I don't think it's possible to detect either. Any other ideas or suggestions! TIA for any advice, Sandy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect any Volatile UDF
Hi Charles,
First of all I regard your excellent site as my first port of call for anything to do with calculation - thanks for providing it. My code is for distribution to others and I have no knowledge or control over their UDF's, if indeed they have any. Hence I cannot take the precautions you suggest re UDF's [control calculation] not sure why you do not want to do this There are various minor reasons which would take a convoluted explanation to describe. But one reason is an unpredicted failure of my code would leave the user's calculation mode changed. However for XL97 I will follow your advice and set calculation to manual. Could I also ask you to confirm either way if same is necessary or recommended for later versions. Many thanks, Sandy savituk yahoo co uk -----Original Message----- Hi Sandy, I do not know of a way of detecting volatile UDFs. Since you are using XL97 you will solve a number of your problems if you add both an error handler and an ISEMPTY handler to your UDFs: for details and examples see http://www.DecisionModels.com/calcsecretsj.htm Another approach you might consider is to set a global flag in your code, and then check the flag in each UDF. But IMHO the best and simplest approach is to control application.calculation in your code: not sure why you do not want to do this. regards Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Sandy V" wrote in message ... If a UDF in any open workbook contains Application.Volatile, my code breaks off to complete the UDF when my code does anything that causes a recalc, eg putting a value in a cell (even into ThisWorkbook which does not contain any UDF). This causes problems: - Sometimes when doing the UDF my code terminates. This is more serious than a normal failure - code does not get reset leaving unwanted global variables. (Comments in this NG suggest this occurs when there is an error in the UDF. That's certainly true, but sometimes and inconsistently occurs even without any apparent error.) - A lot of UDF's could slow my code, not so serious but inconvenient. Temporarily setting calculation to manual prevents problems. However I neither need nor want to do this except when absolutely necessary. But I do need to cater for the unknown possibility of volatile UDF's lurking. These problems occur in XL97, I gather (this NG) later versions are less susceptible, if I could be confident (?) these do not present an issue I would code for different versions. So what I would like to do is test for the existence of anything volatile before setting calculation to manual. But how? I've thought of early in my code to put a dummy cell value into ThisWorkbook, and try to: - Detect if my code has broken off to do any UDF, or - Detect if Excel is doing a recalc (apart from the dummy cell input). However I don't think it's possible to detect either. Any other ideas or suggestions! TIA for any advice, Sandy . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect any Volatile UDF
Hi Sandy,
[control calculation] not sure why you do not want to do this There are various minor reasons which would take a convoluted explanation to describe. But one reason is an unpredicted failure of my code would leave the user's calculation mode changed. I would: - save the current calculation mode - trap errors with an on error handler - restore calculation mode if neccessary in the error handler However for XL97 I will follow your advice and set calculation to manual. Could I also ask you to confirm either way if same is necessary or recommended for later versions. Excel 97 has a particular problem with UDFs with errors so it is worse than other versions. In general I would recommend going to manual for other versions as well, but it depends on the circumstances. regds Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com Many thanks, Sandy savituk yahoo co uk -----Original Message----- Hi Sandy, I do not know of a way of detecting volatile UDFs. Since you are using XL97 you will solve a number of your problems if you add both an error handler and an ISEMPTY handler to your UDFs: for details and examples see http://www.DecisionModels.com/calcsecretsj.htm Another approach you might consider is to set a global flag in your code, and then check the flag in each UDF. But IMHO the best and simplest approach is to control application.calculation in your code: not sure why you do not want to do this. regards Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Sandy V" wrote in message ... If a UDF in any open workbook contains Application.Volatile, my code breaks off to complete the UDF when my code does anything that causes a recalc, eg putting a value in a cell (even into ThisWorkbook which does not contain any UDF). This causes problems: - Sometimes when doing the UDF my code terminates. This is more serious than a normal failure - code does not get reset leaving unwanted global variables. (Comments in this NG suggest this occurs when there is an error in the UDF. That's certainly true, but sometimes and inconsistently occurs even without any apparent error.) - A lot of UDF's could slow my code, not so serious but inconvenient. Temporarily setting calculation to manual prevents problems. However I neither need nor want to do this except when absolutely necessary. But I do need to cater for the unknown possibility of volatile UDF's lurking. These problems occur in XL97, I gather (this NG) later versions are less susceptible, if I could be confident (?) these do not present an issue I would code for different versions. So what I would like to do is test for the existence of anything volatile before setting calculation to manual. But how? I've thought of early in my code to put a dummy cell value into ThisWorkbook, and try to: - Detect if my code has broken off to do any UDF, or - Detect if Excel is doing a recalc (apart from the dummy cell input). However I don't think it's possible to detect either. Any other ideas or suggestions! TIA for any advice, Sandy . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect any Volatile UDF
Hi Charles,
I would: - save the current calculation mode - trap errors with an on error handler - restore calculation mode if necessary in the error handler I am already saving the original calculation mode. Currently I restore this just before my code terminates in the normal way. However there is a potential problem even with this - setting calculation back to automatic triggers an immediate recalc, if a UDF error occurs my code terminates early. I am thinking to put the restore code into a separate sub, calling it with OnTime to be done when my normal code has run it's course. I am unclear as to how to set up an error handler in my code to trap for errors in unknown UDF's in other WB's. Establishing an error handler just before anything in my code that causes a recalc followed by a UDF error get's lost! In general I would recommend going to manual for other versions as well Ok I will go with this as "circumstances" outside my WB are unknown, thanks. Sandy savituk yahoo co uk -----Original Message----- Hi Sandy, [control calculation] not sure why you do not want to do this There are various minor reasons which would take a convoluted explanation to describe. But one reason is an unpredicted failure of my code would leave the user's calculation mode changed. I would: - save the current calculation mode - trap errors with an on error handler - restore calculation mode if neccessary in the error handler However for XL97 I will follow your advice and set calculation to manual. Could I also ask you to confirm either way if same is necessary or recommended for later versions. Excel 97 has a particular problem with UDFs with errors so it is worse than other versions. In general I would recommend going to manual for other versions as well, but it depends on the circumstances. regds Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com Many thanks, Sandy savituk yahoo co uk -----Original Message----- Hi Sandy, I do not know of a way of detecting volatile UDFs. Since you are using XL97 you will solve a number of your problems if you add both an error handler and an ISEMPTY handler to your UDFs: for details and examples see http://www.DecisionModels.com/calcsecretsj.htm Another approach you might consider is to set a global flag in your code, and then check the flag in each UDF. But IMHO the best and simplest approach is to control application.calculation in your code: not sure why you do not want to do this. regards Charles ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Sandy V" wrote in message ... If a UDF in any open workbook contains Application.Volatile, my code breaks off to complete the UDF when my code does anything that causes a recalc, eg putting a value in a cell (even into ThisWorkbook which does not contain any UDF). This causes problems: - Sometimes when doing the UDF my code terminates. This is more serious than a normal failure - code does not get reset leaving unwanted global variables. (Comments in this NG suggest this occurs when there is an error in the UDF. That's certainly true, but sometimes and inconsistently occurs even without any apparent error.) - A lot of UDF's could slow my code, not so serious but inconvenient. Temporarily setting calculation to manual prevents problems. However I neither need nor want to do this except when absolutely necessary. But I do need to cater for the unknown possibility of volatile UDF's lurking. These problems occur in XL97, I gather (this NG) later versions are less susceptible, if I could be confident (?) these do not present an issue I would code for different versions. So what I would like to do is test for the existence of anything volatile before setting calculation to manual. But how? I've thought of early in my code to put a dummy cell value into ThisWorkbook, and try to: - Detect if my code has broken off to do any UDF, or - Detect if Excel is doing a recalc (apart from the dummy cell input). However I don't think it's possible to detect either. Any other ideas or suggestions! TIA for any advice, Sandy . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
volatile v non-volatile | Excel Discussion (Misc queries) | |||
Today - volatile function | Excel Discussion (Misc queries) | |||
Volatile Symbol | Excel Discussion (Misc queries) | |||
INDEX - volatile or not? | Excel Worksheet Functions | |||
VBE Geeks in Volatile Functions | Excel Worksheet Functions |