Forum Discussion
After upgrade the MS Office 365 getting error 2147417848 Method 'Value' of object 'Range' failed
I do not get that error. However, I see that the Worksheet_Change event risks going into an endless loop, because the Change event causes itself to be called again and again due to the fact that the value of the cell that triggered the event to fire is changed within the event itself. This may cause a stack overflow. This may be prevented by adding Application.EnableEvents = False prior to any action in that event that changes a cell, e.g.:
' Existing other code here Else sString2Replace = Target.Text 'removes any instances of a £ symbol For iLoopCount = 1 To Len(sString2Replace) If Mid(sString2Replace, iLoopCount, 1) = "£" Then sString2Replace = Replace(sString2Replace, "£", "") Next Application.EnableEvents = False 'Added by JKP Target.Value = sString2Replace Application.EnableEvents = True 'Added by JKP End If 'Remainder of code
You can enable the Debug button on the error message:
- open the file
- open the VBA editor
- expand the project so it asks for the password and enter the password
- Now repeat the actions that lead to the error.
Find attached error msg.
- After debugging found that getting the error in below code
'Currency Check
Case "$E"
If Target = "" Then
Me.Protect "SST0122BP6002"
Exit Sub
End If
If IsCurrency(Target.Text) = False Then
MSGNotValidCurrency
Target = ""
Else
sString2Replace = Target.Text
'removes any instances of a £ symbol
For iLoopCount = 1 To Len(sString2Replace)
If Mid(sString2Replace, iLoopCount, 1) = "£" Then sString2Replace = Replace(sString2Replace, "£", "")
Next
Target.Value = sString2Replace
End If I do not get that error. However, I see that the Worksheet_Change event risks going into an endless loop, because the Change event causes itself to be called again and again due to the fact that the value of the cell that triggered the event to fire is changed within the event itself. This may cause a stack overflow. This may be prevented by adding Application.EnableEvents = False prior to any action in that event that changes a cell, e.g.:
' Existing other code here Else sString2Replace = Target.Text 'removes any instances of a £ symbol For iLoopCount = 1 To Len(sString2Replace) If Mid(sString2Replace, iLoopCount, 1) = "£" Then sString2Replace = Replace(sString2Replace, "£", "") Next Application.EnableEvents = False 'Added by JKP Target.Value = sString2Replace Application.EnableEvents = True 'Added by JKP End If 'Remainder of code- Thanks alot. after this change now it starts working.. Thanks for your help.
- Excellent! You may have to inspect the entire call tree, starting from the Change event, for other areas where cells are modified in code.