Excel VBA AutoRange函数失效求助：数组转自适应单元格区域失败

你的问题核心在于试图用工作表函数修改其他单元格的内容——Excel的工作表函数是纯函数式的，它们只能返回值，不能产生“副作用”（比如修改其他单元格的公式或内容），这是Excel的安全和设计规则，所以你的AutoRange函数从根本上无法实现预期效果。另外你的代码还有几个小问题，我一步步帮你解决：

原代码的关键问题 #

1. 工作表函数不能使用Selection：在工作表函数中，Selection不会指向你输入公式的单元格，应该用Application.Caller来获取调用函数的单元格，但即使这样，你也不能修改其他单元格的FormulaArray。
2. 变量声明不规范：Dim nb_rows, nb_cols As Integer 只有nb_cols是Integer类型，nb_rows会被默认声明为Variant，同理current_cell, target_range As Range也是一样的问题，应该分开声明每个变量的类型。
3. 工作表函数无法修改其他单元格：这是最核心的限制，Excel不允许工作表函数修改除自身返回值以外的单元格内容。

解决方案 #

根据你的需求，我提供两种可行的方案：

方案1：使用VBA宏（过程）代替工作表函数 #

宏可以直接操作工作表单元格，完全适配你的需求。以下是修改后的代码：

Sub AutoPopulateCSVArray(csvPath As String)
    Dim csvArray As Variant
    Dim targetCell As Range
    Dim nbRows As Long, nbCols As Long
    
    ' 获取用户选择的起始单元格（确保只选一个单元格）
    Set targetCell = Application.Selection
    If targetCell.Cells.Count > 1 Then
        MsgBox "请选择单个单元格作为数据起始位置！", vbExclamation
        Exit Sub
    End If
    
    ' 调用你的CSVtoArray函数读取CSV并转为数组
    csvArray = CSVtoArray(csvPath)
    
    ' 计算数组的实际行数和列数（兼容数组从0或1开始的情况）
    nbRows = UBound(csvArray, 1) - LBound(csvArray, 1) + 1
    nbCols = UBound(csvArray, 2) - LBound(csvArray, 2) + 1
    
    ' 清空目标区域的旧数据（可选，避免残留内容）
    targetCell.Resize(nbRows, nbCols).ClearContents
    
    ' 将数组写入目标区域
    targetCell.Resize(nbRows, nbCols).Value = csvArray
End Sub

使用方法：

1. 打开VBA编辑器（Alt+F11），把这段代码粘贴到你的模块中。
2. 返回Excel，选择你想要显示数据的起始单元格。
3. 按Alt+F8打开宏窗口，选择AutoPopulateCSVArray，点击执行，输入你的CSV文件路径即可。
4. （可选）你可以给这个宏添加一个工作表按钮，点击按钮就能快速执行，不用每次打开宏窗口。

方案2：利用Excel动态数组自动溢出（适用于Excel 365/2021及以上版本） #

如果你使用的是支持动态数组的Excel版本，根本不需要AutoRange函数——只要让你的CSVtoArray函数返回二维数组，输入公式后Excel会自动把数组内容“溢出”到下方和右方的单元格，完全适配数组大小。

只需要确保你的CSVtoArray函数返回的是二维数组即可，示例如下：

Function CSVtoArray(csvPath As String) As Variant
    ' 这里保留你读取CSV的现有代码
    ' 示例读取逻辑（你可以替换成自己的代码）：
    Dim fileNum As Integer
    Dim lineText As String
    Dim rowData As Variant
    Dim resultArray() As String
    Dim rowIndex As Long, colIndex As Integer
    
    fileNum = FreeFile()
    Open csvPath For Input As #fileNum
    
    rowIndex = 0
    Do Until EOF(fileNum)
        Line Input #fileNum, lineText
        rowData = Split(lineText, ",")
        rowIndex = rowIndex + 1
        ' 调整数组大小
        ReDim Preserve resultArray(1 To rowIndex, 1 To UBound(rowData) + 1)
        ' 填充当前行数据
        For colIndex = 1 To UBound(rowData) + 1
            resultArray(rowIndex, colIndex) = rowData(colIndex - 1)
        Next colIndex
    Loop
    Close #fileNum
    
    CSVtoArray = resultArray
End Function

使用方法：

直接在单元格中输入=CSVtoArray("C:\your\csv\path.csv")，按下回车后，Excel会自动把数组内容填充到对应的行和列，完全适配CSV文件的大小。

总结 #

放弃用工作表函数实现AutoRange的思路，因为Excel不允许工作表函数修改其他单元格。根据你的Excel版本选择上面的方案：旧版Excel用宏，新版Excel用动态数组溢出，都能完美解决你的需求。

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