Array to range problem..

[Tommy Bak]

Hi
With reference to http://www.mrexcel.com/board/viewtop...790&forum=2&11

i have modified Jay's code so it will read all the values into an array(65536,1) and each time the code has reached 65536 items it will dump these into a range/entire column.
BUT IT IS SO SLOW.
The code itself is fast enough, but transforming the array into a range of cells is slow.
If nums are set to 22 it takes 5 secs to run the code but 2 minutes to transform it.
This means that if i set nums to 42 (about 58 columns)it will take about 2 hour to run compared to 6 minutes to a textfile.
Can explain this ??
I've got 400 MB ram, so that should not be the problem.

Sub test3()
Dim a, b, c, d, e, f, nums, maxdiff As Byte
Dim LoadCol As Integer
Dim x As String
Dim Counter, TCounter As Long
'Dim Tarray As Variant
Dim Tarray() As String
Start = Timer
Application.ScreenUpdating = False

nums = 20
maxdiff = 15
LoadCol = 1
LastRow = 65536
'REDIM THE ARRAY TO HOLD 1 ENTIRE COLUMN
ReDim Tarray(LastRow, 1)

For a = 1 To nums - 5
For b = a + 1 To WorksheetFunction.Min(a + maxdiff, nums - 4)
For c = b + 1 To WorksheetFunction.Min(b + maxdiff, nums - 3)
For d = c + 1 To WorksheetFunction.Min(c + maxdiff, nums - 2)
For e = d + 1 To WorksheetFunction.Min(d + maxdiff, nums - 1)
For f = e + 1 To WorksheetFunction.Min(e + maxdiff, nums)
Counter = Counter + 1
TCounter = TCounter + 1
'LOAD THE ARRAY
Tarray(TCounter, 1) = a & "," & b & "," & c & "," & d & "," & e & "," & f
If TCounter = LastRow Then
'FILL THE WHOLE COLUMN AND RESET
Range(Cells(1, LoadCol), Cells(LastRow, LoadCol)) = Tarray
LoadCol = LoadCol + 1
TCounter = 0
Application.StatusBar = LoadCol
End If
Next f
Next e
Next d
Next c
Next b
Next a
'FILL THE LAST COLUMN
Range(Cells(1, LoadCol), Cells(TCounter, LoadCol)) = Tarray
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox "Processing complete" & vbCr & Format(Counter, "#,##0") & " entries loaded in " & Timer - Start & "secs"
End Sub

[Jay Petrulis]

Hi Tommy,

I am working on the same thing from a slightly different angle...

I am trying to determine the number of columns needed (=COMBIN(n,r)/65536) and then dimension an array (1 to 65536, 1 to cols).

The cols part should use the INT to get full columns and the MOD to get the partial column.

That way I can use:
Range("A1").Resize(65536,cols) = MyArray

I still need to test it a bit, though. Possibly you can take it and run with it.

Bye,
Jay

[ This Message was edited by: Jay Petrulis on 2002-05-21 16:53 ]

[Ivan F Moala]

On 2002-05-21 16:49, Jay Petrulis wrote:
Hi Tommy,

I am working on the same thing from a slightly different angle...

I am trying to determine the number of columns needed (=COMBIN(n,r)/65536) and then dimension an array (1 to 65536, 1 to cols).

The cols part should use the INT to get full columns and the MOD to get the partial column.

That way I can use:
Range("A1").Resize(65536,cols) = MyArray

I still need to test it a bit, though. Possibly you can take it and run with it.

Bye,
Jay

[ This Message was edited by: Jay Petrulis on 2002-05-21 16:53 ]

Jays Syntax for array reading / writting is
the way to go. What you should always endeavour to do is Cut down on the Reads & writes ie. instead of looping 10000 cells eg

For x = 1 to 500
for y = 1 to 20 etc you would use

Dim A as Variant
A = Range("yourrange").Resize(500,20) 'reads 500x20 array starting at range yourrange.

'to write back to sheet
Range("SomeRange").Resize(500,20) = A

What you have effectively done is cut the
read/write down from 1000 to ONE!
Note: The array A MUST BE A VARIANT, do not assign it the (), should also be 2D even if you are only writing one column or row.
The 1st dim is used for rows
The 2nd for columns.

[Tommy Bak]

Thanks for your repplies Jay and Ivan.
The reason that I only copy one column each time is becaurse I'm not able to determine the array size properly
Combin(42,6)= 5.245.789 (approx. 81 columns)
but with the max difference set 15 the actual no of items is 3.774.976 (approx. 58 columns.
This is quite a big array, which I assumed was what made my computer so slow.

I'll try out your solutions and return on the matter.

regards Tommy

[Jay Petrulis]

Aha! I've confirmed it below...

The =ROUNDUP(COMBIN(42,6)/65536,0) will give the upper bound for the array columns, but you don't have to fill it completely to write the entire thing to the worksheet. The unfilled array elements will just return a blank cell.

Sub test()
Dim x, y, MyArray

ReDim MyArray(1 To 10, 1 To 4)
For x = 1 To 10
    For y = 1 To 2
        If x > y Then MyArray(x, y) = x & "," & y
        MyArray(x, 4) = MyArray(x, 1)
    Next y
Next x

Range("A1").Resize(10, 4) = MyArray
End Sub

Play around with the above and you can ReDim the array (1 to 65536, 1 to 81) and only the 1st through 58th columns or so will contain data.

This may not be the most efficient in terms of array size, but you will *not* have to use a counter within your loop and ReDim preserve the previous array each pass through to add the next combination.

I think the main problem remaining is to assign the first 65536 elements to array(abc, 1). It shouldn't bee too difficult, but I haven't done it yet, so I may be talking nonsense, here.

Keep us posted on your progress. When I get some time, I will play around with this a bit more.

Bye,
Jay

EDIT: The problem with this approach is that it will overwrite any cells in the blank fields, which could pose a problem.

[ This Message was edited by: Jay Petrulis on 2002-05-22 05:55 ]