CODEWITHSUNDEEP
arraysvba
SmartSolid
SmartSolid

Reputation: 15

LBound of arrays stored inside variant

In this question I was given an answer which solved my problem but as I have been going through my code I have noticed something odd. When I assign the 3 arrays to the arrays variant, I can see in the locals window that the LBound of the variant starts at 0 (as expected) however the individual arrays LBound starts at 1 even though I have written Option Base 0 at the top of the module. Is this correct?

Code to populate array

Public Sub FillArray(ByRef data As Variant, 
                    sRange As String) 
data = oCurrentWs.Range(sRange) End Sub

Upvotes: 0

Views: 404

Answers (2)

user3598756
user3598756

Reputation: 29421

it's because the assignment of a Range to a Variant would always result in a 1-lowerbounded array, regardless of any Option Base setting.

as you can see with this test:

    Public Sub FillArray(ByRef data As Variant, sRange As String)
        With oCurrentWs.Range(sRange)
            ReDim data(0 To .Rows.Count - 1, 0 To .Columns.Count - 1) '<--| this dims 'data' as a 0-based array
            data = .Value '<--| this redims 'data' as a 1-based array
        End With
    End Sub

so if you really need to have 0-based arrays you have to code like follows:

Public Sub FillArray(ByRef data As Variant, sRange As String)
    Dim i As Long, j As Long
    With oCurrentWs.Range(sRange)
        ReDim data(0 To .Rows.Count - 1, 0 To .Columns.Count - 1) '<--| dim 'data' as a zero-based array
        For i = 1 To .Rows.Count '<--| loop through referenced range rows
            For j = 1 To .Columns.Count '<--| loop through referenced range columns
                data(i - 1, j - 1) = .Cells(i, j) '<--| fill each array element individually
            Next
        Next
    End With
End Sub

Upvotes: 0

Anil
Anil

Reputation: 3752

Variant arrays are dynamic arrays of the Variant type these are initialized using "= Array()".

Advantage

as they are initialized using "= Array()", LBound and UBound functions work with them even when they have no elements:

Dim varr As Variant

  varr = Array()

  Debug.Print LBound(varr) 'Prints 0
  Debug.Print UBound(varr) 'Prints -1

Now we can also add an element without checking for UBound failure

Excel Ranges Use Base 1

When an array is populated straight from a range the array will start at 1 and not 0.

For more information refer to the Cells & Ranges > Working With Arrays

Upvotes: 1

Related Questions