Option Base 0 and creating an Array from a Range

Question

I am creating an arrays from a range of cells in Excel. All works, array is created. The problem is i use "Option Base 0" and all my other arrays are (0 to x), but arrays created by reading a range are (1 to x+1). Is there any way to read an Array from a range with first element with number 0?

Sample code:

Sub ArraysFromRange()
Dim myArray(9) As Integer, myArray2() As Variant
Dim i As Integer
Dim rngTarget As Range, rngTarget2 As Range

Range("A1:M20").Clear
For i = 0 To UBound(myArray)
    myArray(i) = i
Next i
With ThisWorkbook.Worksheets("test")
    Set rngTarget = .Range(.Cells(1, 1), .Cells(UBound(myArray) + 1, 1))
    Set rngTarget2 = .Range(.Cells(1, 2), .Cells(UBound(myArray) + 1, 2))
End With
rngTarget = Application.Transpose(myArray)
myArray2 = Application.Transpose(rngTarget)

End Sub

After this i have two arrays, but first one is (0 to 9) and second one (1 to 10). If i have a lot of arrays in code i must always remember which one shall have index - 1.

Question two: is there any possibility to read an array from a range as a "Boolean" or "Integer" instead of "Variant"?

Is there a way? Yes, you'd have to rewrite the entire array to a new array. Highly impractical. And to your 2nd question, all arrays created from a range will start as type variant. Once data is placed into the array, each individual element will have a specific data type - which would be of whatever was in the range. You can "remember" which type of array starts with a 1 by using a naming convention, an example would be `rngArr()`. You know that array would have a lower bound of 1 simply because it has `rng` in it. — K.Dᴀᴠɪs, Feb 01 '20 at 10:35
Thanks for fast reaction! I am working on many small arrays, so i think instead of reading range and then rewriting the entire array i will just read it from excel element by element. Thanks to that i will have first element with index 0, data type will be integer. Yes, i used naming convention to recognize if it is base 0 or base 1 array, but i have a lot of arrays and already have names like MatrixOfIdexesTechnologyTypes. It starts to be too long. And i need clear names, because i modify the code rarely and don't want to waste time to investigate what was MoITechTypes. — Nepcior, Feb 01 '20 at 11:05
There is no perfect solution. I used Option Base 1 before but it doesn't change the base everywhere. E.g. the first ListBox element had index 0 anyway. — Nepcior, Feb 01 '20 at 23:15
Possible duplicate of [May I return a 0-base array from ws.usedrange.value?](https://stackoverflow.com/q/53938280/11683) — GSerg, Feb 02 '20 at 09:20

score 0 · Accepted Answer · answered Feb 02 '20 at 09:29

Is there any way to read an Array from a range with first element with number 0?

No

is there any possibility to read an array from a range as a "Boolean" or "Integer" instead of "Variant"?

No

Where SomeRange references a contiguous range of >1 cells, SomeRange.Value returns a 1 based 2D array of Variants. Thats what it does, you can't change that.

As others have noted, and as the answers to the suggested duplicate Q propose, you can always copy values from that array into another array of different base and/or type, but that's a different story.

Option Base 0 and creating an Array from a Range

1 Answers1