mirror arrays

[vasyr]

I have learned that " set arrayX=range("xxxx") " allows me to handle the range as an array where I can fill values.... arrayx(3,2)=valuex automatically write valuex in the third row and second column of range("xxxx") as if it were a mirror of the array... My question is:

For speed purposes on large worksheets it is much faster to program on an array than through formulas on a worhsheet range. I have tried to recover array values to the worksheet using FormulaArray but it is very slow... do you know a better way to transfer values from an array in memory to a range on a worksheet....

Let me illustrate...in real case Rnd() can be any formula

Sub MIRRORrange()

Dim rank(1 To 200, 1 To 200)


For rowx = 1 To 200
For colx = 1 To 200
rank(rowx, colx) = Rnd()
Next colx
Next rowx
Application.ScreenUpdating = False
range("results").FormulaArray = rank
Application.ScreenUpdating = True

End Sub

Calculation of rank takes less than one second, retrieving it more than one and a half minute.

Thank you

[stevebriz]

can you post your xls...I would like test to see why it is taking so long.

[vasyr]

here it is, thanks for any help.

[Myles]

Why not simply use:

Please Login or Register  to view this content.

where "rangeo100" is the required named range. Screenupdating setting is not necessary.

[vasyr]

I tried it naming a range(200x200) in the workshet...

I got a debug dialog box saying:

Run-time error '1004':
Method 'Range' of object '_Global' failed

that is why I tried FormulaArray, and decided to deactivate
screen updating as this method transfers values from rank to range
cell by cell slowing down unacceptably the worksheet.

If I define a range(1x1) to receive values of rank, the i get in the Worksheet only rank(1,1).

[vasyr]

I tried it naming a range(200x200) in the workshet...

I got a debug dialog box saying:

Run-time error '1004':
Method 'Range' of object '_Global' failed
for Range("range100")=rank

that is why I tried FormulaArray, and decided to deactivate
screen updating as this method transfers values from rank to range
cell by cell slowing down unacceptably the worksheet.

If I define a range(1x1) to receive values of rank, the i get in the Worksheet only rank(1,1).

[stevebriz]

I did a bit od seraching on why the formulaarray is slow and found a workaround..with another sub at
http://www.avdf.com/apr98/art_ot003.html
so I change you sub and added the workaround as below:

Please Login or Register  to view this content.

Workaround sub is as below

Please Login or Register  to view this content.

[Myles]

Vas,

I don't seem to see your problem. I tried my code on your worksheet with and without named range with no error message. Works flawlessly.

[vasyr]

Steve:

WOW!!! It works really like magic.
Thank you very, very, very much, I will try to undestand why it works.