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
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
VBA Code:
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