Excel VBA合并两个矩阵时出现REF错误，请问如何解决？

问题原因 #

• 首要错误：工作表调用的VBA自定义函数不允许直接修改工作表单元格内容，你代码末尾那段Cells(i, j) = m(i, j)是非法操作，Excel会直接阻止函数运行，触发错误。
• 语法错误：原有代码中If j <= s的判断分支没有写End If闭合，运行会直接报错。
• 逻辑错误：函数声明了返回Variant()类型的数组，但你最终没有把拼接好的数组m赋值给函数名v2，无法把结果返回给单元格区域。
• 逻辑漏洞：你默认右矩阵只有1列、行数和左矩阵完全相等，没有做兼容判断，如果右矩阵行列数不符合预期也会触发下标越界或者返回结果不全。
• 操作问题：作为数组函数使用时，如果选中的输出区域大小小于拼接后的矩阵大小，也会出现#REF!错误。

修正后的代码 #

Public Function v2(LeftMatrix As Range, RightMatrix As Range) As Variant()
    Dim e As Long, s As Long, r_col As Long
    ' 先判断两个矩阵行数是否一致，不一致直接返回REF错误
    If LeftMatrix.Rows.Count <> RightMatrix.Rows.Count Then
        v2 = CVErr(xlErrRef)
        Exit Function
    End If
    
    e = LeftMatrix.Rows.Count
    s = LeftMatrix.Columns.Count
    r_col = RightMatrix.Columns.Count
    
    ' 按两个矩阵的总列数定义结果数组
    ReDim m(1 To e, 1 To s + r_col) As Variant
    
    Dim i As Long, j As Long
    ' 填充左侧矩阵内容
    For i = 1 To e
        For j = 1 To s
            m(i, j) = LeftMatrix.Cells(i, j).Value
        Next j
        ' 填充右侧矩阵内容
        For j = 1 To r_col
            m(i, s + j) = RightMatrix.Cells(i, j).Value
        Next j
    Next i
    
    ' 把结果数组返回给函数
    v2 = m
End Function

使用方法 #

1. 选中要输出合并后矩阵的空白区域，区域大小要和「左矩阵行数 × (左矩阵列数+右矩阵列数)」完全一致
2. 公式栏输入=v2(选中左矩阵区域, 选中右矩阵区域)
3. 按Ctrl+Shift+Enter（Excel 2019及以前版本）或直接按回车（Excel 365/2021支持动态数组）即可输出结果。

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