arraysexcelvbaexcel-tableslistobject

I can't access individual members from an array populated from table object


I am populating an array from a row of a table. I am able to print the entire array, but I am not able to print a member or iterate the array independently as shown in the following images:

Image 1) the array is printed

enter image description here

Image 2) the subscript is out of range

enter image description here

As also seen on the image here is the code:

Sub try_w()
    Dim H_PR() As Variant
        
    H_PR_dim = Worksheets("COV_CHOL").ListObjects("Historical_PR").ListRows(1).Range.Count
    ReDim H_PR(H_PR_dim)
        
    H_PR = Worksheets("COV_CHOL").ListObjects("Historical_PR").ListRows(1).Range.Value
    '1.a.2)
    'Try
    Worksheets("H_PRi").Range("B2:E2") = H_PR
    'Try end/PASS
    'Try
    Worksheets("H_PRi").Range("B3") = H_PR(1)
    'Try end/FAIL
End Sub

Solution

  • Writing from Range to Array

    Option Explicit
    
    Sub try_w()
        
        Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
        ' Source
    
        Dim sws As Worksheet: Set sws = wb.Worksheets("COV_CHOL")
        Dim stbl As ListObject: Set stbl = sws.ListObjects("Historical_PR")
        
        ' Range
        
        ' This is a one-row range.
        Dim srrg As Range: Set srrg = stbl.ListRows(1).Range
        ' Number of columns
        'Dim cCount As Long: cCount = srrg.Columns.Count
        ' Number of rows
        'Dim rCount As Long: rCount = srrg.Rows.Count
        
        ' Array
        
        ' You can write the data from a range using the following line.
        ' You don't need the number of rows or columns.
        Dim srData As Variant: srData = srrg.Value
        ' Note that this works only if there are at least two cells in the range!
        ' See the function below on how to do it properly.
        
        ' Number of columns
        Dim cCount As Long: cCount = UBound(srData, 2)
        ' Number of rows
        'Dim rCount As Long: rCount = UBound(srData, 1)
    
        ' Destination
        
        Dim dws As Worksheet: Set dws = wb.Worksheets("H_PRi")
        '1.a.2)
        'Try
        dws.Range("B2").Resize(, cCount).Value = srData
        ' or 
        'dws.Range("B2").Resize(1, cCount).Value = srData
        ' or without cCount:
        'dws.Range("B2").Resize(, UBound(Data, 2)).Value = srData
        ' Note that the default of both 'Resize' arguments is '1' i.e.
        ' Range("B2").Resize(1, 1) is Range("B2"), while
        ' Range("B2").Offset(0, 0) is Range("B2").
        
        'Try end/PASS
        'Try
        dws.Range("B3").Value = srData(1, 1)
        dws.Range("B4").Value = srData(1, 2)
        dws.Range("B5").Value = srData(1, 3)
        dws.Range("B6").Value = srData(1, 4)
        'Try end/FAIL
        
        ' Note that this is just practice. The array is redundant here
        ' because you can simply do...
    '    dws.Range("B2").Resize(, cCount).Value = srrg.Value
    '    dws.Range("B3").Value = srrg.Cells(1)
    '    dws.Range("B4").Value = srrg.Cells(2)
    '    dws.Range("B5").Value = srrg.Cells(3)
    '    dws.Range("B6").Value = srrg.Cells(4)
        ' which is called 'copying by assignment'.
    
    End Sub
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Purpose:      Returns the values of a range in a 2D one-based array.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Function GetRange( _
        ByVal rg As Range) _
    As Variant
        If rg Is Nothing Then Exit Function
        
        Dim rData As Variant
        If rg.Rows.Count + rg.Columns.Count = 2 Then
            ReDim rData(1 To 1, 1 To 1): rData(1, 1) = rg.Value
        Else
            rData = rg.Value
        End If
    
        GetRange = rData
    End Function
    
        ' In our previous example, we can utilize the function like this:
        'Dim srData As Variant: srData = GetRange(srrg)