VBA Excel填充具有多个列的ListBox [英] VBA Excel Populate ListBox with multiple columns

问题描述

对于某些人来说，这可能是一个便宜的问题，但我对如何填充列表框完全感到困惑.

This may be a cheap question for some but I'm totally confused on how to populate my listbox.

使用此行，我可以填充列表框，如下所示:
ListBox1.List = Sheets("Sheet1").Cells(1, 1).CurrentRegion.Value
或

Using this line I can populate the listbox as shown below:
ListBox1.List = Sheets("Sheet1").Cells(1, 1).CurrentRegion.Value
or

Dim rngName As Range
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Sheet1")
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
        If ws.Cells(i, 1).Value <> vbNullString Then Me.ListBox1.AddItem 
        ws.Cells(i, 1).Value
Next i

以下是我计划用于填充列表框的数据，并且这些数据是渐进式的.只有该列具有修复计数.

Below is the data I'm planning to use to populate the list box and is progressive. Only the column has the fix count.

请有人告诉我如何使用 FOR LOOP 填充适合多列和多行的列表框，如上面的代码所示.任何帮助表示赞赏.谢谢.

Someone please enlighten me on how to populate a list box adapative to multiple columns and rows using FOR LOOP as shown in my code above. Any help appreciated. Thanks.

推荐答案

方法

1. 遍历数组总是比遍历更好-它快得多.
2. 创建一个具有一个内衬的变体数据字段数组甚至比Siddharth Rout所建议的重新定义预先声明的数组并将其填充到额外的循环中要快得多(尽管这是一种好方法:-) 注意:下面的代码基于上面注释中引用的他的方法，目的只是为了证明区别.
3. 用数组填充ListBox1.List(方法相同，但方向相反).

1. It's always better to loop through an array than a range - it's much faster.
2. It's even faster to create a variant data field array with a one liner instead of redimensioning a predeclared array and fill it in an extra loop as proposed by Siddharth Rout (though a good method :-) Note: The code below is based on his Approach referenced in the above comment just to demonstrate the difference.
3. Fill ListBox1.List with the array (same method, but reverse direction).

代码

Private Sub CommandButton1_Click()
' Purpose:  fill listbox with range values after clicking on CommandButton1
'           (code could be applied to UserForm_Initialize(), too)
' Note:     based on @Siddharth-Rout 's proposal at https://stackoverflow.com/questions/10763310/how-to-populate-data-from-a-range-multiple-rows-and-columns-to-listbox-with-vb
'           but creating a variant data field array directly from range in a one liner
'           (instead of filling a redimensioned array with range values in a loop)
Dim ws      As Worksheet
Dim rng     As Range
Dim MyArray                 ' variant, receives one based 2-dim data field array
'~~> Change your sheetname here
Set ws = Sheets("Sheet1")

'~~> Set you relevant range here
Set rng = ws.Range("A1:C" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

With Me.ListBox1
    .Clear
    .ColumnHeads = False
    .ColumnCount = rng.Columns.Count

    '~~> create a one based 2-dim datafield array
     MyArray = rng

    '~~> fill listbox with array values
    .List = MyArray

    '~~> Set the widths of the column here. Ex: For 5 Columns
    '~~> Change as Applicable
    .ColumnWidths = "50;50;50"
    .TopIndex = 0
End With
End Sub

其他提示

• 数组方法的另一个优点-使用.AddItem方法时，它克服仅内置 10列的内置限制.

• Another advantage of the array method - it overcomes the built-in limitation of only 10 columns when using the .AddItem method.

此外，请记住，列表框索引是基于从零开始的，因此，例如，您将获得第一行(索引0)的电子邮件地址(第3列，索引2) )通过ListBox1.List(0, 2)，而数据字段数组将自动变为基于1的2维数组.

Furthermore, keep in mind that listbox indexing is zero based, so for example you get the e-mail address (column 3, index 2) of your first item row (index 0) via ListBox1.List(0, 2), whereas the data field array becomes automatically a one based 2-dim array.

不限于使用.List方法从列表框中获取信息，您可以使用ListBox1.Column" or even create a new array out of it, which remains a 2-dim object, even if there is only ONE item (note: the Application反转行-列顺序.Transpose`方法将重新创建二维数组一维数组只有一行).

You aren't restricted to use the .List method to get Information out of the listbox, you can reverse the row - column order by using ListBox1.Column" or even create a new array out of it, which remains a 2-dim object, even if there is only ONE item (note: theApplication.Transpose` method would redim a 2 dimensional array with only one row to a 1-dim array).

最后一点:您可以通过rng = ListBox1.List轻松地将整个列表框再次转储到Excel工作表中，但要小心定义正确的范围.

A last point: you can easily dump back again the whole listbox to an Excel sheet via rng = ListBox1.List, but take care to define the correct range.

这篇关于VBA Excel填充具有多个列的ListBox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！