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?!
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
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.
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
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.
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.
Originally Posted by jdc2000
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
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.
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
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?)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
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