Problems to manipulate arrays VBA

J

Joao

New Member
Joined
Aug 26, 2011
Messages
8
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
I think it's clear, and I think you'll need to loop.
 
Upvote 0
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! :(
 
Upvote 0
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:

Code: 
Range("A1:B10").Value = myArray
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
The API solution:
Rich (BB code): 

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
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
I haven't run it or looked at it closely, ZVI (but will), but have no doubt it works.

Very nice, thanks for posting.
 
Upvote 0
You must log in or register to reply here.

Similar threads

2
  • Question Question
Replace codes with names adding chr(10)
Replies
4
Views
103
Fuji
F
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
128
BenW71
B
N
  • Solved
Help with a VBA I’m working on
Replies
8
Views
185
Zot
Z
S
  • Question Question
VBA - Filter a class array based on class function
Replies
1
Views
186
RoryA
RoryA
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
260
Pingaware
P

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top