Problems to manipulate arrays VBA

[Joao]

Hi everybody!

Well, I have 2 problems using arrays in VBA. First imagine that I'm working with an array with more than 1 dimension, for example a 3x2x4 matrix test(2,1,3). Then I try to get some values out of it in two ways and there comes my 2 doubts:

1) How can I paste in the spreadsheet just a part of the the matrix test? Specifically, I'd like to equate Range("A1:B3")=test,:,2). I know that is possible to do looping but the matrix is really big so I'd like a more direct way in the way I tried above

2) How do I define/create another array that is a part of the matrix test? The idea for me would be something like: text2,=test,:,2). Again, I'm not looking for a looping code.

If I didn't make myself clear, please let me know.

Thanks!
Joao

 

[ZVI]

I haven't run it or looked at it closely, ZVI (but will), but have no doubt it works.

Very nice, thanks for posting.

Thanks, Shg
I've tested it on Excel 2003 & XP OS as well as on Excel 2010 32-bit & Win7 OS 64-bit. Seems it works as expected.
Vlad

P.S. Misprint:
Was: ' NOTE: the dimensions of b() can be different relative to b(),
Should be: ' NOTE: the dimensions of b() can be different relative to a(),

 

[mirabeau]

Hi Joao,

Maybe 3(and more)D arrays are not often used on this forum.

However, if you're interested in an operational usage, in addition to Vladimir's illustration of course, there's one in Post#4 in the thread

http://www.mrexcel.com/forum/showthread.php?t=264609

 

[Joao]

Hi ZVI! Thanks, it works pretty well....very interesting!

The only problem in my case is that my original matrix, in your code it would be equivalente to the matrix "a", is so big, approximately a(3124,2519,3) as Variant, that when I create the matrix "b" the excel display an alert that the memory is insuficient.

I don't know if there's a way to alocate more memory to excel. Maybe I will try that or I will have to change my code.

Anyway ZVI, I usually need this kind of operation in others codes that I write so that will be very useful for me, thanks a lot!

 

[Joao]

Hi Mirabeau!

I took a look in the post that you mentioned, but in that case the information is already in the spreadsheet. In my case I generate the information in VBA, so my matrix is in the memory of excel, so I think that won't work for my purposes. Thanks for the attention!

 

[mirabeau]

Hi Mirabeau!

I took a look in the post that you mentioned,but in that case the information is already in the spreadsheet. In my case I generate the information in VBA, so my matrix is in the memory of excel, so I think that won't work for my purposes. Thanks for the attention!

Makes no difference really. You have to get data into the system somehow.

It's very easy to switch between one and the other.

I just gave that link as an example of using a 3D array in conjunction with (the basically 2-dimensional) Excel, which I understood your post was about.

But of course you should do whatever you like with whatever info is available.

 

[Joao]

Ok, Mirabeau. I'll take a closer look on the post, thanks anyway