VBA doesn't compares correctly via greater than string comparison

[tomtom91]

I have a sorting going on that looks like this



If istArray(k, 4) > sollArray(l, 4) Then
'Ist > soll -> soll first
finalArray(i, 1) = sollArray(l, 1)
finalArray(i, 4) = sollArray(l, 2)
finalArray(i, 5) = sollArray(l, 3)
l = l + 1
Else 'Ist < soll -> ist first
finalArray(i, 1) = istArray(k, 1)
finalArray(i, 2) = istArray(k, 2)
finalArray(i, 3) = istArray(k, 3)
k = k + 1
End If


For k = 1 I got the Word "ERR_BSUP_EL_OC" in istArray and for l = 1 the word "ERR_BSU_STELPMPDFCT" in sollArray. Both are written in uppercase (UCASE()) to be sure to compare correctly. When I hover over the variables in Visual Basics I see these entries getting compared. istArray should be greater > than sollArray. Therefor the first part should be activated. But it doesn't! The second part after the else gets triggered. I don't get why.

If I use Excels IF("ERR_BSUP_EL_OC">"ERR_BSU_STELPMPDFCT_VW";"Yes";"No") in Excel directly it even says "yes". What am I missing here? :banghead:

BTW the sorting works superb except for these two inputs, where the sorting gets "out of line".

[Bob Phillips]

The 8th character of istArray is P, which is ASCII 80.

The 8th character of sollArray is _, which is ASCII 95.

80 is not greater than 95, so the test fails.

[Aflatoon]

I'd assume VBA is doing a binary comparison by default. You could use
if strcomp(istArray(k, 4), sollArray(l, 4), vbtextcompare) = 1 Then

[tomtom91]

I'd assume VBA is doing a binary comparison by default. You could use
if strcomp(istArray(k, 4), sollArray(l, 4), vbtextcompare) = 1 Then

Like Bob Phillips wrote the ASCII compare for "_" and "P" states, that "_" is smaller than "P" what I wouldn't have guessed. Especially when Excel itself says that "_" is smaller than "P".

The full idea is to do something like this:
- If the strings are the same, write everything in one line
- If on string is smaller than the other, write the smaller one in the line.

So it's kind of a sorting. Of course I could use the strcomp() for the first part, but for the second part?

[Aflatoon]

Did you try the code I posted?

[tomtom91]

Did you try the code I posted?

Sorry I thought strcomp() was only for equal. Tried it and it works perfect. :bow:

[Aflatoon]

It will do less than, equal, or greater than, and return -1, 0 and 1 as appropriate. :)