I have a set of templates that we use to check/calculate costs from
invoices using a set of user defined functions (nested if's beyond the
normal limit).

I had these set up as local (each sheet uses a different set of functions,
so it made it easier for me to navigate/maintain), but I was having trouble
whenever I needed to copy or move a sheet (ie we routinely check over 100K
records at a time so multiple worksheets are a necessity). If I needed to
do this, excel was throwing up error messages in relation to my UDF's local
to that sheet.

In an effort to get around this I have converted them to global, but this
seems to incur a significant performance penalty, ie the size has blown out
considerably, and the load times are woefully slow (i assume due to
recalculation)

Can anyone advise me whether or not this is a viable strategy to resolve my
initial problem (or point me at a helpful resource), or if I should stick
with my original solution)

TIA

S

--
2 + 2 will never equal 5 (not even for very large values of 2...)

Can you tell what is meant by loc al and global UDFs, not a term I am
familiar with.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Simon" wrote in message
...
I have a set of templates that we use to check/calculate costs from
invoices using a set of user defined functions (nested if's beyond the
normal limit).

I had these set up as local (each sheet uses a different set of functions,
so it made it easier for me to navigate/maintain), but I was having
trouble
whenever I needed to copy or move a sheet (ie we routinely check over 100K
records at a time so multiple worksheets are a necessity). If I needed to
do this, excel was throwing up error messages in relation to my UDF's
local
to that sheet.

In an effort to get around this I have converted them to global, but this
seems to incur a significant performance penalty, ie the size has blown
out
considerably, and the load times are woefully slow (i assume due to
recalculation)

Can anyone advise me whether or not this is a viable strategy to resolve
my
initial problem (or point me at a helpful resource), or if I should stick
with my original solution)

TIA

S

--
2 + 2 will never equal 5 (not even for very large values of 2...)

In our last episode Bob Phillips wrote:

Can you tell what is meant by loc al and global UDFs, not a term I am
familiar with.


OK, a bit more info (my frustration probably got the better of me with my
original post...)

size wise the 2 files are almost the same, and I'm in the process of doing
some more testing (basically it was taking about 20 minutes (plus!) just to
open a workbook containing 3 sheets and about 150K rows of data - about 10
columns of source data and 13 calculated), and I had several to work with
that day

You can define your user defined functions as global (ie useable from any
sheet in a workbook) or local (only useable from within the worksheet they
are defined in).

To add a local UDF, add the worksheet name to the UDF name, eg sheet1!Cost
(if your sheet name has spaces you need to enclose the sheet name in
commas, ie 'sheet 1!'cost)

FYI, an excellent tool for managing named ranges/functions is (strangely
enough) Name Manager - an Excel addin that's much easier to use than the
Excel name definition dialog box

get it here http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp

S

Simon

I could be wrong, but it looks to me like you have a misunderstanding about the
difference between UDF's which are user defined functions and cannot be local or
global and range names which can be local or global.

Example of a UDF..........

Function ShowFormula(cell)
ShowFormula = "No Formula"
If cell.HasFormula Then ShowFormula = cell.Formula
End Function


Gord Dibben MS Excel MVP

On Tue, 26 Sep 2006 23:31:42 +0000 (UTC), Simon wrote:

In our last episode Bob Phillips wrote:

Can you tell what is meant by loc al and global UDFs, not a term I am
familiar with.


OK, a bit more info (my frustration probably got the better of me with my
original post...)

size wise the 2 files are almost the same, and I'm in the process of doing
some more testing (basically it was taking about 20 minutes (plus!) just to
open a workbook containing 3 sheets and about 150K rows of data - about 10
columns of source data and 13 calculated), and I had several to work with
that day

You can define your user defined functions as global (ie useable from any
sheet in a workbook) or local (only useable from within the worksheet they
are defined in).

To add a local UDF, add the worksheet name to the UDF name, eg sheet1!Cost
(if your sheet name has spaces you need to enclose the sheet name in
commas, ie 'sheet 1!'cost)

FYI, an excellent tool for managing named ranges/functions is (strangely
enough) Name Manager - an Excel addin that's much easier to use than the
Excel name definition dialog box

get it here http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp

S

Gord

could well be a misunderstanding on my part, sorry if this caused any
confusion...

what I've done is add a defined name, but instead of using a range, I've used
a formula.

works the same (kind of) but has the advantage (when used locally) of
confining use of the function(s) to the relevant worksheets

S

This doesn't clarify your problem any, apart from showing that you seem to
be confused between UDFs and names as Gord says, but seems more of an answer
to a problem.

What is the question?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Simon" wrote in message
...
In our last episode Bob Phillips wrote:

Can you tell what is meant by loc al and global UDFs, not a term I am
familiar with.


OK, a bit more info (my frustration probably got the better of me with my
original post...)

size wise the 2 files are almost the same, and I'm in the process of doing
some more testing (basically it was taking about 20 minutes (plus!) just
to
open a workbook containing 3 sheets and about 150K rows of data - about 10
columns of source data and 13 calculated), and I had several to work with
that day

You can define your user defined functions as global (ie useable from any
sheet in a workbook) or local (only useable from within the worksheet they
are defined in).

To add a local UDF, add the worksheet name to the UDF name, eg sheet1!Cost
(if your sheet name has spaces you need to enclose the sheet name in
commas, ie 'sheet 1!'cost)

FYI, an excellent tool for managing named ranges/functions is (strangely
enough) Name Manager - an Excel addin that's much easier to use than the
Excel name definition dialog box

get it here http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp

S