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

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

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

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

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