Range Name affecting speed of macro

[Richard Buttrey]

I posted a question back in March, (thread "Loop time seems dependent
on unrelated workbook - Why?"), and have finally managed to identify
the problem area but still don't know what is causing it.

Hence this is more a general request for suggestions as to the cause,
since I have now put in a work around.

The problem was this. I have three workbooks.

In Book1 (which contains about 600 range names), a database sheet,
(which contains no formulae or names) is fed from another working
'paper' sheet. This database sheet is first output as an independent
file to create a second workbook.

Book 2 is used as a data import feed to a database sheet in a third
workbook. (Book2 is also used by other applications which is why I
don't feed directly from book1 to book3)

Book 3 contains amongst others, a database sheet, (fed by a copy and
paste from Book2), which is used by various other report template
sheets.

The 3 Workbooks. contain no links or range names in common.

The third reporting workbook has various macros which filter and
summarise data, hide rows, export data files and various other stuff,
based on a user choosing from various criteria.

This reporting workbook had been working fine for 18 months or so, but
last month it started a go slow campaign. A macro which typically
took about 30-45 seconds to process started taking many minutes.

I finally highlighted the problem data.
The first reporting Wb contains about 600 range names. The working
paper sheet and the database sheet (which is filtered from the working
paper sheet), each have circa 2000 rows of data. By iteratively
deleting half the range names in Book1, exporting the data to the 2nd
Wb. importing to the 3rd Wb, running the macro in the third Wb,
noting the time the macro took, and then either deleting half the
remaining names in Book1, or re-opening Book1 and deleting the other
half of the names, I eventually found one particular range name which
was causing the slow running. When I deleted just this one name from
Book1, everything was back to normal. (Deleting it and then
re-creating it did not solve the problem).

I am totally perplexed why this one name in Book1 should cause a
problem in what is essentially an independent, (no links or common
names) Book3.

The only other thing I should add is that the problem only exists when
Book1 is open in memory at the same time as Book3.

I'd be grateful for any ideas or suggestions as to what may be going
on here.

Usual TIA.




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[broro183]

Hi,
Please post the formula used in the troublesome named range definition.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

[Pete_UK]

When you copy a sheet from one workbook to another, any named ranges
which are used by the sheet go with it (still referenced back to the
original workbook). So, in creating your second workbook (you don't
describe how), you may well be taking some of the named ranges from
Book1. If you then copy and paste this into Book 3, your ranges will
also be transfered with it.

I don't know why one particular range should cause the slowdown, but it
might be worth examining how you create Book 2 and think of
alternatives to copy/paste.

Hope this helps.

Pete (from Woolston!)

[Richard Buttrey]

On 28 Apr 2006 05:01:34 -0700, "Pete_UK" <pashurst@auditel.net> wrote:

>When you copy a sheet from one workbook to another, any named ranges
>which are used by the sheet go with it (still referenced back to the
>original workbook). So, in creating your second workbook (you don't
>describe how), you may well be taking some of the named ranges from
>Book1. If you then copy and paste this into Book 3, your ranges will
>also be transfered with it.
>
>I don't know why one particular range should cause the slowdown, but it
>might be worth examining how you create Book 2 and think of
>alternatives to copy/paste.
>
>Hope this helps.
>
>Pete (from Woolston!)

Hello Neighbour :-)

Thanks for this suggestion Pete.

When I look at the intermediate book2, it is devoid of any range names
- as I expect.

Book 2 is created by a copy and paste from the datasheet in Book1. On
that data sheet neither are there any names local to the sheet.

Neither can I see any hidden names (using the excellent Name Manager
3.2 fromJan Karel Pieterse).

So at the moment I'm still stumped although all the facts point to
there being some sort of a problem with a particular name.

:-(



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Charles Williams]

What is the refersto formula of the name that causes the problem?

If you select it in Name Manager and click the Evaluate button how long does
it take before the answer appears?


Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:n45452lid61m1mh0kpcn667iap2p91sn69@4ax.com...
> On 28 Apr 2006 05:01:34 -0700, "Pete_UK" <pashurst@auditel.net> wrote:
>
>>When you copy a sheet from one workbook to another, any named ranges
>>which are used by the sheet go with it (still referenced back to the
>>original workbook). So, in creating your second workbook (you don't
>>describe how), you may well be taking some of the named ranges from
>>Book1. If you then copy and paste this into Book 3, your ranges will
>>also be transfered with it.
>>
>>I don't know why one particular range should cause the slowdown, but it
>>might be worth examining how you create Book 2 and think of
>>alternatives to copy/paste.
>>
>>Hope this helps.
>>
>>Pete (from Woolston!)
>
> Hello Neighbour :-)
>
> Thanks for this suggestion Pete.
>
> When I look at the intermediate book2, it is devoid of any range names
> - as I expect.
>
> Book 2 is created by a copy and paste from the datasheet in Book1. On
> that data sheet neither are there any names local to the sheet.
>
> Neither can I see any hidden names (using the excellent Name Manager
> 3.2 fromJan Karel Pieterse).
>
> So at the moment I'm still stumped although all the facts point to
> there being some sort of a problem with a particular name.
>
> :-(
>
>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

[Richard Buttrey]

On Fri, 28 Apr 2006 17:16:17 +0100, "Charles Williams"
<Charles@DecisionModels.com> wrote:

Charles, Thanks for taking the time to consider this problem.
I'm still as perplexed as ever.


>What is the refersto formula of the name that causes the problem?

The actual name is 'Main Working Paper'!$HW$53:$HW$1499

>If you select it in Name Manager and click the Evaluate button how long does
>it take before the answer appears?

It evaluates instantly.


Rgds


>
>Charles
>______________________
>Decision Models
>FastExcel 2.2 Beta now available
>www.DecisionModels.com
>
>"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
>message news:n45452lid61m1mh0kpcn667iap2p91sn69@4ax.com...
>> On 28 Apr 2006 05:01:34 -0700, "Pete_UK" <pashurst@auditel.net> wrote:
>>
>>>When you copy a sheet from one workbook to another, any named ranges
>>>which are used by the sheet go with it (still referenced back to the
>>>original workbook). So, in creating your second workbook (you don't
>>>describe how), you may well be taking some of the named ranges from
>>>Book1. If you then copy and paste this into Book 3, your ranges will
>>>also be transfered with it.
>>>
>>>I don't know why one particular range should cause the slowdown, but it
>>>might be worth examining how you create Book 2 and think of
>>>alternatives to copy/paste.
>>>
>>>Hope this helps.
>>>
>>>Pete (from Woolston!)
>>
>> Hello Neighbour :-)
>>
>> Thanks for this suggestion Pete.
>>
>> When I look at the intermediate book2, it is devoid of any range names
>> - as I expect.
>>
>> Book 2 is created by a copy and paste from the datasheet in Book1. On
>> that data sheet neither are there any names local to the sheet.
>>
>> Neither can I see any hidden names (using the excellent Name Manager
>> 3.2 fromJan Karel Pieterse).
>>
>> So at the moment I'm still stumped although all the facts point to
>> there being some sort of a problem with a particular name.
>>
>> :-(
>>
>>
>>
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Charles Williams]

