将范围值传递给数组时，为什么数组索引从1开始 [英] Why array Index starts at 1 when passing range values to array

问题描述

在此VBA程序中，我要做的就是从电子表格传递一个数组并将其添加到每个数组单元格中.我的问题是数组的索引.当我开始循环数组时，它没有当我从零开始索引时可以工作(错误下标超出范围)，但是当我从1开始数组时它可以完美地工作.为什么呢?(我认为只有在顶部 Option Base 1 中指定该情况)

In this VBA program all I am trying to do is to pass an array from spreadsheet and add 1 to each of the array's cells. My problem is with the index of the array. when I start looping the array it doesnt work when I start the index from zero ( I get error subscript out of range) but it works perfectly when I start the array from 1. Why is that? (I thought that would be the case only I specify at the top Option Base 1)

Sub Passarray()
    Dim Array As Variant
    Dim i, j As Integer
    'Pass array and manipulate
    Vol = Range("Volatility")
    For i = 0 To 2
       For j = 0 To 2
          Vol(i, j) = 1+ Vol(i,j)
       Next j
    Next i
End Sub

推荐答案

根据我的经验，将 Range 传递给数组时不是这种情况.
我不知道背后的具体原因，但是此链接表示您无法更改这种行为.

That wasn't the case when you pass Range to arrays based on my experience.
I don't know the specific reason behind, but this link indicates that you cannot change this behavior.

^{QUOTE:将工作表数据加载到的数组的下限(LBound)始终等于1，无论模块中可能具有哪种Option Base指令.您无法更改此行为.}

您可以做的是像这样利用 LBound/UBound 的使用:

What you can do is to utilize the use of LBound/UBound like this:

Vol = Range("Volatility")
For i = LBound(Vol, 1) To UBound(Vol, 1)
    For j = Lbound(Vol, 2) To Ubound(Vol, 2)
        '~~> do stuff here
        Vol(i, j) = 1 + Vol(i, j)
    Next j
Next i

但是，如果您的 Range 只是一列多行，则将其像这样传递给Array:

If however your Range is just one column with several rows, you pass it to Array like this:

Vol = Application.Transpose(Range("Volatility"))
For i = LBound(Vol) To UBound(Vol)
    '~~> do stuff here
    Vol(i) = 1 + Vol(i)
Next

这样，您将生成一维数组而不是二维数组.
要迭代值，可以在上面使用，也可以使用 For Each :

This way, you will produce one-D array instead of two-D array.
To iterate values you can use above or you can also use For Each:

Dim x As Variant '~~> dimension another variant variable
For Each x In Vol
    '~~> do stuff here
    x = 1 + x
Next

这篇关于将范围值传递给数组时，为什么数组索引从1开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！