dcsimg
Results 1 to 15 of 15

Thread: Strange Run-time error '6' Overflow

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    5

    Unhappy Strange Run-time error '6' Overflow

    Hi geniuses,
    I'm using MS Excel from Office 365 and wrote down some code to populate some numbers with my limited knowledge. The problem is that unless I click in another cell and then back in the cell from which the macro needs to run, I get an overflow error. How can I prevent this from happening?

    Steps to reproduce (using files in the attachment):
    1. Open up "Macro para separar en filas.xlam" to add the HRIS tab in the Ribbon
    2. Open up "7-29 - Malfunctioning macro.xlsx"
    3. Click on cell "D6"
    4. Click on the HRIS tab
    5. Click on Number Header Key
    6. Click on Number Line Key

    This error will show:
    Run-time error '6'
    Overflow

    The row that reads "MaxNum = MaxNum + 1" is highlighted in yellow, and the MaxNum variable holds value 32767, which is too high, because the greatest possible number in this example is 9.

    Please note that if before clicking on the "Number Line Key" button, you select a different cell and then select cell "D6" again, the macro works properly. Why would that happen?!

    Thanks in advance,

    Facundo
    Attached Files Attached Files

  2. #2
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,427

    Re: Strange Run-time error '6' Overflow

    Cannot download that at work, however, if you can post the code you are using, we might be able to diagnose the issue.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    5

    Re: Strange Run-time error '6' Overflow

    Quote Originally Posted by jdc2000 View Post
    Cannot download that at work, however, if you can post the code you are using, we might be able to diagnose the issue.
    Here you go!

    Code:
    Sub NumberLineKey(control As IRibbonControl)
    'Number subset of rows starting with 1
    Dim MaxNum As Integer
    Dim SOActual As String
    Dim SOSiguiente As String
    Dim ChooseColumn As Integer
    
    ChooseColumn = InputBox("Please indicate the offset of the line key column", "Enter column offset", -1)
    
    ActiveCell.Select
    Application.ScreenUpdating = False
    
    SiguienteSO:
    MaxNum = 1
    
    IniciarLoop:
    ActiveCell.Select
    SOActual = ActiveCell.Value2
    ActiveCell.Offset(1, 0).Select
    SOSiguiente = ActiveCell.Value2
        If SOActual.Value2 = SOSiguiente.Value2 Then
            MaxNum = MaxNum + 1
            GoTo IniciarLoop
        Else
            ActiveCell.Offset(-1, ChooseColumn).Select
            ActiveCell = MaxNum
                Do Until MaxNum = 1
                ActiveCell.Offset(-1, 0).Select
                ActiveCell = MaxNum - 1
                MaxNum = MaxNum - 1
                Loop
        End If
    Selection.End(xlDown).Select
        If ActiveCell.Row = 1048576 Then
        Selection.End(xlUp).Select
        End If
    
    ActiveCell.Offset(1, -ChooseColumn).Select
    If ActiveCell = "" Then
        GoTo Finalizar
    End If
    GoTo SiguienteSO
    
    Finalizar:
    ActiveCell.Offset(-1, 0).Select
    Selection.End(xlUp).Select
    Application.ScreenUpdating = True
    
    MsgBox "All line keys have been numbered successfuly", vbInformation
    
    End Sub

  4. #4
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    790

    Re: Strange Run-time error '6' Overflow

    The first step is to rework your code to get rid of all of the GoTo statements.

    Without tracing through your code, it is clear that it is hitting the MaxNum = MaxNum + 1 statement more times than you think it should, which means that SOActual.Value2 = SoSigeuiente.Value2 is evaluating to true more times than you think it should, which means that IniciarLoop is being "Gone to" more times than you think it should.

    Beyond that, I can't assist further.

    Good luck.

  5. #5
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,427

    Re: Strange Run-time error '6' Overflow

    Add a break point or a Stop statement to see what is happening with the values:

    Code:
    Sub NumberLineKey(control As IRibbonControl)
    'Number subset of rows starting with 1
    Dim MaxNum As Integer
    Dim SOActual As String
    Dim SOSiguiente As String
    Dim ChooseColumn As Integer
    
    ChooseColumn = InputBox("Please indicate the offset of the line key column", "Enter column offset", -1)
    
    ActiveCell.Select
    Application.ScreenUpdating = False
    
    SiguienteSO:
    MaxNum = 1
    
    IniciarLoop:
    ActiveCell.Select
    SOActual = ActiveCell.Value2
    ActiveCell.Offset(1, 0).Select
    SOSiguiente = ActiveCell.Value2
        If SOActual.Value2 = SOSiguiente.Value2 Then
            Stop ' When this statement is reached, hover the mouse pointer over the various variables to see what they actually contain!
            MaxNum = MaxNum + 1
            GoTo IniciarLoop
        Else
            ActiveCell.Offset(-1, ChooseColumn).Select
            ActiveCell = MaxNum
                Do Until MaxNum = 1
                ActiveCell.Offset(-1, 0).Select
                ActiveCell = MaxNum - 1
                MaxNum = MaxNum - 1
                Loop
        End If
    Selection.End(xlDown).Select
        If ActiveCell.Row = 1048576 Then
        Selection.End(xlUp).Select
        End If
    
    ActiveCell.Offset(1, -ChooseColumn).Select
    If ActiveCell = "" Then
        GoTo Finalizar
    End If
    GoTo SiguienteSO
    
    Finalizar:
    ActiveCell.Offset(-1, 0).Select
    Selection.End(xlUp).Select
    Application.ScreenUpdating = True
    
    MsgBox "All line keys have been numbered successfuly", vbInformation
    
    End Sub

  6. #6

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    5

    Re: Strange Run-time error '6' Overflow

    Thank you! How can I do that?

    Quote Originally Posted by OptionBase1 View Post
    The first step is to rework your code to get rid of all of the GoTo statements.

    Without tracing through your code, it is clear that it is hitting the MaxNum = MaxNum + 1 statement more times than you think it should, which means that SOActual.Value2 = SoSigeuiente.Value2 is evaluating to true more times than you think it should, which means that IniciarLoop is being "Gone to" more times than you think it should.

    Beyond that, I can't assist further.

    Good luck.

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    5

    Re: Strange Run-time error '6' Overflow

    The values are climbing over 20 thousand, when they shouldn't go over 9. I'm not sure where to apply that in order to troubleshoot.

    Quote Originally Posted by jdc2000 View Post
    Add a break point or a Stop statement to see what is happening with the values:

    Code:
    Sub NumberLineKey(control As IRibbonControl)
    'Number subset of rows starting with 1
    Dim MaxNum As Integer
    Dim SOActual As String
    Dim SOSiguiente As String
    Dim ChooseColumn As Integer
    
    ChooseColumn = InputBox("Please indicate the offset of the line key column", "Enter column offset", -1)
    
    ActiveCell.Select
    Application.ScreenUpdating = False
    
    SiguienteSO:
    MaxNum = 1
    
    IniciarLoop:
    ActiveCell.Select
    SOActual = ActiveCell.Value2
    ActiveCell.Offset(1, 0).Select
    SOSiguiente = ActiveCell.Value2
        If SOActual.Value2 = SOSiguiente.Value2 Then
            Stop ' When this statement is reached, hover the mouse pointer over the various variables to see what they actually contain!
            MaxNum = MaxNum + 1
            GoTo IniciarLoop
        Else
            ActiveCell.Offset(-1, ChooseColumn).Select
            ActiveCell = MaxNum
                Do Until MaxNum = 1
                ActiveCell.Offset(-1, 0).Select
                ActiveCell = MaxNum - 1
                MaxNum = MaxNum - 1
                Loop
        End If
    Selection.End(xlDown).Select
        If ActiveCell.Row = 1048576 Then
        Selection.End(xlUp).Select
        End If
    
    ActiveCell.Offset(1, -ChooseColumn).Select
    If ActiveCell = "" Then
        GoTo Finalizar
    End If
    GoTo SiguienteSO
    
    Finalizar:
    ActiveCell.Offset(-1, 0).Select
    Selection.End(xlUp).Select
    Application.ScreenUpdating = True
    
    MsgBox "All line keys have been numbered successfuly", vbInformation
    
    End Sub

  8. #8
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,427

    Re: Strange Run-time error '6' Overflow

    What is SOActual.Value2 when the code execution reaches the Stop statement?

    What is the SOSiguiente.Value2 when the code execution reaches the Stop statement?

    If SOActual.Value2 = SOSiguiente.Value2 is always true, the code will just keep looping and incrementing the MaxNum value which will eventually overflow. Clicking on a different cell may cause the value(s) to change, stopping the loop and preventing the overflow.

    Maybe if you explained what this code is supposed to do, or what you are wanting it to do, in detail, we might be able to give you some ideas as to what to change.
    Last edited by jdc2000; Jul 30th, 2019 at 02:12 PM.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,438

    Re: Strange Run-time error '6' Overflow

    i ran the code from your test workbook in the OP

    ran without error and appeared to produce the correct result

    you should avoid using select or active anything and work with fully qualified ranges
    one reason is if you click anywhere on the excel worksheet, just to see what is going on, the selection is changed

    the gotos are also frowned upon and conditional loops would be better practice
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,868

    Re: Strange Run-time error '6' Overflow

    How about changing
    "Dim MaxNum As Integer" to "Dim MaxNum As Long"?

    This code looks like it's been made for "old" Excel, which could only contain 32K Rows compared to "new" Excel (since 2007?)
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,438

    Re: Strange Run-time error '6' Overflow

    if before clicking on the "Number Line Key" button, you select a different cell and then select cell "D6" again, the macro works properly. Why would that happen?!
    on retesting i found that at the finish of the first macro the active cell is actually D255, so when running the second macro it starts on D255, so max number increments to the end of the worksheet, which will go out of range for an integer variable, fixing the variable type will not make the code work as desired

    again better to avoid working with and changing the activecell
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Strange Run-time error '6' Overflow

    A small suggestion.

    Avoid the use of .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook

    Two Main reasons why .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook etc... should be avoided

    1. It slows down your code.
    2. It is usually the main cause of runtime errors.

    How do we avoid it?

    1) Directly work with the relevant objects

    Consider this code

    Code:
        Sheets("Sheet1").Activate
        Range("A1").Select
        Selection.Value = "Blah"
        Selection.NumberFormat = "@"
    This code can also be written as

    Code:
        With Sheets("Sheet1").Range("A1")
            .Value = "Blah"
            .NumberFormat = "@"
        End With
    2) If required declare your variables. The same code above can be written as

    Code:
        Dim ws as worksheet
    
        Set ws = Sheets("Sheet1")
    
        With ws.Range("A1")
            .Value = "Blah"
            .NumberFormat = "@"
        End With
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  13. #13

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    5

    Re: Strange Run-time error '6' Overflow

    Thank you! I'll try to fix the code and let you know how it went!

  14. #14
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Strange Run-time error '6' Overflow

    Also use "Long" instead of "Integer" when working with Excel rows. I guess you are getting the error because of that.

    Change

    Code:
    Dim MaxNum As Integer
    Dim ChooseColumn As Integer
    to

    Code:
    Dim MaxNum As Long
    Dim ChooseColumn As Long
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  15. #15
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Strange Run-time error '6' Overflow

    Also use "Long" instead of "Integer" when working with Excel rows. I guess you are getting the error because of that.

    Change

    Code:
    Dim MaxNum As Integer
    Dim ChooseColumn As Integer
    to

    Code:
    Dim MaxNum As Long
    Dim ChooseColumn As Long
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width