excelvbamultidimensional-array# VBA pasting 3 dimensional array into sheet

I have a 3 dimensional array (5 x 5 x 3) and I need to post (5 x 5 x 1) to Sheet1, (5 x 5 x 2) to Sheet2, (5 x 5 x 3) to Sheet3. Because I am building this 3 dimensional array inside 3 nested for loops, I cannot use a for loop to access the (5 x 5) part of the loop. Is there any identifier that tells excel to index all elements of an array, such as in MatLab by using (1:end, 1:end, 1)? Basic code is below:

```
Sub practice_2()
Dim arr(1 To 5, 1 To 5, 1 To 3)
Dim a As Integer
Dim x As Integer
Dim y As Integer
For a = 1 To 3
For x = 1 To 5
For y = 1 To 5
arr(x, y, a) = x * y
Next
Next
Sheets(a).Select
'Following line is where I want to access the (5 x 5 x 1) array
Range(Cells(1, 1), Cells(5, 5)) = arr
Next
End Sub
```

Solution

There's not much you can do directly with a 3-D array in Excel. However, VBA Variants are pretty flexible. You could get what you want by using a 1-D array that contains 2-D arrays instead of a 3-D array:

```
Dim arr(1 To 3)
Dim a As Integer
Dim x As Integer
Dim y As Integer
For a = 1 To 3
ReDim inner(1 To 5, 1 To 5)
'don't worry...makes a copy
arr(a) = inner
For x = 1 To 5
For y = 1 To 5
arr(a)(x, y) = a * x * y
Next
Next
Sheets(a).Select
Range(Cells(1, 1), Cells(5, 5)) = arr(a)
Next
```

(In answer to your specific question about array syntax, the answer is "no".)

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel