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

 

[shg]

I think it's clear, and I think you'll need to loop.

 

[Joao]

I've done with a loop now, but it took a lot of time! I really need a quicker way...

I tought that would be easier because I know that in other softwares it's very common and simple to do that, so I tought VBA must have a way too. Hope I am right, otherwise things will turn upside down!

 

[shg]

There's other things you can do with 2D arrays, because they have analogies on the worksheet (ranges are 2D arrays) and worksheet functions that operate on them. pgc01 on this forum is a wiz at applying them to VBA arrays.

3D+ arrays, none that I know of.

You could copy 1D or 2D slices of 3D+ arrays to another array and then write that directly to the worksheet:

Range("A1:B10").Value = myArray

 

[Joao]

Awesome!!I think that would work for me really well!
But how do I slice the 3D array? Could you give an simple example, please?

 

[shg]

Use the same code you used to write it to a worksheet, except write it to a 2D array. Then write the 2D array to the worksheet.

 

[ZVI]

The API solution:

Option Explicit
#If Win64 Then
  Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
#Else
  Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#End If


' ZVI:2011-08-27 http://www.mrexcel.com/forum/showthread.php?t=574784
' How to use API CopyMemory for the fast data exchange between arrays
Sub ArrayCopy()
  
  ' Declare variant type arrays variables
  Dim a(0 To 2, 0 To 1, 0 To 3), b()
  ' Declare Long type variables
  Dim i&, j&, k&, m&
  
  ' Populate a(:,:,:)
  Debug.Print "==="
  Debug.Print " i", " j", " k", "a(i, j, k)"
  For k = 0 To 3
    For j = 0 To 1
      For i = 0 To 2
        m = m + 1
        a(i, j, k) = m
        ' Show values of a(:,:,2)
        If k = 2 Then Debug.Print i, j, k, a(i, j, k)
      Next
    Next
  Next
  
  ' Calc the amount of memory for a(:,:,2) by its bounds
  m = VarPtr(a(0, 0, 3)) - VarPtr(a(0, 0, 2))
  
  ' Prepare b(:,:) with 2D dimensions similar to a(:,:,2)
  ' NOTE: the dimensions of b() can be different relative to b(),
  '       but the size of b() in memory (amount of elements) should be not less then m,
  '       else the Excel application crashes!
  ReDim b(0 To 2, 0 To 1)
  
  ' Copy a(:,:,2) to b(:,:) using memory references to the 1st elements of arrays
  Call CopyMemory(b(0, 0), a(0, 0, 2), m)
  
  ' Show values of the resulting b(:,:)
  Debug.Print: Debug.Print " i", " j", "b(i, j)"
  For j = 0 To 1
    For i = 0 To 2
      Debug.Print i, j, b(i, j)
    Next
  Next
  
  ' Copy result to the cells
  Range("A1:B3").Value = b()
  
End Sub

 

[Joao]

Oh, I see! I tought that this slice was a kind of function or something.

I tried that and the problem of doing a loop it that the 3D matrix is so big that excel don't have enough memory to create a 2D matrix! Big trouble!

 

[Joao]

Oh, I see! I tought that this slice was a kind of function or something.

I tried that and the problem of doing a loop it that the 3D matrix is so big that excel don't have enough memory to create a 2D matrix! Big trouble!

 

[shg]

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

Very nice, thanks for posting.