Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Range Name affecting speed of macro
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 __________________________ |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Range Name affecting speed of macro
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!) |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Range Name affecting speed of macro
On 28 Apr 2006 05:01:34 -0700, "Pete_UK" 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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Range Name affecting speed of macro
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" wrote in message ... On 28 Apr 2006 05:01:34 -0700, "Pete_UK" 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 __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Range Name affecting speed of macro
On Fri, 28 Apr 2006 17:16:17 +0100, "Charles Williams"
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" wrote in message ... On 28 Apr 2006 05:01:34 -0700, "Pete_UK" 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 __________________________ |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Range Name affecting speed of macro
well theres nothing slow about that.
Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Richard Buttrey" wrote in message ... On Fri, 28 Apr 2006 17:16:17 +0100, "Charles Williams" 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" wrote in message ... On 28 Apr 2006 05:01:34 -0700, "Pete_UK" 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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying a Selected Range in a Macro | Excel Discussion (Misc queries) | |||
Macro to Highlight Range | Excel Discussion (Misc queries) | |||
format macro | Excel Discussion (Misc queries) | |||
Macro - define cell range for a sum function | Excel Discussion (Misc queries) | |||
Macro for Show/Hide Range | Excel Discussion (Misc queries) |