Consulting

Results 1 to 4 of 4

Thread: Array Output

  1. #1
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    254

    Array Output

    I have filled an array and am attempting to output the array.
    When I output the array I am only getting the first row of elements.
    If I ask for a specific element in the array it will return the correct value.

    Filling the array is done with a 'select case' as I step through another array.
     Case "TA"
                      ReDim Preserve arr_B_St_TA(9, c_arr_B_St_TA)
                            arr_B_St_TA(0, c_arr_B_St_TA) = DFArray(c_DFArray, PRODID)
                            arr_B_St_TA(1, c_arr_B_St_TA) = DFArray(c_DFArray, ITEMID)
                            arr_B_St_TA(2, c_arr_B_St_TA) = DFArray(c_DFArray, SCHEDSTART)
                            arr_B_St_TA(3, c_arr_B_St_TA) = DFArray(c_DFArray, QTYSCHED)
                            arr_B_St_TA(4, c_arr_B_St_TA) = ""
                            arr_B_St_TA(5, c_arr_B_St_TA) = ""
                            arr_B_St_TA(6, c_arr_B_St_TA) = ""
                            arr_B_St_TA(7, c_arr_B_St_TA) = ""
                            arr_B_St_TA(8, c_arr_B_St_TA) = "TA: " & DFArray(c_DFArray, ta)
                       c_arr_B_St_TA = c_arr_B_St_TA + 1
    Later I try to output the array
    ActiveSheet.Cells(1, 1).Resize(UBound(arr_B_St_TA, 2), 9) = Application.Transpose(arr_B_St_TA)
    Which only outputs items manually input
    Dim arr_B_St_TA() As Variant      
              ReDim arr_B_St_TA(9, 1)
                   arr_B_St_TA(0, 0) = "a"
                   arr_B_St_TA(1, 0) = "b"
                   arr_B_St_TA(2, 0) = "c"
                   arr_B_St_TA(3, 0) = "d"
                   arr_B_St_TA(4, 0) = ""
                   arr_B_St_TA(5, 0) = ""
                   arr_B_St_TA(6, 0) = ""
                   arr_B_St_TA(7, 0) = ""
                   arr_B_St_TA(8, 0) = "e"
              c_arr_B_St_TA = 1
    But when I ask for element
    Msgbox arr_B_St_TA(0, 1)
    It returns values the correct values

    What am I doing wrong? Other than terrible coding practices
    - I HAVE NO IDEA WHAT I'M DOING

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I think since you're putting a 2D array onto the 2D worksheet, you don't need the .Transpose, unless you want to go from a m x n to a n x m array

    Experiment with this and see. I just filled some data to test



     
    
    Option Explicit
    Sub test()
        Dim arr_B_St_TA() As Variant
        Dim c_arr_B_St_TA As Long, i As Long
        
        c_arr_B_St_TA = 0
        
        For i = 0 To 9
                
            ReDim Preserve arr_B_St_TA(0 To 8, 0 To c_arr_B_St_TA)
                                
             arr_B_St_TA(0, c_arr_B_St_TA) = 100 * i + c_arr_B_St_TA
             arr_B_St_TA(1, c_arr_B_St_TA) = 101 * i + c_arr_B_St_TA
             arr_B_St_TA(2, c_arr_B_St_TA) = 102 * i + c_arr_B_St_TA
             arr_B_St_TA(3, c_arr_B_St_TA) = 103 * i + c_arr_B_St_TA
             arr_B_St_TA(4, c_arr_B_St_TA) = 104 * i + c_arr_B_St_TA
             arr_B_St_TA(5, c_arr_B_St_TA) = 105 * i + c_arr_B_St_TA
             arr_B_St_TA(6, c_arr_B_St_TA) = 106 * i + c_arr_B_St_TA
             arr_B_St_TA(7, c_arr_B_St_TA) = 107 * i + c_arr_B_St_TA
             arr_B_St_TA(8, c_arr_B_St_TA) = "TA: " & 1001 * i
        
            c_arr_B_St_TA = c_arr_B_St_TA + 1
        Next i
    
        '9 rows, 10 columns
        ActiveSheet.Cells(1, 1).Resize(UBound(arr_B_St_TA, 1) + 1, UBound(arr_B_St_TA, 2) + 1).Value = arr_B_St_TA
       
        '10 rows, 9 columns
        ActiveSheet.Cells(1, 13).Resize(UBound(arr_B_St_TA, 2) + 1, UBound(arr_B_St_TA, 1) + 1).Value = Application.WorksheetFunction.Transpose(arr_B_St_TA)
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Difficult to say without some values for some of the constants, but I would suspect that because your arrays are 0 based arrays, they're one column wider and one row longer than you think.
    So this is a guess:
    Let's say that c_arr_B_St_TA = 2
    The statement:
    ReDim Preserve arr_B_St_TA(9, c_arr_B_St_TA)
    is interpreted as:
    ReDim Preserve arr_B_St_TA(9, 2)
    which in longhand would be:
    ReDim Preserve arr_B_St_TA(0 to 9, 0 to 2)
    This is an array 10 rows deep and 3 columns wide.
    Transpose that and you have an array 3 rows deep and 10 columns wide, but your write-to-sheet statement is:
    .Resize(UBound(arr_B_St_TA, 2), 9)
    which is interpreted as:
    .Resize(2, 9)
    which is only 2 rows deep and 9 columns wide.
    Could that be it?


    If somewhere in your code (just before you write to the sheet) you add:
    zzz = Application.Transpose(arr_B_St_TA)
    (and still c_arr_B_St_TA = 2)
    if you look in the Locals pane you should see that the array (zzz) you're writing to the sheet is: Variant/Variant(1 to 3, 1 to 10) and not (1 to 2, 1 to 9)

    When you write an array to a smaller range, you lose the last row(s) and/or column(s).
    Last edited by p45cal; 02-16-2018 at 03:58 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    254
    Thank you Paul_Hossler and p45cal,
    p45cal was correct, 0 based arrays do indeed have one more set of elements...

    I repeat this process few times to build my various arrays, I just hadn't noticed the missing data in the other arrays yet.
    The correction I made was to change the statement:
    ActiveSheet.Cells(1, 1).Resize(UBound(arr_B_St_TA, 2), 9) = Application.Transpose(arr_B_St_TA)
    to
    ActiveSheet.Cells(1, 1).Resize(UBound(arr_B_St_TA, 2) + 1, 9) = Application.Transpose(arr_B_St_TA)
    - I HAVE NO IDEA WHAT I'M DOING

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •