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?

Tim Williams · Accepted Answer · 2023-03-14 16:15:31Z

2

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)

answered Mar 14, 2023 at 16:15

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2023-03-14 16:58:34Z

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

Certainly helpful in addition to @TimWilliams ' answer; see my late post using MS 365, too.

T.M. · Accepted Answer · 2023-03-16 18:08:43Z

1

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

edited Mar 16, 2023 at 18:08

answered Mar 15, 2023 at 19:43

T.M.

10k3 gold badges40 silver badges61 bronze badges

2 Comments

VBasic2008 Over a year ago

Creative as always. Are you aware that you can use WorksheetFunction or Application with Sequence e.g.: ... = Application.Sequence(UBound(datafield, 1), 1, 1, 1)? Also, in this case, instead of the ugly DROP(A1:E3,0,-4) you could use the nice TAKE(A1:E3,,1). I prefer something like =LET(Data,A1:E3,HSTACK(DROP(Data,,1),TAKE(Data,,1))).

T.M. Over a year ago

Thank you for the additional information, which will certainly be valuable additions for many users. @VBasic2008

Collectives™ on Stack Overflow

Array values not appearing when posted in Excel sheet

3 Answers 3

Comments

Shift Array Columns

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Shift Array Columns

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related