VBA2D Array<-> Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Charles - 18 Nov 2006 00:13 GMT

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

Reply to this Message

Harlan Grove - 18 Nov 2006 00:21 GMT

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
[quoted text clipped - 3 lines]
>array. I would expect to be able to do things a bit more directly. Any
>idea?

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.

Reply to this Message

Charles - 18 Nov 2006 11:21 GMT

I think that answer my question!

Thanks

> 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.

Reply to this Message

Rate this thread: