Loading Excel Array from VB Array Faster

[ExcelMonkey]

I am loading an excel array (1000x244)from a VB array using a ForNext
Loop. I load the VBA array (ArrrayThing) with random numbers and then
pass to excel using an offset to a named cell (PasteCell) within a
loop.

I takes about 22 seconds on my 1.0 GHz machine. I have ScreenUpdateing
= Fase and the Calc is off. I want ot speed this up. Since my excel
array and VBA array have the same dimenions (1000x244), is it possible
to fill the excel array without looping? That is if I call the excel
array EntireArray, can I then pass the entire contents of the VBA array
into it without looping?

Option Base 1
Sub ArrayThing()
Dim DataArray(1 To 1000, 1 To 244) As Variant
Dim StartTime As Date
Dim EndTime As Date
Dim FinalTime As Double
Dim RandomVar As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("EntireArray").Clear

StartTime = Now()
Randomize
For A = 1 To 1000
For B = 1 To 244

RandomVar = Rnd()
DataArray(A, B) = RandomVar
Next B
Next A

For C = 1 To 1000
For D = 1 To 244
Range("PasteCell").Offset(C - 1, D - 1) = DataArray(C, D)
Next D
Next C
EndTime = Now()
FinalTime = EndTime - StartTime
MsgBox ("The model run was completed in " & Format(FinalTime,
"hh:mm:ss") & ".")
End Sub

 

[ExcelMonkey]

I just answered my own question. I replaced the offset to the named
cell within the loop with :

Range("EntireArray") = DataArray

Works fine.


Option Base 1
Sub ArrayThing()
Dim DataArray(1 To 1000, 1 To 244) As Variant
Dim StartTime As Date
Dim EndTime As Date
Dim FinalTime As Double
Dim RandomVar As Double

Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual

Range("EntireArray").Clear

StartTime = Now()
For A = 1 To 1000
For B = 1 To 244
Randomize
RandomVar = Rnd()
DataArray(A, B) = RandomVar
Next B
Next A

Range("EntireArray") = DataArray

EndTime = Now()
FinalTime = EndTime - StartTime
MsgBox ("The model run was completed in " & Format(FinalTime,
"hh:mm:ss") & ".")
End Sub

 

[Beto]

I takes about 22 seconds on my 1.0 GHz machine. I have ScreenUpdateing
= Fase and the Calc is off. I want ot speed this up. Since my excel
array and VBA array have the same dimenions (1000x244), is it possible
to fill the excel array without looping? That is if I call the excel
array EntireArray, can I then pass the entire contents of the VBA array
into it without looping?

Yes you can, just assign it. I didn'u use the pastecell reference so
change this to suit your needs. This is an example.

Option Base 1
Sub ArrayThing()
Dim DataArray(1 To 1000, 1 To 244) As Variant
Dim StartTime As Date
Dim EndTime As Date
Dim FinalTime As Double
Dim RandomVar As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("EntireArray").Clear

StartTime = Now()
Randomize
For A = 1 To 1000
For B = 1 To 244

RandomVar = Rnd()
DataArray(A, B) = RandomVar
Next B
Next A

Range("EntireArray") = DataArray

EndTime = Now()
FinalTime = EndTime - StartTime
MsgBox ("The model run was completed in " & Format(FinalTime,
"hh:mm:ss") & ".")
End Sub


Regards,

 

[Dana DeLouis]

Just some ideas if you are not sure of the size of the Array.

Sub ArrayThing()
'//Small Demo...
Dim DataArray(1 To 10, 1 To 5) As Variant
Dim R As Long 'Row
Dim C As Long 'Column

Randomize
For R = 1 To UBound(DataArray, 1)
For C = 1 To UBound(DataArray, 2)
DataArray(R, C) = Rnd
Next C
Next R

Range("A1").Resize(UBound(DataArray, 1), UBound(DataArray, 2)) =
DataArray
End Sub