Filling an array causes unexplainable results causing much frustration. Second set of eyes is appreciated.

[OaklandJim]

I am trying to assist with a post to Mr. Excel. But I am stumbling on something involving arrays that seems like it should be straightforward, easy even.

When I fill an array within a called sub and debug.print the content as the array is filled the correct data is apparently entered into the array as expected. After the filling exercise debug.print gives erroneous results for the array. Specifically the fist array "row" is empty even though the sub called does put the correct values into that array "row."

Said another way, my code reports that the array is filled as expected during the filling process in the sub called but when accessing the array 1. after it is filled in the sub called and 2. in the calling sub the data is not what was reported during array filling.

I have beat this thing to death. As usual, I do not think that my logic is effed up but, also, it is unlikely an issue with Excel.

My code is filled with Debug.Print commands that show how the results differ.

Here is the data. It has four columns: 1. ID, 2. Name, 3. Date and 4. Time. 34 data rows in the example data. Pretty basic!

Note that there are two unique IDs in the example data: 8 and 9.

RearrangeTimesData.xlsm
	B	C	D	E
2	ID	Name	Date	Time log
3	8	Hasnain	1/4/2023	10:58
4	8	Hasnain	1/4/2023	23:16
5	8	Hasnain	1/5/2023	12:36
6	8	Hasnain	1/5/2023	22:50
7	8	Hasnain	1/6/2023	11:21
8	8	Hasnain	1/6/2023	22:43
9	8	Hasnain	1/7/2023	11:20
10	8	Hasnain	1/7/2023	13:30
11	8	Hasnain	1/7/2023	14:22
12	8	Hasnain	1/7/2023	22:41
13	8	Hasnain	1/8/2023	11:00
14	8	Hasnain	1/8/2023	12:11
15	8	Hasnain	1/8/2023	12:29
16	8	Hasnain	1/8/2023	22:39
17	8	Hasnain	1/9/2023	11:06
18	8	Hasnain	1/9/2023	22:43
19	8	Hasnain	1/10/2023	14:58
20	8	Hasnain	1/10/2023	19:48
21	8	Hasnain	1/10/2023	20:21
22	8	Hasnain	1/10/2023	23:18
23	9	Ali	1/4/2023	11:06
24	9	Ali	1/4/2023	23:16
25	9	Ali	1/5/2023	12:05
26	9	Ali	1/5/2023	22:46
27	9	Ali	1/6/2023	12:19
28	9	Ali	1/6/2023	22:42
29	9	Ali	1/7/2023	11:20
30	9	Ali	1/7/2023	22:41
31	9	Ali	1/8/2023	11:00
32	9	Ali	1/8/2023	14:47
33	9	Ali	1/8/2023	14:57
34	9	Ali	1/8/2023	22:39
35	9	Ali	1/10/2023	14:58
36	9	Ali	1/10/2023	23:17
Sheet1

Here is the sub used to call the offending sub. I tried to explain everything with helpful comments.

Option Explicit

Sub TestFillArrays()

'   Worksheet where data is located.
    Dim wsData As Worksheet
    
'   Upperleftmost cell in the data range. It is a header for the column.
    Dim rAnchorCellData As Range
    
'   Countof data rows, not including header.
    Dim iDataRowsCount As Long
    
'   Keep track of how many unique IDs appear in the data.
    Dim iUniqueIDsCount
    
'   Array holding the data. In the example there are 34 data rows.
    Dim avData() As Variant
    
'   Array holding values for unique IDs. In the example there are
'   two unique IDs.
    Dim avUniqueIDsFound() As Variant
    
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    
    Set rAnchorCellData = wsData.Range("B2") '<= (header) cell where data starts

    iDataRowsCount = rAnchorCellData.Offset(10000).End(xlUp).Row - rAnchorCellData.Row
    
'   For each line the data has four items: 1. ID, 2. Name, 3. Date, 4. Time
'   In the test data there are 34 rows of data
    ReDim avData(1 To 4, 1)

