none
How use getupperbound and how assign elements to array? RRS feed

  • 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


    Monday, November 9, 2015 7:36 PM

Answers

  • 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)

    • Marked as answer by VBApractice Tuesday, November 10, 2015 4:15 PM
    Monday, November 9, 2015 9:52 PM
  • 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


    • Marked as answer by VBApractice Tuesday, November 10, 2015 7:01 PM
    Tuesday, November 10, 2015 4:30 PM
  • 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

    • Marked as answer by VBApractice Tuesday, November 10, 2015 6:54 PM
    Tuesday, November 10, 2015 4:54 PM
  • 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)

    • Marked as answer by VBApractice Tuesday, November 10, 2015 6:58 PM
    Tuesday, November 10, 2015 5:10 PM

All replies

  • 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)

    • Marked as answer by VBApractice Tuesday, November 10, 2015 4:15 PM
    Monday, November 9, 2015 9:52 PM
  • 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.

    Tuesday, November 10, 2015 4:25 PM
  • 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


    • Marked as answer by VBApractice Tuesday, November 10, 2015 7:01 PM
    Tuesday, November 10, 2015 4:30 PM
  • 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.

    Tuesday, November 10, 2015 4:47 PM
  • 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

    • Marked as answer by VBApractice Tuesday, November 10, 2015 6:54 PM
    Tuesday, November 10, 2015 4:54 PM
  • 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)

    • Marked as answer by VBApractice Tuesday, November 10, 2015 6:58 PM
    Tuesday, November 10, 2015 5:10 PM
  • 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
    Tuesday, November 10, 2015 6:08 PM
  • Many thanks for your help.
    Tuesday, November 10, 2015 6:59 PM
  • 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.
    Tuesday, November 10, 2015 7:04 PM
  • 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


    Tuesday, November 10, 2015 7:30 PM