Using rng.Value & RefersToRange to pass data between worksheets - SLOW!

[Frank & Pam Hayes]

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

 

[Tom Ogilvy]

Turn off calculation until you finish passing your values.

 

[Frank & Pam Hayes]

Tom,

I should have been more explicit, but I do have this on an earlier line:

Application.Calculation = xlCalculateManual

Any other thoughts?

 

[Tom Ogilvy]

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.

 

[Frank & Pam Hayes]

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