'   For each unique ID there are three array elements: 1. ID, 2. Name,
'   and 3. Count of data rows containg the unique ID. Note that 3. is used
'   after the array filling process in this sub.
    ReDim avUniqueIDsFound(1 To 3, 1)

'   Call sub that fills the two arrays. 1. avData that contains all data
'   rows' content and 2. avUniqueIDsFound containing data for unique IDs found
'   in the data. This fills the avData array correctly. It does not fill the
'   avUniqueIDsFound array correctly.

    Call FillArrays( _
        rAnchorCellData, _
        iDataRowsCount, _
        (iUniqueIDsCount), _
        avData, _
        avUniqueIDsFound)

Debug.Print
Debug.Print "After call to sub FillArrays"
Debug.Print "Unique IDs found count = " & UBound(avUniqueIDsFound, 2)

'This indicates that the first "row" in the array is empty. But the
Debug.Print
Debug.Print "avUniqueIDsFound(1, 1) = " & avUniqueIDsFound(1, 1)
Debug.Print "avUniqueIDsFound(2, 1) = " & avUniqueIDsFound(2, 1)

Debug.Print
Debug.Print "avUniqueIDsFound(1, 1) = " & avUniqueIDsFound(1, 2)
Debug.Print "avUniqueIDsFound(2, 2) = " & avUniqueIDsFound(2, 2)

'Test code to confirm that the avData was filled correctly.
'Dim iRow As Long
'Debug.Print
'For iRow = 1 To iDataRowsCount
'Debug.Print "Row " & iRow & ". " & avData(2, iRow)
'Next iRow

End Sub

Here is the sub called. It is supposed to fill the array pavUniqueIDsFound with data for unique entries in the data. There are two unique IDs found in the example data above. Lots of in-line comments that describe the process. Note that this code fills two arrays: 1. pavData which contains all data rows' content and 2. pavUniqueIDsFound which contains data regarding each unique ID. There are two unique IDs in the data.

    prAnchorCellData As Range, _
    piDataRowsCount As Long, _
    ByRef piUniqueIDsCount As Long, _
    ByRef pavData As Variant, _
    ByRef pavUniqueIDsFound As Variant _
)

'   Used for looping through data rows for a person.
    Dim iRow As Long
    
'   Used to keep track of the current ID being processed.
    Dim sCurrentID As String
    
'   Used to keep track of the previus ID that was processed.
    Dim sPreviousID As String
    
    sCurrentID = ""
    
    sPreviousID = ""
    
    piUniqueIDsCount = 0
    
'   Iterate through all data rows for all people.
    For iRow = 1 To piDataRowsCount
        
        ReDim Preserve pavData(1 To 4, iRow)
        
        With prAnchorCellData.Offset(iRow, 0)
            pavData(1, iRow) = .Value
            pavData(2, iRow) = .Offset(0, 1).Value
            pavData(3, iRow) = .Offset(0, 2).Value
            pavData(4, iRow) = .Offset(0, 3).Value

'Debug.Print "row " & iRow & ". Data cell = " & .Address
'Debug.Print "row " & iRow & ". Name = " & pavData(2, iRow)

        End With
        
'       Get the ID for the unique ID found.
        sCurrentID = pavData(1, iRow)

Debug.Print
Debug.Print "previous unique ID = " & sCurrentID
Debug.Print "current unique ID = " & sPreviousID

        
'       Process another unique ID if one is encountered.
        If sPreviousID <> sCurrentID _
         Then
         
'           Another unique ID was encountered. Increment count.
            piUniqueIDsCount = piUniqueIDsCount + 1

Debug.Print
Debug.Print "next unique ID = " & piUniqueIDsCount
            
'           Make the array bigger to accommodate the next unique ID encounterd.
            ReDim pavUniqueIDsFound(1 To 3, piUniqueIDsCount)
            
