Excel单元格调用VBA函数返回数组实现多单元格取值求助

Excel 对从工作表单元格直接调用的自定义函数（UDF）有严格的行为限制：仅允许修改调用该函数的单元格本身的返回值，不允许修改其他单元格内容、工作表结构、应用设置等对象，因此你尝试在函数内写入其他单元格的操作会直接触发#VALUE!错误，属于设计层面的限制，和代码语法无关。

你需要的二维数组多单元格调用需求，有两种成熟的实现方式：

方案1：溢出数组/多单元格数组公式（适合返回整块连续区域的结果） #

直接让自定义函数返回完整二维数组，Excel会自动把数组值填充到对应区域：

Function GetResultArray() As Variant
    ' 定义二维数组，可根据你的需求调整维度大小
    Dim resultArr(1 To 3, 1 To 4) As Variant
    Dim i As Integer, j As Integer
    
    ' 这里替换为你自己的数组生成逻辑
    For i = 1 To 3
        For j = 1 To 4
            resultArr(i, j) = "第" & i & "行第" & j & "列值"
        Next j
    Next i
    
    GetResultArray = resultArr
End Function

使用方法：

• 如果使用Excel 365/2021及以上版本：任意空白单元格输入=GetResultArray()，数组会自动溢出填充到对应大小的连续区域
• 如果使用旧版Excel：选中和数组维度大小一致的单元格区域（比如上述代码是3行4列，就选3行4列的空白区域），输入公式后按Ctrl+Shift+Enter确认数组公式即可。

方案2：带索引的单单元格取值函数（适合分散位置调用数组不同位置值） #

如果你需要在工作表不连续的多个位置分别调用数组的不同值，可以写带索引参数的函数，同时缓存数组避免重复计算：

' 模块级变量，缓存已生成的数组和缓存状态
Private cachedArray As Variant
Private arrayCached As Boolean

Function GetArrayItem(rowIndex As Integer, colIndex As Integer, Optional refresh As Boolean = False) As Variant
    ' 参数说明：rowIndex行索引、colIndex列索引，refresh为True时强制刷新数组
    ' 数组未缓存或需要刷新时重新生成数组
    If refresh Or Not arrayCached Then
        ' 这里替换为你自己的数组生成逻辑
        ReDim cachedArray(1 To 10, 1 To 5) As Variant
        Dim i As Integer, j As Integer
        For i = 1 To 10
            For j = 1 To 5
                cachedArray(i, j) = i * j
            Next j
        Next i
        arrayCached = True
    End If
    
    ' 索引越界判断，避免返回不可预期的错误
    If rowIndex >= LBound(cachedArray, 1) And rowIndex <= UBound(cachedArray, 1) And _
       colIndex >= LBound(cachedArray, 2) And colIndex <= UBound(cachedArray, 2) Then
        GetArrayItem = cachedArray(rowIndex, colIndex)
    Else
        GetArrayItem = CVErr(xlErrIndex) ' 越界返回#INDEX!标准错误
    End If
End Function

使用方法：
在需要调用的单元格输入=GetArrayItem(行号,列号)即可，比如要取数组第2行第3列的值，输入=GetArrayItem(2,3)；如果数组逻辑有更新需要刷新缓存，在任意调用公式里加第三个参数为TRUE即可：=GetArrayItem(2,3,TRUE)。

注意：不推荐使用Evaluate、工作表事件等非常规手段实现UDF修改其他单元格的需求，这类方法稳定性差，容易触发Excel崩溃或者计算异常，数组方案是符合Excel设计规范的最优解。

内容的提问来源于stack exchange，提问作者JOSON MATHEW JOSE