在VBA-数组下标谁能解释一下吗？ [英] Array subscripts in VBA- Can anyone explain this?

问题描述

请附上我写来说明我有一个困惑的小测试程序的屏幕截图。上述＆lt文本;>是由我输入解释，我得到的错误。

Please find attached a screenshot of a small test program that I wrote to illustrate a confusion I am having. The text in <> is inputted by me to explain the error I am getting.

该MSGBOX是为了使程序留在休息模式，这样我可以看到什么值均

The MsgBox was created to make the program stay in a break mode so that I can see what the values were.

下面是code。 （我已经抛弃了早期版本，但数值仍应该相同）

Here is the code. (I had discarded the earlier version but the values should still be same)

Sub test()
Dim Test1()
Dim Test2()

'values hardcoded in the sheet from where this macro is launched
'Cells(7,4) = 1
'Cells(7,5) = 2
'Cells(7,6) = 3
'Cells(7,7) = 4
'Cells(7,8) = 5
'Cells(7,9) = 6
'Cells(7,10) = 7


Set Rng1 = Range(Cells(7, 4), Cells(7, 10))
Test1 = Rng1.Value2
ReDim Test2(4)
Test2 = Rng1.Value2
MsgBox ("This was a test program")

End Sub

我的问题

1. 这是一个单排阵 - 所以为什么Excel中把它作为一个二维数组
？

2. 一般情况下，第一行和列从0即零开始。那么，为什么我要使用1访问由数组挑值？或者为什么行0和列0给出错误？


3. 即使 REDIM 语句势力它有4个值，为什么它拿起第五个值？这是否意味着我们不必担心Redimensioning数组如果有更多的价值很可能会遇到和Excel会一直做要紧？

1. It is a single row array - so why does Excel take it as a two dimensional array?
2. Normally the first row and column start from 0 i.e. zero. So why do I have to use 1 for accessing the values picked by the array? Or why does row 0 and column 0 give an error?
3. Even if Redim statement forces it to have 4 values, why is it picking up the fifth value? Does this mean we need not worry about Redimensioning the array if more values are likely to be encountered and Excel will always do the needful?

感谢很多提前。

推荐答案

看起来你只是覆盖与您设置为 RNG1 的范围阵列。的code后

It looks like you are simply overwriting your array with the range you set as Rng1. After the code of

Test2 = Rng1.Value2

这意味着你的阵列是不是阵列您使用ReDim 版，但值由Excel的值2返回的数组属性，这是基于1的二维数组，每个单元格 RNG1 值。

This means that your array is not the array you ReDimed, but the array of values returned by Excel's Value2 property, which is a 1 based 2D array, one value for each cell in Rng1.

所以，是的，数组，除非指定，0为主，但是一旦一个范围值返回基于由返回值2 属性数组是1。 （如果你调用值2 上一个单细胞，而不是细胞的范围内，你只会得到一个变种。）

So, yes, arrays, unless specified, are 0 based, but the array returned by the Value2 property is 1 based when returned for a range of values. (If you call Value2 on a single cell instead of an range of cells, you just get a Variant.)

这篇关于在VBA-数组下标谁能解释一下吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！