VBA Excel用多列填充列表框 [英] 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. 使用 one liner 创建变体数据字段数组甚至比 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 的二维数组.

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" 或甚至从中创建一个新数组，它仍然是一个 2 维对象，即使只有一个项目(注意:Application.Transpose` 方法会将只有一行的二维数组重新调整为 1-dim 数组).

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用多列填充列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！