Excel: Runtime Error '1004' assigning VBA Array to Range without prior call to Worksheet.Activate

Question

I generate a large matrix of doubles in a 2d VBA Dynamic Array. When I attempt to assign the Array to a Excel Range object, I receive the following error:

Runtime Error '1004':

Application-defined or Object-defined Error

Unless, that is, I call Worksheet.Activate before the assignment. (The size of the Array is about 88 x 1150) Here is the code snip:

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

The problem is that the cells object is not fully qualified. You have to fully qualify them. Try this (Notice the DOT before CELLS?

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