VBA-对列表框中的数据进行排序，排序有效但列表框中的数据未更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/345315/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA-Sorting the data in a listbox, sort works but data in listbox not changed
提问by
A listbox is passed, the data placed in an array, the array is sort and then the data is placed back in the listbox. The part that does work is putting the data back in the listbox. Its like the listbox is being passed by value instead of by ref.
传递一个列表框，将数据放在一个数组中，对数组进行排序，然后将数据放回列表框中。起作用的部分是将数据放回列表框中。它就像列表框是通过值而不是 ref 传递的。
Here's the sub that does the sort and the line of code that calls the sort 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
Thanks for your help.
谢谢你的帮助。
回答by GSerg
You can't pass objects by value. Since you're not going to return another instance of listbox to the caller, you should declare LP as ByVal. That does not affect the code though. It works and the list gets sorted. I think you omitted some importand details.
您不能按值传递对象。由于您不会将另一个列表框实例返回给调用者，因此您应该将 LP 声明为 ByVal。但这并不影响代码。它有效并且列表被排序。我想你省略了一些重要的细节。
回答by barrowc
This works for me on Excel 2003 on a very basic UserForm with a single ListBox called ListBox1:
这对我在 Excel 2003 上的一个非常基本的用户窗体上工作，它有一个名为 ListBox1 的列表框：
Private Sub UserForm_Initialize()
ListBox1.AddItem "john"
ListBox1.AddItem "paul"
ListBox1.AddItem "george"
ListBox1.AddItem "ringo"
SortListBox ListBox1
End Sub
and then your SortListBox as written apart from fixing the three comments which start with ! rather than '
然后你的 SortListBox 除了修复以 ! 而不是 '
The only difference to your initializer is the name (UserForm_Initializevs InitializeForm). Make sure to use the object and event selectors at the top of the code page for the userform to ensure that the event handlers get named correctly
与您的初始化程序的唯一区别是名称（UserForm_Initializevs InitializeForm）。确保使用用户表单代码页顶部的对象和事件选择器，以确保正确命名事件处理程序
回答by TerrorAustralis
I dont know if this would work for you but try it this way.
我不知道这是否适合你，但试试这种方式。
First, make an array of all the items in the list box
首先，将列表框中的所有项目组成一个数组
Pass that array to your function
将该数组传递给您的函数
Sort that array
对该数组进行排序
return the array to the main program
将数组返回给主程序
clear the listbox
清除列表框
overwrite the listbox items with the new array
用新数组覆盖列表框项
