Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
avoid recalculating user-defined function when file opened
Hello,
I use Excel 2003 on Windows XP (SP2?) Is there a way to not let Excel to recalculate values for the cells which contains user defined function? When i open a file that contains my user defined function (which exists in the module of the file which has the function), it mommentallily shows "#NAME?" in the sheet and then the value pop up in less than a second. That is fine if i have only one cell with formula, but I intend to use the cell many places. I want to excel to remember the value when the file got saved/closed, and do not recalculate unless its precedents got changed. Just like any native Excel functions behave. As a note, I dont have application.volatile in my user-defined function, and I don't want to change automatic calculation property of the excel it self (I want always keep it on) Thank you very much, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
avoid recalculating user-defined function when file opened
AFAIK, that's just normal behavior. Tou must have a HUGE workbook.
See this: http://www.mvps.org/dmcritchie/excel/slowresp.htm Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "yosuke kimura" wrote: Hello, I use Excel 2003 on Windows XP (SP2?) Is there a way to not let Excel to recalculate values for the cells which contains user defined function? When i open a file that contains my user defined function (which exists in the module of the file which has the function), it mommentallily shows "#NAME?" in the sheet and then the value pop up in less than a second. That is fine if i have only one cell with formula, but I intend to use the cell many places. I want to excel to remember the value when the file got saved/closed, and do not recalculate unless its precedents got changed. Just like any native Excel functions behave. As a note, I dont have application.volatile in my user-defined function, and I don't want to change automatic calculation property of the excel it self (I want always keep it on) Thank you very much, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
avoid recalculating user-defined function when file opened
Thank you for the link. Some of the link in there was famous one, like
Chip Pearson, Dermot Balson, William Mercer. I still am looking for answer to my question , why my user-defined function (UDF) is called when file is get opened. It turned out, I think, that the problem is not coming from the use of UDF. But the argument was the problem. I tried four different ways to call my function from Sheet1 =MyFunc(Sheet2!A1:C8) =MyFunc(Sheet2!A1:C8*1) =MyFunc(OFFSET(Sheet2!A1,0,0,8,3)) =MyFunc(OFFSET(Sheet2!A1,0,0,8,3)*1) And then have a following Debug.Print "called: " + Application.ThisCell.Address When the argument uses OFFSET, it got called when the file get opened. In fact, the problem was even worse. Any change in anywhere in Excel file appears to trigger the function.... Seems that using OFFSET function makes Excel to make overly safe assumption of dependency, and keep executing the function to be safe? I wanted to use OFFSET to grab some portion of data (which is on Sheet2 in this example) and do statistics. It now seems to be that I should avoid use of OFFSET, instead of UDF. Is this something which is known (use of OFFSET trigger calculations a lot more)? Or is my finding somehow biased? Thanks, On Mar 31, 8:29*pm, ryguy7272 wrote: AFAIK, that's just normal behavior. *Tou must have a HUGE workbook. * See this:http://www.mvps.org/dmcritchie/excel/slowresp.htm Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''.. "yosuke kimura" wrote: Hello, I use Excel 2003 on Windows XP (SP2?) Is there a way to not let Excel to recalculate values for the cells which contains user defined function? When *i open a file that contains my user defined function (which exists in the module of the file which has the function), it mommentallily shows "#NAME?" in the sheet and then the value pop up in less than a second. *That is fine if i have only one cell with formula, *but I intend to use the cell many places. *I want to excel to remember the value when the file got saved/closed, and do not recalculate unless its precedents got changed. *Just like any native Excel functions behave. As a note, I dont have application.volatile in my user-defined function, and I don't want to change automatic calculation property of the excel it self (I want always keep it on) Thank you very much, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User-defined functions not calculating/recalculating | Excel Programming | |||
User-defined functions not calculating/recalculating | Excel Programming | |||
User-defined functions not calculating/recalculating | Excel Programming | |||
Add help or help file to a user defined function? | Excel Programming | |||
Stopping User Defined Functions for Recalculating | Excel Programming |