Results 1 to 11 of 11

Thread: retrive specific elements from 2d arrays

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    retrive specific elements from 2d arrays

    Code:
    Dim array As Array = value_range.Value2
                MsgBox(value_range.Value2(0, 1))
    kepping getting an Index was outside the bounds of the array at message box when i try the above code

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: retrive specific elements from 2d arrays

    Put a breakpoint on the line with the MsgBox. When execution stops at the breakpoint, take a look at array (and while you're at it, give it a different name. Nothing good can come of having a variable named array), or at value_range.Value2. What is the size of the array? What are the sizes of the dimensions? They aren't going to be what you are expecting, but beyond that nobody can say why because we don't know where that thing came from.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    Re: retrive specific elements from 2d arrays

    my code reads an excel file line by line so at array it has the values from cell a2 and b2 in excel file. the excel sheet has 20 entries but only reads 1 line at a time

  4. #4
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,289

    Re: retrive specific elements from 2d arrays

    Is value_range.Value2 really a 2D array? If it is then the IDE would complain on the 1st code line already because array is declared as 1D array (Dim array As Array = value_range.Value2).
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    Re: retrive specific elements from 2d arrays

    sorry its a 1d array with 2 values been read in. i need to strip out one of the values and get that to match something else so that i copy over what i want to excel. they way my code is working is it copies over everything from one excel to another

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    Re: retrive specific elements from 2d arrays

    this is the code for array
    Code:
     For c As Integer = 2 To 100
    
                Dim value_range As Excel.Range = objSht.Range("A" & c, "S" & c)
               
                '    Dim a As Object
    
                Dim array As Array = value_range.Value2
                MsgBox(value_range.Value2(0, 1))
    
    
                '    If value_range.Value2[0,1]= DateTimePicker1.Value Then
                AddRow(array)
                Else
    
                End If
              
    
            Next

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: retrive specific elements from 2d arrays

    If it's a 1D array, then you can't access it with (0,1). It kind of looks like you could, because there really shouldn't be much difference between that and accessing a 1D array, but there is. After all, you are accessing the second element of the first line of data. You only have one line of data, so it would make sense that you could do this, but if the compiler sees value_range.Value2 as being a 1D array, then it will balk at the way you have it written. In that case,

    value_range.Value2(1)

    would work.
    My usual boring signature: Nothing

  8. #8
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,289

    Re: retrive specific elements from 2d arrays

    Which column in Excel is the date value you're looking for? Besides, Value2 returns only floating point values, so you will need to convert that back to datetime type before trying to test it against your datetimepicker.value. And as the type name suggest, it's date AND time, so unless the 2 datetime objects are equal to the tick, your test will fail. If you're interested in only the date portion of the datetime object then you need to strip the time off, i.e datetimepicker.value.Date will give you the date with the time being 0:00:00.
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Oct 2011
    Posts
    255

    Re: retrive specific elements from 2d arrays

    how to i convert the float to a datetime
    Code:
     Dim array As Array = value_range.Value2
                MsgBox(value_range.Value2(1, 2))
    
    
                If value_range.Value2(1, 2) = DateTimePicker1.Value.Date Then
                    AddRow(array)
                Else
    the value_range.Value2(1, 2) pull in the date like this 41638 want to convert that to the date it means which is the 30/12/2013

  10. #10
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,289

    Re: retrive specific elements from 2d arrays

    Quote Originally Posted by rjhe22 View Post
    how to i convert the float to a datetime
    Code:
     Dim array As Array = value_range.Value2
                MsgBox(value_range.Value2(1, 2))
    
    
                If value_range.Value2(1, 2) = DateTimePicker1.Value.Date Then
                    AddRow(array)
                Else
    the value_range.Value2(1, 2) pull in the date like this 41638 want to convert that to the date it means which is the 30/12/2013
    Use Date.FromOADate method.
    Code:
    Dim d As Date = Date.FromOADate(41638)
    MessageBox.Show(d.ToString)
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  11. #11
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: retrive specific elements from 2d arrays

    Range.Value2 will return a two dimensional array if the Range is contains more than one cell. The array will be in a [Row][Col] format that matches the specified Range and the indices begin with one (1) not zero (0). This is analogous to the Range.Cells(row,col) usage in VBA.

    The array will be an array of objects. As stated above, Range.Value2 will return a double type if the cell contains a numeric value, Nothing if the cell is empty, and a String for of course string values.

    If the Cell if a formatted numeric (Date, Time, Currency, Accounting, etc.) and you use Range.Value, the following conversions are performed for you. Note that the array values are boxed in System.Object values.

    • Numeric ==> System.Double
    • Currency ==> System.Decimal
    • Accounting ==> System.Decimal
    • Date ==> System.DateTime
    • Time ==> System.Double
    • Percentage ==> System.Double
    • Fraction ==> System.Double
    • Scientific ==> System.Double
    • Text ==> String if string value else System.Double if it is a number formatted as Text (Not a quoted number)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width