Excel：运行时错误 '1004'将 VBA 数组分配给范围而不事先调用工作表。激活

Question

我在2d VBA动态数组中生成了一个大的双精度矩阵。当我尝试将数组分配给Excel范围对象时，我收到以下错误:

Runtime Error '1004':
Application-defined or Object-defined Error

除非，也就是说，我调用工作表。在分配前激活。(数组的大小约为88 x 1150)下面是代码片段:

Dim lIndxRow As Long, lIndxRowBase As Long, lIndxRowLast As Long, lIndxCol As Long, lIndxColBase As Long, lIndxColLast As Long
lIndxRow = [Close_FirstRow]
lIndxRowBase = [Close_FirstRow]
lIndxRowLast = [Close_LastRow]
lIndxColBase = [Close_FirstCol]
lIndxColLast = [Close_LastCol]
Dim Mat() As Double
ReDim Mat(lIndxRowBase To lIndxRowLast, lIndxColBase To lIndxColLast + 1)

While lIndxRow <= lIndxRowLast
    nSharesTotal = 0#
    While lIndxCol <= lIndxColLast
        Dim nShares As Double
        '
        ' Calculate value for nShares
        '
        Mat(lIndxRow, lIndxCol) = nShares
        nSharesTotal = nSharesTotal + nShares
        lIndxCol = lIndxCol + 1
    Wend
    Mat(lIndxRow, lIndxCol) = nSharesTotal
    lIndxRowPrev = lIndxRow
    lIndxRow = lIndxRow + 1
Wend
' no error when next line uncommented
'Worksheets("Share Pos").Activate
Worksheets("Share Pos").Range(Cells(lIndxRowBase, lIndxColBase), Cells(lIndxRowLast, lIndxColLast + 1)).Value2 = Mat

Answer 1

问题是cell对象不是完全限定的。你必须让他们完全符合条件。试试这个(注意到单元格前的点吗?

With Worksheets("Share Pos")
    .Range(.Cells(lIndxRowBase, lIndxColBase), .Cells(lIndxRowLast, _
    lIndxColLast + 1)).Value2 = Mat
End With