Range.Resize from Visual Basic in Windows to Excel

[Guest]

I have a visual basic program in Windows that puts calculated data in an
array INTO an EXCEL spreedsheet. I am using late binding. Note I am not
Writing a macro inside EXCEL, but in a Visual Basic program outside of EXCEL.

The array is excelhold(122)

I tried to use the statement
oSheet.Range("A2").Resize(122,1).Value=excelhold

The problem in put the value of excelhold(1) into all the cells in column A
in the spreadsheet instead of putting all the values of excelhold.

If I use the statement
oSheet.Range("A2").Resize(1,122).Value=excelhold
It puts all the values of excelhold in row 2

Resize(122) and Resize (122,0) and skipping Resize all together does not
work either.

Any ideas?
Thanks

 

[keepITcool]

excel will interpret a 1 dimensional array as horizontal data,
whereas vertical data s.b 2 dimensional.

Also note that unless you have the line OPTION BASE 1 set at the top of
your module you're working with a 0 based array.

Dim (122,0) is equivalent to dim (0 to 122,0 to 0) as variant
which holds 123 elements in the first dimension.

If you only need 122 elems decrease the ubound by 1 or up the lbound.
dim excelhold(1 to 122,1 to 1)




Sub x()
Dim i%,excelhold(122, 0)
For i = 0 To 122: excelhold(i, 0) = i: Next
Range("a1").Resize(123, 1) = excelhold
End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


MMesarch wrote :

 

[Tom Ogilvy]

Just some added information.

as long as your array is less than 5461 elements you can also do

Assume oxlApp refers to the excel application


oSheet.Range("A2").Resize(122,1).Value=oxlApp.Transpose(excelhold)

and if you array is really 123 elements (zero based)

oSheet.Range("A2").Resize(123,1).Value=oxlApp.Transpose(excelhold)