如何从VBA字典更新工作表而不出现性能问题?

在内存中创建并填充二维数组,然后将数组转储回工作表。

'at this point the dictionary is already populated similar to
'dict.Add 'ID_01', Array('val1', 'val2', 'val3')

dim i as long, arr as variant

with sheet a

    arr = .range(.cells(2, "A"), .cells(.rows.count, "A").end(xlup)).value2
    redim preserve arr(lbound(arr, 1) to ubound(arr, 1), 1 to 4)
    'if sheet A already has values that only require updating,
    'then use this instead
    'arr = .range(.cells(2, "A"), .cells(.rows.count, "A").end(xlup).offset(0, 3)).value2

    for i=lbound(arr, 1) to ubound(arr, 1)
        If dict.exists(arr(i, 1)) Then
            arr(i, 2) = dict.Item(arr(i, 1))(0)
            arr(i, 3) = dict.Item(arr(i, 1))(1)
            arr(i, 4) = dict.Item(arr(i, 1))(2)
        end if
    next i

    .cells(2, "A").resize(ubound(arr, 1), ubound(arr, 2)) = arr

end with