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

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

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)

--
Regards,
Tom Ogilvy

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> 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