excel - excel vba在列表视图上排序int值

Question

我需要按它们的 int 值对我的值进行排序，例如 1 2 3 10 1000

但我的代码像这样的字符串 1 10 100 2 3

这是我对 EXCEL VBA 的排序代码：

Private Sub lstview1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)


With lstview1
    .SortKey = ColumnHeader.Index - 1
    If .SortOrder = lvwAscending Then
        .SortOrder = lvwDescending
    Else
        .SortOrder = lvwAscending
    End If
    .Sorted = True
End With

结束子

我怎样才能为excel vba解决这个问题

标签： excelvba

Answer 1

Listview 按字母顺序排序，这是它的限制之一。这是我为您创建的用于对数字数据进行排序的快速示例。

代码

Option Explicit

Dim i As Long

Private Sub UserForm_Initialize()
    With ListView1
        .View = lvwReport
        
        .ColumnHeaders.Add , , "Number", 50
        .ColumnHeaders.Add , , "Fruit", 50
        
        For i = 1000 To 1 Step -1
            .ListItems.Add(, , i).SubItems(1) = "Fruit" & i
        Next
    End With
End Sub

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    With ListView1
        If ColumnHeader.Index = 1 Then
            SortDataWithNumbers
        Else
            .Sorted = True
        End If
    End With
End Sub

Sub SortDataWithNumbers()
    Dim sTemp As String * 10
    Dim lvCount As Long
    
    With ListView1
        lvCount = .ListItems.Count
        
        For i = 1 To lvCount
            sTemp = vbNullString
            
            If .SortKey Then
                'RSet - right align a string within a string variable.
                RSet sTemp = .ListItems(i).SubItems(.SortKey)
                .ListItems(i).SubItems(.SortKey) = sTemp
            Else
                RSet sTemp = .ListItems(i)
                .ListItems(i).Text = sTemp
            End If
        Next
        
        .Sorted = True
        
        For i = 1 To lvCount
            If .SortKey Then
                .ListItems(i).SubItems(.SortKey) = _
                LTrim$(.ListItems(i).SubItems(.SortKey))
            Else
                .ListItems(i).Text = LTrim$(.ListItems(i))
            End If
        Next
    End With
End Sub

在行动