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

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

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

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


"ker_01" wrote in message
...
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

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

On 15 Jul, 20:39, "ker_01" wrote:
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

"ker_01" wrote in message

...



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

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

Anyone?
Thanks!
Keith

"ker_01" wrote in message
...
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


"ker_01" wrote in message
...
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