将 VBA 数组打印到 Excel 单元格中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15032339/
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
Print VBA Array in to Excel Cells
提问by user1977802
I am trying to print a VBA created array into cells of an excel spreadsheet. Percolation and Runoff values keep getting "subscript out of range" errors Am I creating hose arrays correctly? I have merged the calculation and print sub function into one.
我正在尝试将 VBA 创建的数组打印到 Excel 电子表格的单元格中。渗透和径流值不断出现“下标超出范围”错误我是否正确创建了软管阵列？我已将计算和打印子功能合二为一。
NumMonth = 12
Dim col As Long
Dim rw As Long
rw = 4
col = 13
Range(Cells(rw, col), Cells(rw + NumMonth - 1, col)).Value = _
Application.Transpose(WC)
Range(Cells(rw, col + 1), Cells(rw + NumMonth - 1, col + 1)).Value = _
Application.Transpose(Runoff)
Range(Cells(rw, col + 2), Cells(rw + NumMonth - 1, col + 2)).Value = _
Application.Transpose(Percolation)
End Sub
回答by scott
Its application.transpose instead of worksheetfunction
它的 application.transpose 而不是 worksheetfunction
Range(Cells(rw, col), Cells(rw + NumMonth - 1, col)).Value = _
application.Transpose(WC)
Here is a test sub
这是一个测试子
Sub test()
Dim myarray As Variant
'this is a 1 row 5 column Array
myarray = Array(1, 2, 3, 4, 5)
'This will fail because the source and destination are different shapes
Range("A1:A5").Value = myarray
'If you want to enter that array into a 1 column 5 row range like A1:A5
Range("A1:A5").Value = Application.Transpose(myarray)
End Sub
You will get an error if you call an array you created in another sub. The reason for this can be seen in the locals window when you step through your code. When you run the sub that creates your array it will be shown in locals, when the sub ends it will disappear, meaning it is no longer stored in memory. To refer to a variable or array from another sub you must pass it.
如果调用在另一个子程序中创建的数组，则会出现错误。当您单步执行代码时，可以在本地窗口中查看其原因。当您运行创建数组的 sub 时，它将显示在 locals 中，当 sub 结束时它将消失，这意味着它不再存储在内存中。要从另一个子引用变量或数组，您必须传递它。
Passing arrays or variables can be done in different ways here are a few links HereAnd Here
回答by glh
Your Precipand RefETare always equal, is this ment to be?
你Precip和RefET永远是平等的，这是精神吗？
Precip(i) = Cells(4 + i, 2).Value
RefET(i) = Cells(4 + i, 2).Value
Also, when you set the Runoffand Percolationarrays they do not get set to anything if your if statement is not met (below), and I could meet it with the data provided:
此外，当您设置Runoff和Percolation数组时，如果不满足您的 if 语句（如下），它们不会被设置为任何内容，我可以使用提供的数据满足它：
If (fc - WC(j - 1) + RefET(i) * dz < Precip(i) * dz) then
I'd add somthing to make sure there is always a value in them:
我会添加一些东西以确保它们始终具有价值：
If (fc - WC(j - 1) + RefET(i) * dz < Precip(i) * dz) Then
Runoff(i) = (Precip(i) - (fc - WC(j - 1) + RefET(i)) * dz) * 0.5
Percolation(i) = (Precip(i) - (fc - WC(i - 1) + RefET(i)) * dz) * 0.5
WC(j) = fc
Else
Runoff(i) = 0
Percolation(i) = 0
WC(j) = WC(j - 1) + Precip(i) - RefET(i) / dz
End If
Once doing this I found out you hadn't ReDim'd the Runoffand Percolationvariables.you'll need to add the following to your WaterBalanceReadsub.
这样做后，我发现您没有ReDim使用Runoff和Percolation变量。您需要将以下内容添加到您的WaterBalanceRead子文件中。
ReDim Runoff(1 To NumMonth + 1)
ReDim Percolation(1 To NumMonth + 1)
