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 <ryguy7...@discussions.microsoft.com>
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,
Similar Threads
