Excel VBA如何将二维数组写入三角形非矩形命名区域且不遍历单元格

问题核心原因 #

VBA中对非连续的多区域Range赋值数组时，会将数组按列顺序依次填充到每个子区域（Area）中，三角形命名区域本质是多个不连续的行/单元格组成的多区域集合，所以直接赋值只会把数组第一列重复填充到所有子区域，得到错误结果。

最优解决方案：内存数组中转法 #

该方案仅与工作表进行2次IO交互（读原有数据、写回修改后数据），所有修改操作在内存中完成，效率接近直接写入矩形区域，且不会覆盖三角形外的原有数据：

Sub 三角形区域高效赋值()
    Dim arr() As Double, tempArr As Variant
    Dim rngTri As Range, rngBound As Range
    Dim cell As Range, rowOffset As Long, colOffset As Long
    Dim i As Long, j As Long
    
    ' 初始化你的源数组
    ReDim arr(1 To 4, 1 To 4)
    For i = 1 To 4
        For j = 1 To 4
            arr(i, j) = i + j
        Next j
    Next i
    
    ' 绑定三角形命名区域
    Set rngTri = Worksheets("test").Range("TEST_TRIANGLE")
    ' 获取三角形的最小包围矩形范围
    Set rngBound = rngTri.Worksheet.Cells(rngTri.Row, rngTri.Column).Resize(rngTri.Rows.Count, rngTri.Columns.Count)
    
    ' 读取包围矩形的原有数据到内存数组，避免覆盖非三角区域内容
    tempArr = rngBound.Value
    ' 计算矩形左上角相对偏移，适配任意位置的命名区域
    rowOffset = rngBound.Row - 1
    colOffset = rngBound.Column - 1
    
    ' 内存中替换三角形对应位置的数值，此处遍历仅操作内存，无工作表IO，速度极快
    For Each cell In rngTri
        tempArr(cell.Row - rowOffset, cell.Column - colOffset) = arr(cell.Row - rowOffset, cell.Column - colOffset)
    Next
    
    ' 一次性将修改后的数组写回工作表，仅1次写入操作
    rngBound.Value = tempArr
End Sub

简化方案：规则三角按行赋值 #

如果你的三角形是规则的上三角/下三角（每行单元格连续、数量随行号线性变化），可以采用按行批量赋值的方案，IO次数等于行数，也远高于逐单元格写入效率：

Sub 规则三角按行赋值()
    Dim arr() As Double
    Dim rngTri As Range, i As Long, j As Long
    
    ' 初始化源数组
    ReDim arr(1 To 4, 1 To 4)
    For i = 1 To 4
        For j = 1 To 4
            arr(i, j) = i + j
        Next j
    Next i
    
    Set rngTri = Worksheets("test").Range("TEST_TRIANGLE")
    ' 逐行赋值数组对应行的内容
    For i = 1 To rngTri.Rows.Count
        rngTri.Rows(i).Value = Application.Index(arr, i, 0)
    Next i
End Sub

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