VBA用户窗体ListBox不区分大小写排序问题求助

我需要优化一段VBA代码：移除工作表「Groups」A列的重复值，将内容添加到用户窗体的ListBox中，同时实现不区分大小写的字母排序。当前代码的排序会优先处理大写字母，结果不符合预期。

原代码 #

Dim Coll As Collection, cell As Range, LastRow As Long
Dim blnUnsorted As Boolean, i As Integer, temp As Variant
Dim SourceSheet As Worksheet
Set SourceSheet = Worksheets("Groups")

'///////////////////////////////////////////////////////

'Populate the ListBox with unique Make items from column A.
LastRow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
Set Coll = New Collection
'Open a With structure for the ListBox control.
With ClientInput
    .Clear
    For Each cell In SourceSheet.Range("A2:A" & LastRow)
        'Only attempt to populate cells containing a text or value.
        If Len(cell.Value) <> 0 Then
            Err.Clear
            Coll.Add cell.Text, cell.Text
            If Err.Number = 0 Then .AddItem cell.Text
            End If
    Next cell
        blnUnsorted = True
        Do
        blnUnsorted = False
        For i = 0 To UBound(.List) - 1
            If .List(i) > .List(i + 1) Then
                temp = .List(i)
                .List(i) = .List(i + 1)
                .List(i + 1) = temp
                blnUnsorted = True
                Exit For
            End If
        Next i
    Loop While blnUnsorted = True
'Close the With structure for the ListBox control.
End With

当前问题 #

当前排序结果（大写优先）：

AC
AZ
ab

期望排序结果（不区分大小写）：

ab
AC
AZ

修改后的代码 #

Dim Coll As Collection, cell As Range, LastRow As Long
Dim blnUnsorted As Boolean, i As Integer, temp As Variant
Dim SourceSheet As Worksheet
Set SourceSheet = Worksheets("Groups")

'///////////////////////////////////////////////////////

'Populate the ListBox with unique Make items from column A.
LastRow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
Set Coll = New Collection
'Open a With structure for the ListBox control.
With ClientInput
    .Clear
    For Each cell In SourceSheet.Range("A2:A" & LastRow)
        'Only attempt to populate cells containing a text or value.
        If Len(cell.Value) <> 0 Then
            Err.Clear
            Coll.Add cell.Text, cell.Text
            If Err.Number = 0 Then .AddItem cell.Text
            End If
    Next cell
        blnUnsorted = True
        Do
        blnUnsorted = False
        For i = 0 To UBound(.List) - 1
            ' 使用StrComp函数并指定vbTextCompare参数实现不区分大小写比较
            If StrComp(.List(i), .List(i + 1), vbTextCompare) > 0 Then
                temp = .List(i)
                .List(i) = .List(i + 1)
                .List(i + 1) = temp
                blnUnsorted = True
                Exit For
            End If
        Next i
    Loop While blnUnsorted = True
'Close the With structure for the ListBox control.
End With

关键修改说明 #

将原排序逻辑中的直接字符串比较If .List(i) > .List(i + 1) Then，替换为StrComp函数结合vbTextCompare参数的比较方式：

• StrComp函数返回比较结果：大于0表示前者在文本排序中靠后，等于0表示相等，小于0表示前者靠前
• vbTextCompare参数会忽略大小写进行文本比较，完全符合需求

内容的提问来源于stack exchange，提问作者34653120