在Public Function中填充ComboBox.List时出现「下标越界」错误求助

错误原因分析 #

你的函数里存在两个关键问题，导致无法找到目标工作表或列表对象：

• 引用工作表时多余添加了单引号：Sheets("'" & ExcelSheetName & "'")，当工作表名无空格或特殊字符时，不需要用单引号包裹，这会让代码去查找名为'Table'（带单引号）的工作表，实际不存在该名称的工作表。
• 引用列表对象时多了一个末尾空格：ListObjects("'" & ExcelQueryName & " '")，最后一个单引号前的空格，会让代码查找名为'PowerQuery '（带末尾空格）的列表对象，和实际表名PowerQuery不匹配。

修复后的代码 #

Public Function AutoCompile_List(ComboBox As Object, ExcelSheetName As String, ExcelQueryName As String)
    Dim MyTable As ListObject
    Dim myArray As Variant
    
    'Automatically populate ComboBox.List
    Set MyTable = ThisWorkbook.Sheets(ExcelSheetName).ListObjects(ExcelQueryName)
    ' 增加空表判断，避免无数据行时触发错误
    If Not MyTable.DataBodyRange Is Nothing Then
        myArray = MyTable.DataBodyRange
        ComboBox.List = myArray
    Else
        ComboBox.Clear ' 表为空时清空ComboBox
    End If
End Function

调用示例 #

和你原本计划的调用方式一致：

Private Sub CommandButton_Click()
    Call AutoCompile_List(myComboBox, "Table", "PowerQuery")
End Sub

额外优化说明 #

新增的If Not MyTable.DataBodyRange Is Nothing判断，能避免当列表对象没有数据行时，myArray = MyTable.DataBodyRange触发新的运行时错误。

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