Range.Resize from Visual Basic in Windows to Excel
 

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

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

Range.Resize from Visual Basic in Windows to Excel
 

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 :

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

Range.Resize from Visual Basic in Windows to Excel
 

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.Tran spose(excelhold)

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

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

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
ft.com...

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 :

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