How use getupperbound and how assign elements to array?

Question

Could you tell me how to do what I'm trying to do?  Here is my code.  I have an error in the bold lines for getupperbound and below that where I try to assign elements to an array.

Sub GenDataArray()
    ' Generate an array of the values in the Data tab.
    
    Dim sht1 As Worksheet
    Dim LastCell As String
    Dim LastRow As Integer
    Dim DataAry As Variant
    
    Set sht1 = Sheets("Data")
    LastCell = sht1.UsedRange.SpecialCells(xlLastCell).Address
    LastRow = sht1.UsedRange.SpecialCells(xlLastCell).Row
    
    ' Copy data from data tab.
    ' cell(2,1) includes the column headings in the Data tab.
    DataAry = sht1.Range(Cells(2, 1), LastCell).Value
    
    Dim sht2 As Worksheet
    Dim AryRowN As Integer
    Dim indx As Integer
    Dim Tbl2Ary As Variant
    
    ' These are the 27 columns from DataArray that are used in "Table 2" in this order.
    Dim Tbl2ColsAry As Variant
    Tbl2ColsAry = Array(1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45)

    Set sht2 = Sheets("Sheet1")
    
    For rowindx = 0 To DataAry.getupperbound
        
        For colindx = 0 To Tbl2ColsAry.getupperbound
        
            AryColN = Tbl2ColsAry(colindx)
        
            Tbl2Ary(rowindx, colindx) = DataAry(rowindx, AryColN)
        
        Next colindx
    Next rowindx
    sht2.Cells(1, 1) = Tbl2Ary
    
End Sub

Answer 1

The upper bound of an array is returned by the function UBound.

For a one-dimensional array ary, the upper bound is UBound(ary).

For a two-dimensional array ary (such as the values of a range of cells), UBound(ary, 1) returns the upper bound of the rows dimension, and UBound(ary, 2) the upper bound of the columns dimension.

Notes:

1) Even if a range consists of only one row or one column, its array of values is still two-dimensional.

2) The lower bound of the array of values of a range is 1 in both dimensions, not 0.

So

    For rowindx = 0 To DataAry.getupperbound

should be

    For rowindx = 1 To UBound(DataAry, 1)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Thank you for your explanation and correction.  Your code works.

Now I'm getting Run-time error '13': Type mismatch on this line.

Tbl2Ary(rowindx, colindx) = DataAry(rowindx, arycoln)

I get the same error this way.

Tbl2Ary(1, 0) = DataAry(1, 1)

Both arrays are Dim ... As Variant.

Answer 3

But DataAry is a variant, not a range:

 DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value   'Variant array    

Note that Cells(1,2) is on the activesheet, not necessarily sht1 - thus the added bold sht1

To use a range, the code would need to be

 Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)   'Variant set to Range object

It is also important to note that when using variants, the lower bound could be 0 (the default) or 1 depending on the Option Base setting of the project 

For rowindx = LBound(DataAry) To UBound(DataAry)

For colindx = LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry)

If the OP was actually mixing ranges (which can have 0,0 cells - even negatively indexed cells, possibly) and variants, the OP would be better off making sure that this was at the top of the module

Option Base 1

Answer 4

I think you're saying that if I don't add sht1.cells that the code could pull data from the active sheet instead of sht1 so I have added this to my code.  I also added the option base 1 and changed my lower bound to 1 in the for loop.

I don't understand yet whether I should change this

DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value

to this

Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)

Do both options take values from a range on a spreadsheet and put them in an array?  What is the difference?

Thanks for your help.

Answer 5

You need to dimension that array properly:

Sub GenDataArrayFixed()
    ' Generate an array of the values in the Data tab.
    
    Dim sht1 As Worksheet
    Dim LastCell As String
    Dim LastRow As Integer
    Dim DataAry As Variant
    Dim rowindx As Long
    Dim colindx As Long
    Dim AryColN As Long
    
    Set sht1 = Sheets("Data")
    LastCell = sht1.UsedRange.SpecialCells(xlLastCell).Address
    LastRow = sht1.UsedRange.SpecialCells(xlLastCell).Row
    
    ' Copy data from data tab.
    ' cell(2,1) includes the column headings in the Data tab.
    DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value
    
    Dim sht2 As Worksheet
    Dim AryRowN As Integer
    Dim indx As Integer
    Dim Tbl2Ary As Variant
    
    ' These are the 27 columns from DataArray that are used in "Table 2" in this order.
    Dim Tbl2ColsAry As Variant
    Tbl2ColsAry = Array(1, 2, 3, 4, 5, 12, 14, 15, 16, 19, 20, 21, 24, 25, 18, 10, 17, 26, 39, 40, 33, 34, 31, 32, 6, 35, 45)

    Set sht2 = Sheets("Sheet1")
    ReDim Tbl2Ary(LBound(DataAry) To UBound(DataAry), LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry))
    
    For rowindx = LBound(DataAry) To UBound(DataAry)
        
        For colindx = LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry)
        
            AryColN = Tbl2ColsAry(colindx)
        
            Tbl2Ary(rowindx, colindx) = DataAry(rowindx, AryColN)
        
        Next colindx
    Next rowindx
    sht2.Cells(1, 1) = Tbl2Ary
    
End Sub

Answer 6

Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)

sets the variable DataAry to the range from A2 to LastCell.

DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)

sets the variable DataAry to the array of values of that range. The latter line is equivalent to

DataAry = sht1.Range(sht1.Cells(2, 1), LastCell).Value

since Value is the default property of a range.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 7

The real difference is that when you use the first line, the data is read into memory all at once: with the second, the code ends up reading the range values cell by cell, which may be slower. How you step through is different too - you would use 1 to DataAry.Rows.Count and  1 to DataAry.Columns.Count when you use the range instead of LBound and UBound

Answer 8

Many thanks for your help.

Answer 9

Using the debug data watch, I get the values of the array using the second line.  The first line gives me information about the range, but not the values.  I'm not sure how to use the first line.

Answer 10

Set DataAry = sht1.Range(sht1.Cells(2, 1), LastCell)

For rowindx = 1 To DataAry.Rows.Count

For colindx = LBound(Tbl2ColsAry) To UBound(Tbl2ColsAry)

   AryColN = Tbl2ColsAry(colindx)
        
            Tbl2Ary(rowindx -1, colindx) = DataAry(rowindx, AryColN)

Next

Next