Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Excel Array from VB Array Faster
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Excel Array from VB Array Faster
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 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Excel Array from VB Array Faster
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 -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "ExcelMonkey " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
Which is faster sum(if) as an array or sumproduct? | New Users to Excel | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Loading Excel Arrange into VBA array | Excel Programming | |||
Loading 3 Dimensional Array | Excel Programming |