Array from sheet range requiring different notation than loading from code and row count starting at 1 rather than 0

[bgrushin]

I have generally in the past created arrays from a list entered in the vba code but am working on a project that I want the user to be able to update themselves, so am pulling the array data from a range in the sheet.

The issue I ran into is doing this requires my to call each array record using both row,column notation, ie. arr(1,1), and also the row count is starting at 1 rather than 0, ie. arr(r) where r = 0 returns subscript out of range error, even arr(r) where r=1 gives me the same error and I have to do array(r,1) to get it to work.

When loading the information via string arr = array("data1","data2","data3") I can just do arr(r) and it returns a value just fine. I figured this is because it sees that there is only one column in the array but even doing a ReDim arr (0 to 2) as variant doesn't work to allow me to use arr(r).

My code is below, if someone can explain to me the reasoning so I understand and use this properly going forward, and also if something in an existing process gets thrown off down the line why that might be the case. TIA

Private Sub Workbook_Open()
Dim comp As Variant
Dim r As Integer
Dim sample As String

ReDim comp(0 To lrow("V")) As Variant

comp = Range("V2:V" & lrow("V")).Value

For r = 0 To UBound(comp)
    sample = comp(r)
Next r

End Sub

Function lrow(clm, Optional sht As String)

If sht = "" Then
    sht = ActiveSheet.Name
End If

lrow = Sheets(sht).Range(clm & "65000").End(xlUp).Row

End Function

 

[mikerickson]

When you set a variant variable to the value of a range object, you get a two dimensioned array.

If you absolutly need a one dimensional array, you can use Application.Transpose to convert it from two dimensions to one (if the range isn't too big.)

Dim myRayArray as Variant
Dim myCookedArray as Variant

myRawArray = Range("A1:CZ1").Value
myCookedArray = Application.Transpose(MyRawArray)

Dim myRayArray as Variant
Dim myCookedArray as Variant

myRawArray = Range("A1:A100").Value
myCookedArray = Application.Transpose(Application.Transpose(MyRawArray))

In both the above, myCookedArray will be a one dimensional array.

Side Note: The Redim Preserve function only works on the highest dimension of a multi-dimensional array

Dim myArray(1 to 10, 1 to 10)

Redim Preserve myArray(1 to 10, 8 to 23): Rem good
Redim Preserve myArray(1 to 6, 1 to 10): Rem bad

 

[bgrushin]

When you set a variant variable to the value of a range object, you get a two dimensioned array.

If you absolutly need a one dimensional array, you can use Application.Transpose to convert it from two dimensions to one (if the range isn't too big.)

Dim myRayArray as Variant
Dim myCookedArray as Variant

myRawArray = Range("A1:CZ1").Value
myCookedArray = Application.Transpose(MyRawArray)

Dim myRayArray as Variant
Dim myCookedArray as Variant

myRawArray = Range("A1:A100").Value
myCookedArray = Application.Transpose(Application.Transpose(MyRawArray))

In both the above, myCookedArray will be a one dimensional array.

Side Note: The Redim Preserve function only works on the highest dimension of a multi-dimensional array

Dim myArray(1 to 10, 1 to 10)

Redim Preserve myArray(1 to 10, 8 to 23): Rem good
Redim Preserve myArray(1 to 6, 1 to 10): Rem bad

Hi Mike, thanks for your response.

When you say the redim preserve only works on the highest dimension, you mean the last "column"?

I still don't understand the 2 things that caused my initial confusion. 1. Why when loading the array data from a range in excel must I now specify column if there is only 1? 2. Why do I need to reference the row number starting at 1 rather than at 0 (ie i=1, arracy(i) ) which is the case when the array values are populated in the code?

Regards,
Boris

 

[mikerickson]

myArray = Range("A1:B3")

at that point myArray will have 3 rows and 2 columns, it will be the same size array as if you had used Dim myArray(1 to 3, 1 to 2)

The confusing thing is that a single column range will also have two dimensions.

myArray = Range("A1:A4")

will result in the same dimensions as if you had done Dim myArray(1 to 4, 1 to 1). And even though you have only one possible index for the second index, you still have to specify it "myArray(2,1)" rather than "myArray(2)"

Why do I need to reference the row number starting at 1 rather than at 0

Because that's the way that Excel works. I'm sure that there are reasons that the programmers set up the default for arrays to be 0 based, but that was so long ago and so deep inside the software that's one of those questions where "why" isn't as useful as "how do I work with it". Which is by using Ubound, LBound and explicitly setting the lower bounds of arrays that you declare. You might want to look at the use of Option Base.

 

[bgrushin]

@mikerickson Having issues with arrays again. This is being done in Excel 2016, I have in A2:A7 as the picture shows, I set my array as below (the lrow function is my own for ease of finding last row, doesn't have an impact on my issue):

Sub aztrans()
Dim azbk As Workbook
Dim accts As Variant
Dim lr As Integer

accts = Array(Sheets("Entry").Range("A2:A" & lrow("A")).Value)

Call azpivot

End Sub

Function lrow(clm, Optional sht As String)

If sht = "" Then
    sht = ActiveSheet.Name
End If

If Right(ActiveWorkbook.Name, 3) = "xls" Then
    lrow = Sheets(sht).Range(clm & "65000").End(xlUp).Row
Else
    lrow = Sheets(sht).Range(clm & "1000000").End(xlUp).Row
End If

End Function

But as you'll see in the locals window the array is for some reason named accts(0), and to pull the data from the array I have to do accts(0)(x,y). This is a new behavior of the (0) being added that I haven't seen before, any insight on this?

Thanks,
Boris

 

[mikerickson]

As written accts is an array that has one element.
That element is the array of values from your range.

Try

accts = Sheets("Entry").Range("A2:A" & lrow("A")).Value

 

[bgrushin]

As written accts is an array that has one element.
That element is the array of values from your range.

Try

accts = Sheets("Entry").Range("A2:A" & lrow("A")).Value

That worked, thank you very much. I've read about arrays on a few different blogs but still seem to be having trouble grasping them, do you have recommendation of where to look to get a better grasp, be it a site, youtube video or book?

Thanks,
Boris