Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Range not equal to zero in Visual Basic If/Then statement | Excel Discussion (Misc queries) | |||
changing a formula range in visual basic | Excel Programming | |||
Range Reference in Visual Basic | Excel Programming | |||
Visual Basic Range variable | Excel Programming | |||
Visual basic to step through range and store a value based on text attribute value. | Excel Programming |