'           Into the array put the unique ID and the person's name. That data
'           was stored in the pavData array above.
            pavUniqueIDsFound(1, piUniqueIDsCount) = pavData(1, iRow)
            pavUniqueIDsFound(2, piUniqueIDsCount) = pavData(2, iRow)

Debug.Print
Debug.Print "While filling the array in sub FillArray"
Debug.Print "Unique ID " & piUniqueIDsCount & " = " & pavUniqueIDsFound(1, piUniqueIDsCount)
Debug.Print "Unique Name " & piUniqueIDsCount & " = " & pavUniqueIDsFound(2, piUniqueIDsCount)
        
        End If
        
'       Set next ID for the next iteration
        sPreviousID = sCurrentID
        
    Next iRow
    
Debug.Print
Debug.Print "In sub FillArrays after filling the array in sub FillArrays"

Debug.Print
Debug.Print "piUniqueIDsCount = " & piUniqueIDsCount
Debug.Print "Data rows found count = " & UBound(pavData, 2)

Debug.Print
Debug.Print "Unique ID 1 ID = " & pavUniqueIDsFound(1, 1)
Debug.Print "Unique ID 1 name = " & pavUniqueIDsFound(2, 1)

Debug.Print
Debug.Print "Unique ID 2 ID = " & pavUniqueIDsFound(1, 2)
Debug.Print "Unique ID 2 name = " & pavUniqueIDsFound(2, 2)

End Sub

 

[OaklandJim]

I kept working on this super frustrating issue. I simplified things for a potential helper. I separated the offending code into a pretty short function.

See three code items below. One is a test sub. Another other correctly fills the data array with all line items in the dataset. The last one is the failing function. It fills an array with information about UNIQUE IDs found in the entire dataset. Debug.Print commands are meant to demonstrate the issue.

I must be missing something obvious. While the function is iterating the array seems to be filled as expected. (See Debug.Print output.) But outside of that context the array is not filled as expected.

Option Explicit
Option Base 1


Sub TestFillUniqueIDsArray()

'   Worksheet where data is located.
    Dim wsData As Worksheet
    
'   Upperleftmost cell in the data range. It is a header for the column.
    Dim rAnchorCellData As Range
    
'   Countof data rows, not including header.
    Dim iDataRowsCount As Long
    
'   Keep track of how many unique IDs appear in the data.
    Dim iUniqueIDsCount
    
'   Array holding the data. In the example there are 34 data rows.
    Dim avData() As Variant
    
'   Array holding values for unique IDs. In the example there are
'   two unique IDs.
    Dim avUniqueIDsFound() As Variant
    
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    
    Set rAnchorCellData = wsData.Range("B2") '<= (header) cell where data starts

    iDataRowsCount = rAnchorCellData.Offset(10000).End(xlUp).Row - rAnchorCellData.Row
    
'   For each line the data has four items: 1. ID, 2. Name, 3. Date, 4. Time
'   In the test data there are 34 rows of data
    ReDim avData(1 To 4, 1)

'   For each unique ID there are three array elements: 1. ID, 2. Name,
'   and 3. Count of data rows containg the unique ID. Note that 3. is used
'   after the array filling process in this sub.
    ReDim avUniqueIDsFound(1 To 3, 1)

    Call FillDataArray(rAnchorCellData, iDataRowsCount, avData)

'   Call sub that fills the avUniqueIDsFound array containing data for unique
'   IDs found in the data.
    Call FillUniqueIDsArray(avData, avUniqueIDsFound)

Debug.Print
Debug.Print "After call to Function FillUniqueIDsArray"
Debug.Print "Unique IDs found count = " & UBound(avUniqueIDsFound, 2)

'This indicates that the first "row" in the array is empty. But the
Debug.Print
Debug.Print "avUniqueIDsFound(1, 1) = " & avUniqueIDsFound(1, 1)
Debug.Print "avUniqueIDsFound(2, 1) = " & avUniqueIDsFound(2, 1)

