Assigning a complete array to an excel range object at once?

[ramgni]

Hi,

I am trying to transfer a fairly large array from an external program to an Excel sheet via VBA in one go. However, simply assigning an array to an Excel range as in the proto example below does not do the job, as all cells are filled with the array's lower bound. Specifying an array index obviously does not help either. I tried all sorts of different options from within Excel to first get it straight there, but to no avail...
This is EEG data and there are lots, lots, lots of channels and timepoints, so I need all the speed I can get and loops just simply are toooo slow.

Question: Is there any way to assign a whole array to an Excel range object at once and without having to go through loops?

TIA,
ramgni

------------- snip -------------------------------
Sub Main
Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application&quot
ExcelApp.Visible = True
ExcelApp.WorkBooks.Add
Dim MyArray(1 To 20) As Double
For MyArrayNum = 1 To 20
MyArray(MyArrayNum) = MyArrayNum
Next MyArrayNum
ExcelApp.Activesheet.Range("A1:A20&quot.Value = MyArray
End Sub
------------- snip -------------------------------

 

[dsi]

Try a loop.

For iIdx = LBound(MyArray) to UBound(MyArray)
  xlApp.ActiveSheet.Range("A" & iIdx).Value = MyArray(iIdx)
Next

 

[ilses]

ramgni,

The following does use a loop to fill a small array, but dumping the array on the sheet doesn't.

Option Base 1
Sub dumpArray()

ActiveSheet.Range("A1:A20").FormulaArray = "=INDEX(getArray(),ROW())"

End Sub

Public Function getArray()
Dim arrInteger(20)
For i = 1 To 20
    arrInteger(i) = i
Next i
getArray = arrInteger
End Function

ilses

 

[SkipVought]

Hi,
To speed things up, trun off screen updating...

Sub PutArray(MyArray, iCol As Integer)
    Dim lRow As Long, iOff As Integer
    Application.ScreenUpdating = False
    If LBound(MyArray, 1) = 0 Then
        iOff = 1
    Else
        iOff = 0
    End If
    For lRow = LBound(MyArray, 1) To UBound(MyArray, 1)
        Cells(lRow + iOff, iCol).Value = MyArray(lRow)
    Next
End Sub

Skip,
metzgsk@voughtaircraft.com