well theres nothing slow about that.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:e967521pnrji1jik4b3a9gh0knvbk0unfn@4ax.com...
> On Fri, 28 Apr 2006 17:16:17 +0100, "Charles Williams"
> <Charles@DecisionModels.com> wrote:
>
> Charles, Thanks for taking the time to consider this problem.
> I'm still as perplexed as ever.
>
>
>>What is the refersto formula of the name that causes the problem?
>
> The actual name is 'Main Working Paper'!$HW$53:$HW$1499
>
>>If you select it in Name Manager and click the Evaluate button how long
>>does
>>it take before the answer appears?
>
> It evaluates instantly.
>
>
> Rgds
>
>
>>
>>Charles
>>______________________
>>Decision Models
>>FastExcel 2.2 Beta now available
>>www.DecisionModels.com
>>
>>"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
>>message news:n45452lid61m1mh0kpcn667iap2p91sn69@4ax.com...
>>> On 28 Apr 2006 05:01:34 -0700, "Pete_UK" <pashurst@auditel.net> wrote:
>>>
>>>>When you copy a sheet from one workbook to another, any named ranges
>>>>which are used by the sheet go with it (still referenced back to the
>>>>original workbook). So, in creating your second workbook (you don't
>>>>describe how), you may well be taking some of the named ranges from
>>>>Book1. If you then copy and paste this into Book 3, your ranges will
>>>>also be transfered with it.
>>>>
>>>>I don't know why one particular range should cause the slowdown, but it
>>>>might be worth examining how you create Book 2 and think of
>>>>alternatives to copy/paste.
>>>>
>>>>Hope this helps.
>>>>
>>>>Pete (from Woolston!)
>>>
>>> Hello Neighbour :-)
>>>
>>> Thanks for this suggestion Pete.
>>>
>>> When I look at the intermediate book2, it is devoid of any range names
>>> - as I expect.
>>>
>>> Book 2 is created by a copy and paste from the datasheet in Book1. On
>>> that data sheet neither are there any names local to the sheet.
>>>
>>> Neither can I see any hidden names (using the excellent Name Manager
>>> 3.2 fromJan Karel Pieterse).
>>>
>>> So at the moment I'm still stumped although all the facts point to
>>> there being some sort of a problem with a particular name.
>>>
>>> :-(
>>>
>>>
>>>
>>> __
>>> Richard Buttrey
>>> Grappenhall, Cheshire, UK
>>> __________________________
>>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________