英文:
How can I fix a 'Subscript out of range' error when populating an array in VBA?
问题
我正在努力找出我的代码有什么问题，以及为什么会返回上面的错误。
代码的目的是构建resArr，最终必须有94个值，当它准备好时，我需要将所有值复制到工作表的一行中。
下面的代码只是构建的第一步，但我已经遇到了错误。
wsPrm是全局变量，在另一个Sub中定义，所以它是正确的。
我知道数组的编号从0开始，这就是我用ReDim函数从1定义它的原因。
错误来自以下这行代码：resArr(year + 13) = dataArr(year + 1)
英文:
I'm struggling to find out what wrong with my code and why it returns the above error.
The purpose of the code is to construct resArr that in the end must have 94 values, and when it ready I need to copy all the values to one row in worksheet.
The next code is only first step off the constructing and I already have the error.
wsPrm is the global variable and defined in another Sub, so it's correct.
I know that numbering of arrays starts from 0, that's the reason I defined it by ReDim function from 1.
The error comes from line : resArr(year + 13) = dataArr(year + 1)
Public Sub Simulate()
Dim term, tranche As Integer
Dim dataArr() As Variant, resArr(1 To 94) As Variant
noiterations = wsPrm.Range("iterations").Value
tranche = wsPrm.Range("tranche").Value
term = wsPrm.Range("Period").Value
Tresv = wsPrm.Range("Tresv").Value
For counter = 1 To noiterations
ReDim dataArr(1 To term + 1)
dataArr = wsSim.Range(wsSim.Cells(6, 43), wsSim.Cells(6 + term, 43)).Value
For year = 0 To term
resArr(year + 13) = dataArr(year + 1)
resArr(year + 13 + 41) = dataArr(year + 1)
Next year
Next counter
End Sub
答案1
得分: 3
"I know that numbering of arrays starts from 0" - 在大多数编程语言中是的。但是，VBA是基于COM构建的，它使用一种混合的数组索引方式。Excel通常使用基于1的索引（但也有例外情况，它使用基于0的索引），在您的代码中，您可以使用基于42的索引来定义数组（尽管这没有意义）。
ReDim dataArr(1 To term + 1) 是多余的，您不需要为Excel返回的数组分配内存，Excel会自行处理。
表达式 wsSim.Range(wsSim.Cells(6, 43), wsSim.Cells(6 + term, 43)).Value（假设wsSim也是对Worksheet的全局变量）返回一个二维数组，两个维度都是基于1的：
v = wsSim.Range(wsSim.Cells(6, 43), wsSim.Cells(6 + term, 43)).Value
Debug.Print LBound( v, 1 ) ' 必须是1
Debug.Print UBound( v, 1 ) ' 必须是1 + term
Debug.Print LBound( v, 2 ) ' 必须是1
Debug.Print UBound( v, 2 ) ' 必须是1
我不明白94是从哪里来的，但如果term是从某处读取的，那么它必定是一个经过计算的值，对吗？
英文:
"I know that numbering of arrays starts from 0" - In most programming languages, yes. VBA, however, builds on COM, which uses a mishmash of array indexing. Excel usually uses 1-based indices (but there are exceptions, when it uses 0-base indexing), and in you code you can define arrays with 42-based indices (not that it made sense).
ReDim dataArr(1 To term + 1) is superfluous, you do not need to allocate memory, Excel does it for the arrays that it returns.
The expression wsSim.Range(wsSim.Cells(6, 43), wsSim.Cells(6 + term, 43)).Value (assuming that wsSim is also a global variable to a Worksheet) returns a two dimensional array, and both dimensions are 1-based:
v = wsSim.Range(wsSim.Cells(6, 43), wsSim.Cells(6 + term, 43)).Value
Debug.Print LBound( v, 1 ) ' Must be 1
Debug.Print UBound( v, 1 ) ' Must be 1 + term
Debug.Print LBound( v, 2 ) ' Must be 1
Debug.Print UBound( v, 2 ) ' Must be 1
I don't see where 94 comes from but if term is read from somewhere, it must be a calculated value, right?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论