WorksheetFunction.Sort Pre-pending a Null Value to an Array

Dear Forum,

I have this really weird thing happening in my VBA that I've never run across, and I'm wondering if anyone else has? I am populating a variant array with Long values, and then modifying the array by using a WorksheetFunction.Sort to it. Upon doing so, the array gets pre-pended with a null value, as shown below. Has anyone ever heard of this? Here's my code, and the results from the Immediate window.

    ' Get the 'Legacy' ID from EH worksheet and create a list of Legacy #'s for sort. ' XXXXX 20220425_1335 BT XXXXXXXX
    x = 0
    For i = EHLkpFirstDataRow To EHLkpLastDataRow Step 1
        EHTmpLongStr = Trim(CStr(ehWks.Cells(i, EHLegacyIDColNum).Value))
        If Len(EHTmpLongStr) > 0 Then
            If Not IsNumeric(EHTmpLongStr) Then Err.Raise 17
            EHTmpLong = CLng(EHTmpLongStr)
            ' If we've gotten here, we have a long.  Add it to the array.
            If x = 0 Then
                ReDim EHTmpArray(0)
            Else
                ReDim Preserve EHTmpArray(0 To x)
            End If  ' x = ...
            EHTmpArray(x) = EHTmpLong
            x = x + 1
        End If ' Len...
    Next i
    
    ' Sort the list.
    EHLegacyIDs = Application.WorksheetFunction.Sort(EHTmpArray, , , True)

The code errors-out later at this line, when I try to get the first value from the sorted array. The array is not read or modified between the Sort and the 'EHLegacyIDs(0)' portion of code.

Set searchRng = ehWks.Range(searchRngAddr).Find(EHLegacyIDs(0), , xlValues, xlWhole)

The error I get is a 'Subscript out of Range' error.

Here's the proof that the null value is getting pre-pended, from the Immediate window. These Immediate statements were run when the code was stopped at the error location.

? Ubound(EHTmpArray)
 2649 
? Ubound(EHLegacyIDs)
 2650

Also, if I send a

? Ubound(EHLegacyIDs(0))

command to the Immediate window at this point, I get the 'Subscript out of Range' error.

Has anyone heard of this happening? I'm using Office 365 on a Windows 10 machine.

Brian

From what I've found, using the worksheet function Sort, will always return a 1 based array regardless of your settings.
Therefore there will never be an element 0, which is why you get the error.

Fluff13,

Well, in my humble opinion, that is just silly for Excel to do that. But that appears to be what is happening. I was able to verify that by checking the Lbound at the error point, as AlphaFrog suggested. I guess, add one "hate" point to the tally of my love/hate relationship with Excel!

Brian

that is just silly for Excel to do that

But you cannot have a zero based array in Excel. If you try to index an array with 0 for the row it will return the entire array.

Mmmm, are you sure? I used zero-based arrays in Excel VBA all the time. This was the first time I encountered a problem like this with an array.

Brian

You can have 0 based arrays in VBA, but not in xl & as you are using a worksheet function, it will act the same as it would in a sheet.
VBA & Excel are not the same thing.

Ah. I already knew that VBA and Excel are not the same thing, but I was not aware that worksheet functions would operate differently than other VBA functions. Guess every day is a day to learn something new. Thanks!

Brian