如何用私有过程传递的变量字符串替换SQL的WHERE子句？

你的核心问题是test函数里没有使用传入的xref变量，而是硬写了WHERE子句，同时代码里还有变量声明、对象释放的小问题，以下是修正后的完整代码：

1. 修正后的test过程（改为Sub更合适） #

Option Explicit
Private Sub test(ByVal xref As String)
    Dim conn As Object, rs As Object, sq As String

    ' 建立数据库连接
    Set conn = CreateObject("ADODB.Connection")
    With conn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"
        .Open
    End With

    ' 拼接SQL时直接使用传入的xref作为WHERE子句
    sq = "SELECT T1.[A],T1.[B],T1.[C],T1.[D] " & _
        "FROM [Data$] T1 " & _
        xref & _
        " GROUP BY T1.[A],T1.[B],T1.[C],T1.[D]"
      
    ' 执行查询并绑定到ListBox
    Set rs = conn.Execute(sq)
    With Me.ListBox
        .Clear
        .Column = rs.GetRows
    End With
    
    ' 清理资源
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

2. 修正ComboBox的Change事件 #

Private Sub ComboBox1_Change()
    Dim xref As String
    xref = "WHERE T1.[A] IN ('" & ComboBox1.Value & "')"
    test xref
End Sub

Private Sub ComboBox2_Change()
    Dim xref As String
    xref = "WHERE T1.[B] IN ('" & ComboBox2.Value & "')"
    test xref
End Sub

关键调整说明 #

• 把test从Function改为Sub，因为不需要返回值，逻辑更直接
• 直接将传入的xref变量拼接到SQL语句中，替换原有的硬写WHERE子句
• 补充了数据库连接和记录集的关闭、对象释放，避免内存泄漏
• 用ListBox.Clear替代.Value=""，清空列表更彻底
• 规范了变量声明，比如给conn指定As Object类型

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