VBA中的数组下标可以解释一下吗？ [英] Array subscripts in VBA- Can anyone explain this?

问题描述

请找到附有我写的一个小测试程序的屏幕截图，以说明我遇到的困惑。输入<>中的文本是为了解释我正在收到的错误。

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.

这里是代码。 （我已经抛弃了早期的版本，但值应该仍然相同）

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个值，为什么拿起第五个价值？这是否意味着我们不必担心如果有更多的值可能遇到并重新升级数组，那么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 。之后的代码

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

Test2 = Rng1.Value2

这意味着你的数组不是 ReDim ed的数组，而是由Excel的 Value2 属性，它是基于1的2D数组， 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，但由 Value2 属性返回的数组为1时返回值范围值。 （如果您在单个单元格而不是单元格范围内调用 Value2 ，则只需获取一个Variant。）

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屋！