avoid recalculating user-defined function when file opened

[yosuke kimura]

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,

 

[ryguy7272]

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---

 

[yosuke kimura]

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,