Array values not appearing when posted in Excel sheet

Question

So I am running this code

Sub Array_testing()

    Dim TestArray As Variant
    Dim OutputArray As Variant
    Dim OutputX As Variant
    Dim OutputY As Variant
    Dim InputX As Variant
    Dim InputY As Variant
    
    OutputX = 1
    OutputY = 1
    InputX = 1
    InputY = 2
    
    ReDim OutputArray(3, 5)
    
    TestArray = Range("a1:e3").Value

    Range("a6:e8").Value = TestArray
    
    Range("g1").Value = TestArray(2, 4)
        
    While InputX <> 4
            Debug.Print "output (" & OutputX & "," & OutputY & ") - input (" & InputX & "," & InputY & ")"
            OutputArray(OutputX, OutputY) = TestArray(InputX, InputY)
            Debug.Print OutputArray(OutputX, OutputY)
            OutputY = OutputY + 1
            InputY = InputY + 1
            If OutputY = 6 Then
                OutputX = OutputX + 1
                OutputY = 1
                InputX = InputX + 1
            End If
            If InputY = 6 Then
                InputY = 1
            End If
    Wend

    Range("a10:e12").Value = OutputArray
    Range("g2").Value = OutputArray(2, 1)
     
End Sub

and using these as my array values:

when the code hits "Range("a10:e12").Value = OutputArray", this is what I get

when I am expecting this to show up

I know that the array values are entering correctly because when I specifically call the array location (for example OutputArray(1,2) I get Red, which is as expected.

And when I put the TestArray values in, they come up perfect. It's the same call but for a different array, and the values are in the proper allocation spots, so why is it not showing up when I display the values of OutputArray?

Answer 1

Arrays in VBA are by default zero-based, so

ReDim OutputArray(3, 5) 

is the same as

ReDim OutputArray(0 To 3, 0 To 5) 

Your array is larger than you think, so you'd need A10:F13 to show the whole content. The parts of your array which don't fit into the specified range will not be displayed.

1-based arrays are simpler to deal with if you're going to be putting them into a range on a worksheet, so:

ReDim OutputArray(1 To 3, 1 To 5) 

Answer 2

Shift Array Columns

• All your issues are covered in Tim Williams' answer.
• Here's an example of how you could solve your task by using a For...Next loop which in my opinion is more straightforward than using a Do Loop.

Sub ArrayTest1()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim srg As Range: Set srg = ws.Range("A1:E3")
    
    Dim sData(): sData = srg.Value
    
    Dim dData(): ReDim dData(1 To UBound(sData, 1), 1 To UBound(sData, 2))
    
    Dim r As Long, c As Long
    
    For r = 1 To UBound(sData, 1)
        For c = 1 To UBound(sData, 2) - 1
            dData(r, c) = sData(r, c + 1)
        Next c
        dData(r, c) = sData(r, 1)
    Next r
    
    Dim dfCell As Range: Set dfCell = ws.Range("A10")
    Dim drg As Range: Set drg = dfCell.Resize(UBound(dData, 1), UBound(dData, 2))
    
    drg.Value = dData
    
End Sub

• This one is the same but uses variables for the array limits.

Sub ArrayTest2()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim srg As Range: Set srg = ws.Range("A1:E3")
    Dim rCount As Long: rCount = srg.Rows.Count
    Dim cCount As Long: cCount = srg.Columns.Count
    
    Dim sData(): sData = srg.Value
    
    Dim dData(): ReDim dData(1 To rCount, 1 To cCount)
    
    Dim r As Long, c As Long
    
    For r = 1 To rCount
        For c = 1 To cCount - 1
            dData(r, c) = sData(r, c + 1)
        Next c
        dData(r, c) = sData(r, 1)
    Next r
    
    Dim dfCell As Range: Set dfCell = ws.Range("A10")
    Dim drg As Range: Set drg = dfCell.Resize(rCount, cCount)
    
    drg.Value = dData
    
End Sub

Answer 3

In addition to the valid posts of Tim and VBasic2008:

If you dispose of version MS 365 and its Sequence() function you can redefine the existing column order from e.g.

Array(1,2,3,4,5) ~~> to Array(5,1,2,3,4)

and try the following example code (in connection with the restructuring features of the Index() function):

Option Explicit                           ' head of code module

Sub ShiftArray()
'[1]get datafield
    Dim data: data = Sheet1.Range("A1:E3")
'[2]reorder columns via sequence Array(2, 3, 4, 5, 1)
    data = Application.Index(data, rowOrder(data), colOrder(data))
'[3]write to any target
    Sheet1.Range("A11").Resize(UBound(data), UBound(data, 2)) = data
End Sub

Helper functions

Function rowOrder(datafield)
'Purp: get all existing row numbers as vertical 2-dim 1-based array
    rowOrder = Evaluate("=Sequence(" & UBound(datafield, 1) & ",1,1,1)")
End Function

Function colOrder(datafield, Optional IsLeftShift As Boolean = True)
'Purp: get column numbers as "flat" 1-dim 1-based array
    Dim colcount As Long: colcount = UBound(datafield, 2)
    Dim tmp
    If IsLeftShift Then
        tmp = Evaluate("=Sequence(1," & colcount & ",2,1)")
        tmp(colcount) = 1           ' redefine last element, e.g. {2,3,4,5,1}
    Else
        tmp = Evaluate("=Sequence(1," & colcount & ",0,1)")
        tmp(1) = colcount           ' redefine 1st element,  e.g. {5,1,2,3,4}
    End If
    colOrder = tmp
End Function

Note

If you prefer a tabular Excel approach via MS 365, you might procede even more directly via

=HSTACK(B1:E3;A1:A3)

or

=HSTACK(DROP(A1:E3;;1);DROP(A1:E3;0;-4))

as formula in your spilling target cell, optionally made more readable by defining LET variables :-)

Even better thanks to VBasic2008 's comment via TAKE():

=LET(Data,A1:E3,HSTACK(DROP(Data,,1),TAKE(Data,,1)))

Hint: If you don't dispose of MS 365, you can build the column order array also via loop.

Related post

• Some pecularities of the Application.Index() function