Why array Index starts at 1 when passing range values to array

Question

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

Not related to your question, but do you realise that i is not dimmed as an integer, but as a variant? In VBA you cannot chain declarations. You need to do Dim i as Integer, j as Integer. — teylyn
– teylyn, Commented Jun 3, 2014 at 4:57

Community · Accepted Answer · 2018-05-08 21:49:10Z

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: The array into which the worksheet data is loaded always has an lower bound (LBound) equal to 1, regardless of what Option Base directive you may have in your module. You cannot change this behavior.}

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

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

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

Collectives™ on Stack Overflow

Why array Index starts at 1 when passing range values to array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related