Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using rng.Value & RefersToRange to pass data between worksheets - SLOW!
I am writing a VBA application that identifies a named cell in Workbook1 and
then passes a value from an Array in Workbook2 to the named cells in Workbook1. There are from 1 to 10 items in the Array. When all the items have been passed, Workbook1 is recalculated and a named range is passed back to Workbook2. The code works fine, but it is VERY SLOW. Can someone suggest an alternative method for passing the Array data into the named cells? The code I am using looks like this: Public uNameArray() ReDim uNameArray(1 to 1, 1 to NumberOfArrayItems) For y = 1 to NumberOfArrayItems 'Get the cell name from the Array uName = uNameArray(1, y) 'Set the value of the named cell in Workbook 1 equal to SomeVAlue Set rng.Value = Workbook1.Names(uName).RefersToRange rng.Value = SomeValue Next y I would appreciate any help from the group. Frank |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using rng.Value & RefersToRange to pass data between worksheets - SLOW!
Turn off calculation until you finish passing your values.
-- Regards, Tom Ogilvy "Frank & Pam Hayes" wrote in message news:Fwn6d.17171$M45.16971@trndny09... I am writing a VBA application that identifies a named cell in Workbook1 and then passes a value from an Array in Workbook2 to the named cells in Workbook1. There are from 1 to 10 items in the Array. When all the items have been passed, Workbook1 is recalculated and a named range is passed back to Workbook2. The code works fine, but it is VERY SLOW. Can someone suggest an alternative method for passing the Array data into the named cells? The code I am using looks like this: Public uNameArray() ReDim uNameArray(1 to 1, 1 to NumberOfArrayItems) For y = 1 to NumberOfArrayItems 'Get the cell name from the Array uName = uNameArray(1, y) 'Set the value of the named cell in Workbook 1 equal to SomeVAlue Set rng.Value = Workbook1.Names(uName).RefersToRange rng.Value = SomeValue Next y I would appreciate any help from the group. Frank |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using rng.Value & RefersToRange to pass data between worksheets - SLOW!
Tom,
I should have been more explicit, but I do have this on an earlier line: Application.Calculation = xlCalculateManual Any other thoughts? "Tom Ogilvy" wrote in message ... Turn off calculation until you finish passing your values. -- Regards, Tom Ogilvy "Frank & Pam Hayes" wrote in message news:Fwn6d.17171$M45.16971@trndny09... I am writing a VBA application that identifies a named cell in Workbook1 and then passes a value from an Array in Workbook2 to the named cells in Workbook1. There are from 1 to 10 items in the Array. When all the items have been passed, Workbook1 is recalculated and a named range is passed back to Workbook2. The code works fine, but it is VERY SLOW. Can someone suggest an alternative method for passing the Array data into the named cells? The code I am using looks like this: Public uNameArray() ReDim uNameArray(1 to 1, 1 to NumberOfArrayItems) For y = 1 to NumberOfArrayItems 'Get the cell name from the Array uName = uNameArray(1, y) 'Set the value of the named cell in Workbook 1 equal to SomeVAlue Set rng.Value = Workbook1.Names(uName).RefersToRange rng.Value = SomeValue Next y I would appreciate any help from the group. Frank |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using rng.Value & RefersToRange to pass data between worksheets - SLOW!
Application.Calculation = xlCalculateManual
I assume that was a typo in your email: ? xlCalculateManual ? xlCalculationManual -4135 xlCalculationManual would be the correct constant. If you have to put values in a bunch of different non-contiguous cells, I am not sure how else you would approach it but by identifying each of the locations and placing the value. -- Regards, Tom Ogilvy "Frank & Pam Hayes" wrote in message news:QHn6d.3952$6f.490@trndny02... Tom, I should have been more explicit, but I do have this on an earlier line: Application.Calculation = xlCalculateManual Any other thoughts? "Tom Ogilvy" wrote in message ... Turn off calculation until you finish passing your values. -- Regards, Tom Ogilvy "Frank & Pam Hayes" wrote in message news:Fwn6d.17171$M45.16971@trndny09... I am writing a VBA application that identifies a named cell in Workbook1 and then passes a value from an Array in Workbook2 to the named cells in Workbook1. There are from 1 to 10 items in the Array. When all the items have been passed, Workbook1 is recalculated and a named range is passed back to Workbook2. The code works fine, but it is VERY SLOW. Can someone suggest an alternative method for passing the Array data into the named cells? The code I am using looks like this: Public uNameArray() ReDim uNameArray(1 to 1, 1 to NumberOfArrayItems) For y = 1 to NumberOfArrayItems 'Get the cell name from the Array uName = uNameArray(1, y) 'Set the value of the named cell in Workbook 1 equal to SomeVAlue Set rng.Value = Workbook1.Names(uName).RefersToRange rng.Value = SomeValue Next y I would appreciate any help from the group. Frank |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using rng.Value & RefersToRange to pass data between worksheets - SLOW!
Tom,
It was a typo ... you are correct. I tested the code further and it does not seem to be the calculation that is slowing it down. Rather the Set rng and and rng.value lines are what is taking all the time. Since I cycle through this code for three possible values for every combination of up to 9 varaibles (3 ^ 9), this is killing the application. I will keep working on it! Frank "Tom Ogilvy" wrote in message ... Application.Calculation = xlCalculateManual I assume that was a typo in your email: ? xlCalculateManual ? xlCalculationManual -4135 xlCalculationManual would be the correct constant. If you have to put values in a bunch of different non-contiguous cells, I am not sure how else you would approach it but by identifying each of the locations and placing the value. -- Regards, Tom Ogilvy "Frank & Pam Hayes" wrote in message news:QHn6d.3952$6f.490@trndny02... Tom, I should have been more explicit, but I do have this on an earlier line: Application.Calculation = xlCalculateManual Any other thoughts? "Tom Ogilvy" wrote in message ... Turn off calculation until you finish passing your values. -- Regards, Tom Ogilvy "Frank & Pam Hayes" wrote in message news:Fwn6d.17171$M45.16971@trndny09... I am writing a VBA application that identifies a named cell in Workbook1 and then passes a value from an Array in Workbook2 to the named cells in Workbook1. There are from 1 to 10 items in the Array. When all the items have been passed, Workbook1 is recalculated and a named range is passed back to Workbook2. The code works fine, but it is VERY SLOW. Can someone suggest an alternative method for passing the Array data into the named cells? The code I am using looks like this: Public uNameArray() ReDim uNameArray(1 to 1, 1 to NumberOfArrayItems) For y = 1 to NumberOfArrayItems 'Get the cell name from the Array uName = uNameArray(1, y) 'Set the value of the named cell in Workbook 1 equal to SomeVAlue Set rng.Value = Workbook1.Names(uName).RefersToRange rng.Value = SomeValue Next y I would appreciate any help from the group. Frank |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 slow changing between worksheets | Excel Discussion (Misc queries) | |||
Pass word protecting multipul worksheets within a workbook | Excel Discussion (Misc queries) | |||
Pass array of worksheets to ActiveX DLL (VB6) | Excel Programming | |||
slow sorting worksheets | Excel Programming | |||
How do I pass a worksheets name to function? | Excel Programming |