VBA2D Array<-> Excel

C

Charles

Quick syntax question:

is there a way to copy one colunn of a 2D array into excel directly and
vice versa? I.e. I have a 200*10VBA array, and I want to plug the value
of the 7th column into one Excel Column. Of course I don't want to
enter values in Excel one by one. For the moment I fill an intermediary
VBA Array of 200*1 with data, and then I use a range("..").value2=that
array. I would expect to be able to do things a bit more directly. Any
idea?

Thanks
Charles
 
H

Harlan Grove

Charles wrote...
....
is there a way to copy one colunn of a 2D array into excel directly and
vice versa? I.e. I have a 200*10VBA array, and I want to plug the value
of the 7th column into one Excel Column. Of course I don't want to
enter values in Excel one by one. For the moment I fill an intermediary
VBA Array of 200*1 with data, and then I use a range("..").value2=that
array. I would expect to be able to do things a bit more directly. Any
idea?
Click to expand...

Have you tried

yourrange.Value = Application.WorksheetFunction.Index(yourarray, 0, 7)

?

Unlike Excel, VBA provides no built-in array slicing procedures. It's
not clear to me that calling WorksheetFunction functions with VBA array
arguments would prove more efficient than loading the 7th column of
your big array into a smaller array and assigning the latter as the
range's values.
 
Top