Transferring an array of data to Excel, this code doesn't work!

[Kraig K]

I'm acquiring data in from the comm port and storing it in an array. I followed MS's example on "Using automation to transfer an array of data to a range on a worksheet" There example works but when I try to put it in my code is doesn't. This is what I have:

Set objBook = objExcel.Workbooks.Add
      objExcel.Cells(1, 1) = "Force"
      objExcel.Cells(2, 1) = "(" & lblUnits.Caption & ")"
      objExcel.Cells(1, 3) = Date
      objExcel.Range("C:C").ColumnWidth = 9.5
         For i = 2 To Index Step 3
            Format(j) = Mid$(DataArray(i), 4)
            j = j + 1
         Next i
         objExcel.Cells(1, 6) = j
         objExcel.Range("C4", "C10").Value = "Hello"
         objExcel.Range("B4").Resize(100).Value = Format
         objExcel.Cells(1, 5) = Format(2)

Everything works except:

objExcel.Range("B4").Resize(100).Value = Format

If I change that line to say, Format(6) for example, then it prints that array value in 100 cells. It doesn't seem to want to print the entire array. What's wrong? I've tried putting parenths at the end and some other things with no luck. It will only "print" a single value multiple times, never multiple values one time, or something like that!

[Kraig K]

OK, I got it to work. What MS fails to mention in their example is the Resize method must have both a row and a column definition. When you're typing it, the pop up tip says it's optional but it's not!
Here's MS's example on how to do this: (Their example has a little more detail but this is the basic idea)

Dim oExcel As New Excel.Application
Dim oBook As New Excel.Workbook
Dim DataArray() As Variant
Dim r As Integer


Private Sub Form_Load()

ReDim DataArray(25, 0)
   Set oBook = oExcel.Workbooks.Add
   
   For r = 0 To 20
      DataArray(r, 0) = r       'puts data in columns
   Next r
   
   oExcel.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")
   oExcel.Range("A3").Resize(25, 1).Value = DataArray()
   oBook.SaveAs "C:\Temp\aaa.xls"
   oExcel.Quit
   Set oExcel = Nothing
End Sub

If I change DataArray to be one dimensional, it doesn't work. Or if it is 1D, and the Resize method only has one number, it doesn't work, try it!

Anywho, it's working and I'm happy now. I just wish MS would have made this more clear so I wouldn't have to drive myself crazy!