优化VBA切换按钮代码：实现动态行适配与XLOOKUP精简

核心优化思路 #

• 用**ListObject（Excel表格）**替代固定单元格范围，自动适配数据源新增行
• 利用XLOOKUP的多列返回特性，将重复调用合并为单行代码
• 固定指定年度周期对应的列引用，保证逻辑稳定

优化后的完整代码 #

Private Sub ToggleButton1_Click()
    Dim srcTable As ListObject
    Dim targetRange As Range
    Dim lookupCell As Range
    Dim returnCols As Range
    Dim xlookupFormula As String
    
    ' 绑定数据源为Excel表格（先把数据源区域转为表格，命名为"DataTable"）
    Set srcTable = ThisWorkbook.Worksheets("数据源工作表名").ListObjects("DataTable")
    
    ' 固定年度周期对应的列（示例：表格第2到第5列，根据实际列位置调整）
    Set returnCols = srcTable.ListColumns(2).Range.Resize(, 4)
    
    ' 定义查找基准单元格和目标输出区域
    Set lookupCell = ThisWorkbook.Worksheets("展示工作表名").Range("A2")
    Set targetRange = lookupCell.Offset(0, 1).Resize(srcTable.ListRows.Count, returnCols.Columns.Count)
    
    ' 生成单行XLOOKUP数组公式，切换两组数据逻辑
    If ToggleButton1.Value Then
        ' 切换到第一组：匹配表格第1列，返回指定年度列
        xlookupFormula = "=XLOOKUP(" & lookupCell.Address & "," & srcTable.ListColumns(1).Range.Address & "," & returnCols.Address & ","""",0,1)"
    Else
        ' 切换到第二组：匹配表格第6列（按需修改匹配列），返回同样年度列
        xlookupFormula = "=XLOOKUP(" & lookupCell.Address & "," & srcTable.ListColumns(6).Range.Address & "," & returnCols.Address & ","""",0,1)"
    End If
    
    ' 批量写入数组公式，自动适配行数量
    targetRange.FormulaArray = xlookupFormula
End Sub

关键细节说明 #

• 动态行适配：将数据源区域转为Excel表格（选中区域→Ctrl+T）后，ListObject会自动识别新增行，无需手动修改代码中的范围参数
• 精简XLOOKUP调用：通过指定returnCols作为返回列范围，单次XLOOKUP即可返回多列年度数据，替代重复的多列调用
• 固定列引用：ListColumns(2).Range.Resize(,4)明确锁定年度周期对应的列位置，不会因行新增或列调整而偏移
• 批量公式写入：使用FormulaArray直接将数组公式应用到整个目标区域，避免循环写入的冗余代码

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