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:

Sub MIRRORrange22()

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

Range("rangeo100") = rank

End Sub

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:

Sub mirrorRANGE()
Dim rank(1 To 200, 1 To 200) As Single

For rowx = 1 To 200
    For colx = 1 To 200
        rank(rowx, colx) = Rnd()
    Next colx
Next rowx

Call SuperBlastArrayToSheet(rank, Range("rango100"))

End Sub

Workaround sub is as below

Sub SuperBlastArrayToSheet(TheArray As Variant, TheRange As Range)
  With TheRange.Parent.Parent 'the workbook the range is in
    .Names.Add Name:="wstempdata", RefersToR1C1:=TheArray
    With TheRange
      .FormulaArray = "=wstempdata"
      .Copy
      .PasteSpecial Paste:=xlValues
    End With
    .Names("wstempdata").Delete
  End With
End Sub