Excel：在未事先调用 Worksheet.Activate 的情况下将 VBA 数组分配给 Range 时出现运行时错误“1004”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16195589/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: Runtime Error '1004' assigning VBA Array to Range without prior call to Worksheet.Activate
提问by ThomasMcLeod
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:
我在二维 VBA 动态数组中生成了一个大的双精度矩阵。当我尝试将 Array 分配给 Excel Range 对象时，收到以下错误：
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:
除非，也就是说，我在分配之前调用 Worksheet.Activate。（数组的大小约为 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
回答by Siddharth Rout
The problem is that the cells object is not fully qualified. You have to fully qualify them. Try this (Notice the DOT before CELLS?
问题是单元格对象没有完全限定。你必须完全符合他们的要求。试试这个（注意 CELLS 之前的 DOT？
With Worksheets("Share Pos")
.Range(.Cells(lIndxRowBase, lIndxColBase), .Cells(lIndxRowLast, _
lIndxColLast + 1)).Value2 = Mat
End With