Debug.Print
Debug.Print "avUniqueIDsFound(1, 1) = " & avUniqueIDsFound(1, 2)
Debug.Print "avUniqueIDsFound(2, 2) = " & avUniqueIDsFound(2, 2)

'Test code to confirm that the avData was filled correctly.
'Dim iRow As Long
'Debug.Print
'For iRow = 1 To iDataRowsCount
'Debug.Print "Row " & iRow & ". " & avData(2, iRow)
'Next iRow

End Sub

Option Explicit
Option Base 1

Function FillDataArray( _
    prAnchorCellData As Range, _
    piDataRowsCount As Long, _
    ByRef pavData As Variant _
)

'   Used for looping through data rows for a person.
    Dim iRow As Long
    
'   Iterate through all data rows for all people.
    For iRow = 1 To piDataRowsCount
        
        ReDim Preserve pavData(1 To 4, iRow)
        
        With prAnchorCellData.Offset(iRow, 0)
            pavData(1, iRow) = .Value
            pavData(2, iRow) = .Offset(0, 1).Value
            pavData(3, iRow) = .Offset(0, 2).Value
            pavData(4, iRow) = .Offset(0, 3).Value

        End With
        
    Next iRow
    
End Function

Option Explicit
Option Base 1

Function FillUniqueIDsArray( _
    ByVal pavData As Variant, _
    ByRef pavUniqueIDsFound As Variant)

'   Used for looping through data rows for a person.
    Dim iRow As Long
    
'   Used to keep track of the current ID being processed.
    Dim sCurrentID As String
    
'   Used to keep track of the previus ID that was processed.
    Dim sPreviousID As String
    
    Dim iUniqueIDsCount As Long
    
    Dim iDataRowsCount As Long
    
    sCurrentID = ""
    
    sPreviousID = ""
    
    iUniqueIDsCount = 0
    
    iDataRowsCount = UBound(pavData, 2)
    
'   Iterate through all data rows for all people.
    For iRow = 1 To iDataRowsCount

'       Get the ID for the unique ID found.
        sCurrentID = pavData(1, iRow)

'       Process another uniqe ID if one is encountered.
        If sPreviousID <> sCurrentID _
         Then
            iUniqueIDsCount = iUniqueIDsCount + 1
            
'           Make the array bigger to accommodate the nbext uniquie ID encounterd.
            ReDim pavUniqueIDsFound(1 To 3, iUniqueIDsCount)
            
'           Into the array put the unique ID and the person's name. That data
'           was stored in the pavData array above.
            pavUniqueIDsFound(1, iUniqueIDsCount) = pavData(1, iRow)
            pavUniqueIDsFound(2, iUniqueIDsCount) = pavData(2, iRow)
            
Debug.Print
Debug.Print "In function FillUniqueIDsArray, after filling array element " & iUniqueIDsCount
Debug.Print "pavUniqueIDsFound(1, iUniqueIDsCount) = " & pavUniqueIDsFound(1, iUniqueIDsCount)
Debug.Print "pavUniqueIDsFound(2, iUniqueIDsCount) = " & pavUniqueIDsFound(2, iUniqueIDsCount)
Debug.Print

        End If

'       Set previous ID for the next iteration.
        sPreviousID = sCurrentID

    Next iRow
    
Debug.Print
Debug.Print "In function FillUniqueIDsArray, after completing array filling"
Debug.Print "pavUniqueIDsFound(1, 1) = " & pavUniqueIDsFound(1, 1)
Debug.Print "pavUniqueIDsFound(2, 1) = " & pavUniqueIDsFound(2, 1)
Debug.Print
    
End Function

 

[OaklandJim]

I think I fixed part of the issue. I forgot to add Preserve when trying to make the array one bigger. An amateur blunder.

But, still some oddness. Sometimes Ubound reports three elements when I know that there are only two in the 2D array. So below is slightly modified code for the function that fills my unique items array.