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

[wiitguru]

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Also, if I send a

Please Login or Register  to view this content.

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

[AlphaFrog]

I appears that the EHLegacyIDs result array is 1-based (1 to 2650) and not zero-based (0 to 2649).

What is the lower boundary of the array

Please Login or Register  to view this content.

Do you have the line Option Base 1 at the top of your code module?

[Fluff13]

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.

[wiitguru]

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

[Fluff13]

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.

[wiitguru]

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

[Fluff13]

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.

[wiitguru]

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