excel 对列表框中的数据进行排序，排序工作，但列表框中的数据不变

1aaf6o9v 于 2023-05-19 发布在其他

关注(0)|答案(4)|浏览(205)

传递一个列表框，将数据放在数组中，对数组进行排序，然后将数据放回列表框中。有效的部分是将数据放回列表框。这就像列表框是通过值而不是通过ref传递的。
下面是执行排序的sub和调用排序sub的代码行。

Private Sub SortListBox(ByRef LB As MSForms.ListBox)

Dim First As Integer
Dim Last As Integer
Dim NumItems As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim TempArray() As Variant

ReDim TempArray(LB.ListCount)

First = LBound(TempArray)               ' this works correctly
Last = UBound(TempArray) - 1            ' this works correctly

For i = First To Last
    TempArray(i) = LB.List(i)           ' this works correctly
Next i

For i = First To Last
    For j = i + 1 To Last
        If TempArray(i) > TempArray(j) Then
            Temp = TempArray(j)
            TempArray(j) = TempArray(i)
            TempArray(i) = Temp
        End If
    Next j
Next i                               ! data is now sorted

LB.Clear                             ! this doesn't clear the items in the listbox

For i = First To Last
    LB.AddItem TempArray(i)          ! this doesn't work either
Next i

End Sub

Private Sub InitializeForm()

'   There's code here to put data in the list box    

Call SortListBox(FieldSelect.CompleteList)

End Sub

谢谢你的帮助。

excel

来源：https://stackoverflow.com/questions/345315/vba-sorting-the-data-in-a-listbox-sort-works-but-data-in-listbox-not-changed

4条答案

按热度按时间

gmxoilav1#

这对我在Excel 2003中的一个非常基本的UserForm上使用一个名为ListBox 1的列表框是有效的：

Private Sub UserForm_Initialize()

ListBox1.AddItem "john"
ListBox1.AddItem "paul"
ListBox1.AddItem "george"
ListBox1.AddItem "ringo"

SortListBox ListBox1

End Sub

然后你的SortListBox除了修复以开头的三个注解之外！而不是“
与初始化器的唯一区别是名称（UserForm_Initialize vs InitializeForm）。请确保使用userform代码页顶部的对象和事件选择器，以确保事件处理程序的命名正确

赞(0）回复(0）举报 2023-05-19

iswrvxsc2#

不能通过值传递对象。由于您不打算将listbox的另一个示例返回给调用者，所以应该将LP声明为ByVal。但这并不影响代码。它起作用，列表得到排序。我认为你遗漏了一些重要的细节。

赞(0）回复(0）举报 2023-05-19

3z6pesqy3#

下面是我如何使用它，例如，w/一个关系字典和两个列：

Private Sub UserForm_Initialize()

 Call HideTitleBar(Me)

 Set ExtraFiltersDic = CreateObject("scripting.dictionary")
 ExtraFiltersDic.CompareMode = 1
 Set ExtraFiltersDic = GetExtraFiltersDic()

 Dim k
 For Each k In ExtraFiltersDic.Keys
  ListBox1.AddItem k
 Next
 
 Call SortListBox(ListBox1, ListBox2, ExtraFiltersDic)
  
End Sub

Public Sub SortListBox(ByRef ListBox As MSForms.ListBox, Optional ByRef ListBox2 As MSForms.ListBox, Optional ByRef RelationalDic As Object)

 Dim First As Integer, Last As Integer, NumItems As Integer
 Dim i As Integer, j As Integer
 Dim TempArray() As Variant, Temp As String

 ReDim TempArray(ListBox.ListCount)

 First = LBound(TempArray)
 Last = UBound(TempArray) - 1
 For i = First To Last
    TempArray(i) = ListBox.List(i)
 Next i
 
 For i = First To Last
    For j = i + 1 To Last
        If TempArray(i) > TempArray(j) Then
            Temp = TempArray(j)
            TempArray(j) = TempArray(i)
            TempArray(i) = Temp
        End If
    Next j
 Next i

 ListBox.Clear

 If Not ListBox2 Is Nothing And Not RelationalDic Is Nothing Then
  Set KeyValDic = CreateObject("scripting.dictionary")
  Set KeyValDic = RelationalDic
 End If

 For i = First To Last
    ListBox.AddItem TempArray(i)
    If Not ListBox2 Is Nothing And Not RelationalDic Is Nothing Then
     ListBox2.AddItem KeyValDic(TempArray(i))
    End If
 Next i

End Sub

赞(0）回复(0）举报 2023-05-19

dphi5xsq4#

我不知道这是否对你有用，但试试这样做。
首先，将列表框中的所有项目组成一个数组
将该数组传递给函数
对那个数组排序
返回数组到主程序
清除列表框
用新数组覆盖列表框项

赞(0）回复(0）举报 2023-05-19