Setting a range.value equal to /part/ of a (3D) variant array?

[ker_01]

I sometimes pull ranges into an array to save time (instead of looping).

Right now, I'd like to do the opposite- I have a complicated workbook and
the code isn't working properly, so I need to see the array during
processing (refreshed during a larger loop) to see what is going on.

The problem is that while I can set a worksheet range equal to a 2D array,
this particular array is 3D and there is only one slice of that I need to
see (effectively a 2D slice)

So what do I need to change in my syntax to go from the two dimensional
assignment:

Sheet1.range("A1:J100).value = TestArray

to a slice of a 3D range, where TestArray(1 to 30, 1 to 100, 1 to 3) and I
only need to see where the third parameter =2;

Sheet1.range("A1:AD100").value = TestArray ([all],[all],2)

Is this possible, and if so, how do I reference TestArray to get my desired
'slice'?

Thanks for any assistance,
Keith

 

[ker_01]

To clarify, here is some test code I was playing with; I haven't found any
syntax for the last line that will work.

Sub test()
Dim TestArray As Variant
ReDim TestArray(1 To 3, 1 To 3, 1 To 3)
For x = 1 To 3
For y = 1 To 3
useY = Chr(y + 64)
For z = 1 To 3
useZ = Chr(z + 32)
TestArray(x, y, z) = x & useY & useZ
Next
Next
Next
Sheet7.Range("A1:C3").Value = TestArray(, , 1) 'can't get this to work with
any syntax I've tried
End Sub

 

[Phillip]

To clarify, here is some test code I was playing with; I haven't found any
syntax for the last line that will work.

Sub test()
Dim TestArray As Variant
ReDim TestArray(1 To 3, 1 To 3, 1 To 3)
For x = 1 To 3
    For y = 1 To 3
        useY = Chr(y + 64)
        For z = 1 To 3
            useZ = Chr(z + 32)
            TestArray(x, y, z) = x & useY & useZ
        Next
    Next
Next
Sheet7.Range("A1:C3").Value = TestArray(, , 1)  'can't get this to work with
any syntax I've tried
End Sub

I sometimes pull ranges into an array to save time (instead of looping).

Right now, I'd like to do the opposite- I have a complicated workbook and
the code isn't working properly, so I need to see the array during
processing (refreshed during a larger loop) to see what is going on.

The problem is that while I can set a worksheet range equal to a 2D array,
this particular array is 3D and there is only one slice of that I need to
see (effectively a 2D slice)

So what do I need to change in my syntax to go from the two dimensional
assignment:

Sheet1.range("A1:J100).value = TestArray

to a slice of a 3D range, where TestArray(1 to 30, 1 to 100, 1 to 3) and I
only need to see where the third parameter =2;

Sheet1.range("A1:AD100").value = TestArray ([all],[all],2)

Is this possible, and if so, how do I reference TestArray to get my
desired 'slice'?

Thanks for any assistance,
Keith- Hide quoted text -

- Show quoted text -

phillip London UK
This is one way

Sub test()
Dim TestArray As Variant

Dim result As Variant
Dim slice As Long
slice = 1 'or 2 or 3
ReDim result(1 To 3, 1 To 3)

ReDim TestArray(1 To 3, 1 To 3, 1 To 3)
For x = 1 To 3
For y = 1 To 3
usey = Chr(y + 64)
For z = 1 To 3
useZ = Chr(z + 32)
TestArray(x, y, z) = x & usey & useZ
Next
Next
Next

For j = 1 To 3
For k = 1 To 3
result(k, j) = TestArray(k, j, slice)
Next
Next
Sheet1.Range("a1:C3").Value = result



End Sub

 

[ker_01]

Anyone?
Thanks!
Keith

To clarify, here is some test code I was playing with; I haven't found any
syntax for the last line that will work.

Option Base 1

Sub test()

'set up a sample array for testing
Dim TestArray As Variant
ReDim TestArray(1 To 3, 1 To 3, 1 To 3)
For x = 1 To 3 'to fill dimension 1 of array with 1,2,3
For y = 1 To 3
useY = Chr(y + 64) 'to fill dimension 2 of array with A,B,C
For z = 1 To 3
useZ = Chr(z + 32) 'to fill dimension 3 of array with !,",#
TestArray(x, y, z) = x & useY & useZ
'should result in (1,A,!); (1,A,"); (1,A,#); (1,B.!);
etc... to (3,C,#)
Next
Next
Next

'*** paste a slice of the sample array into a worksheet- this is the key
line that I need help figuring out ***
'because I need to paste a 2D section of a 3D array
Sheet7.Range("A1:C3").Value = TestArray(, , 1) ' where the "1" should
limit the pasted range to everything that ends with "!"

End Sub

I sometimes pull ranges into an array to save time (instead of looping).

Right now, I'd like to do the opposite- I have a complicated workbook and
the code isn't working properly, so I need to see the array during
processing (refreshed during a larger loop) to see what is going on.

The problem is that while I can set a worksheet range equal to a 2D
array, this particular array is 3D and there is only one slice of that I
need to see (effectively a 2D slice)

So what do I need to change in my syntax to go from the two dimensional
assignment:

Sheet1.range("A1:J100).value = TestArray

to a slice of a 3D range, where TestArray(1 to 30, 1 to 100, 1 to 3) and
I only need to see where the third parameter =2;

Sheet1.range("A1:AD100").value = TestArray ([all],[all],2)

Is this possible, and if so, how do I reference TestArray to get my
desired 'slice'?

Thanks for any assistance,
Keith