VBA中无法访问ListBox数组，无法将工作表指定数据传入ListBox的问题求助

Hey Carlos, let's get that ListBox working exactly how you want it! The core problem here is trying to access ListBox rows before they actually exist—let's break this down and fix it step by step.

Why Your Current Code Fails #

When you first initialize the ListBox, it’s completely empty, so .ListCount equals 0. That means .ListCount - 1 calculates to -1, which is an invalid index (you can’t target a row that doesn’t exist yet). Your test lines like .List(0, 0) = "text" fail for the same reason: there are no rows in the ListBox to assign values to.

The working examples (List = inputws.Range(...)) work because Excel automatically populates the ListBox with rows from the range, creating valid entries behind the scenes before assigning values.

Solution 1: Add a Row First, Then Assign Values #

The simplest fix is to explicitly add an empty row to the ListBox before you try to populate its columns. This creates a valid index you can target:

Option Explicit
Private Sub UserForm_Initialize()
    With ListBox1
        .Clear
        .ColumnHeads = True
        .ColumnCount = 9
        .ColumnWidths = "90,90,90,90,90,90,90,90,90"
        
        Dim cell As Range
        Dim lastrw As Long
        lastrw = inputws.Cells(Rows.Count, 1).End(xlUp).Row
        
        For Each cell In inputws.Range("A2:A" & lastrw)
            If cell.Offset(0, 4).Value = "Sale" Then
                ' Add an empty row first—this increases ListCount by 1
                .AddItem
                ' Now .ListCount - 1 points to the new, empty row
                .List(.ListCount - 1, 0) = inputws.Cells(cell.Row, 5).Value
                .List(.ListCount - 1, 1) = inputws.Cells(cell.Row, 6).Value
                ' Continue assigning values to columns 2 through 8 as needed
                ' .List(.ListCount - 1, 2) = inputws.Cells(cell.Row, 7).Value
                ' ...
            End If
        Next
    End With
End Sub

Solution 2: Use an Array for Better Performance (Recommended) #

If you’re dealing with a lot of rows, calling .AddItem in a loop can cause the UserForm to flicker and slow down. A smoother, faster approach is to collect all filtered data into an array first, then assign it to the ListBox in one go:

Option Explicit
Private Sub UserForm_Initialize()
    ' Set up ListBox properties first
    With ListBox1
        .Clear
        .ColumnHeads = True
        .ColumnCount = 9
        .ColumnWidths = "90,90,90,90,90,90,90,90,90"
    End With
    
    ' Ensure inputws is defined (replace with your actual worksheet name if needed)
    Dim inputws As Worksheet
    Set inputws = ThisWorkbook.Worksheets("YourInputSheet")
    
    Dim lastrw As Long
    lastrw = inputws.Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Dynamic array to store filtered "Sale" rows
    Dim saleData() As Variant
    Dim currentRow As Long
    currentRow = 0
    
    Dim cell As Range
    For Each cell In inputws.Range("A2:A" & lastrw)
        If cell.Offset(0, 4).Value = "Sale" Then
            currentRow = currentRow + 1
            ' Resize array to hold the new row (preserve existing data)
            ReDim Preserve saleData(1 To currentRow, 1 To 9)
            
            ' Assign values to the array (match columns to your needs)
            saleData(currentRow, 1) = inputws.Cells(cell.Row, 5).Value ' ListBox column 0 = E column
            saleData(currentRow, 2) = inputws.Cells(cell.Row, 6).Value ' ListBox column 1 = F column
            ' Add more columns here as required
            ' saleData(currentRow, 3) = inputws.Cells(cell.Row, 7).Value
            ' ...
        End If
    Next cell
    
    ' Only assign the array if we have data to show
    If currentRow > 0 Then
        ListBox1.List = saleData
    End If
End Sub

This method minimizes ListBox redraws and is far more efficient for large datasets.

Quick Notes #

• Double-check that inputws is properly defined (if it’s a module-level variable, make sure it’s set before the UserForm initializes).
• When using .ColumnHeads = True, ensure your worksheet has valid headers above the data range—Excel pulls headers from the row above your data if you assign a range directly, but with our filtered methods, you’re in full control.

内容的提问来源于stack exchange，提问作者Carlos Andres