PDA

View Full Version : [SOLVED] Array Output



MINCUS1308
02-16-2018, 02:06 PM
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 :)

Paul_Hossler
02-16-2018, 03:39 PM
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

p45cal
02-16-2018, 03:39 PM
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).

MINCUS1308
02-19-2018, 06:22 AM
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)