Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a VB6 to .NET conversion and I am getting an index was outside the bounds of the array error at the line marked below.

    In VB6 the code was
    gXLWrkBk.SetValByArray("A" & DATA_ROW_START, lngRows + 1, lngCols + 1, gvarOutRecs)


    And to .NET I tried to pas in the array like this:
    gXLWrkBk.SetValByArray("A" & DATA_ROW_START, lngRows + 1, lngCols + 1, gvarOutRecs(lngRowCnt, lngColCnt))

    I do not understand how the gvarOutRecs gets passed or why it has an error?!? Can anyone help me? The ex.message isnt really making me understand it at all.


    Private Sub WriteXLSheet()

    Dim lngRowCnt As Integer
    Dim lngColCnt As Integer
    Dim lngRows As Integer
    Dim lngCols As Integer
    Dim NewArray(,) As Object

    Const DATA_ROW_START As Integer = 6 'Excel template row where header ends & data starts

    Try
    AddBreakRows(NewArray) 'Add break rows between groups - store in new array

    LogWithDate(vbTab & "Rotating records array for writing...")

    'Measure the Out Records array dimensions
    lngRows = UBound(NewArray, 2)
    lngCols = UBound(NewArray, 1)

    ReDim gvarOutRecs(lngRows, lngCols) 'Create the rotated array

    'Copy the data into the rotated array
    For lngRowCnt = 0 To lngRows
    For lngColCnt = 0 To lngCols

    Try
    gvarOutRecs(lngRowCnt, lngColCnt) = NewArray(lngColCnt, lngRowCnt)

    Catch ex As Exception
    LogErr(ex.Message)
    End Try

    Next
    Next

    'Write the rotated array to the excel workbook
    LogWithDate(vbTab & "Writing rotated array...")

    ****THIS IS WHERE THE ERROR OCCURS>
    gXLWrkBk.SetValByArray("A" & DATA_ROW_START, lngRows + 1, lngCols + 1, gvarOutRecs)

    Catch ex As Exception
    LogErr(ex.Message)
    End Try

    End Sub

  2. #2
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I fond it- does this make sense?

    During the ReDim gvarOutRecs(lngRows , lngCols) I have 135,16 as a resulting array.

    When I pass it as gXLWrkBk.SetValByArray("A" & DATA_ROW_START, lngRows + 1, lngCols + 1, (gvarOutRecs(lngRowCnt, lngColCnt)))
    because of zero I actually have 136,17.

    So can I add one to the redimming to get the right size?

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You look to have found the problem, but a larger redim doesn't fix the problem. Your array is the correct size for the data it contains. You now need to adjust your code to reflect the correct size of the array - maybe a Ubound call to check if you are about to attempt an illegal call?

    cheers, Paul

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    According to one page I found in a Google search:
    That was wrong. Never mind.

Posting Permissions

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