Loading Excel Array from VB Array Faster
ExcelMonkey < wrote:
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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.
|