47

I am having a problem with Excel crashing, when I run VBA code on an excel sheet.
I'm trying to add the following formula on worksheet change:

Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("testpage").Range("A1:A8").Formula = "=B1+C1" End Sub

When this code is run i get a message saying "excel has encountered a problem and needs to close" and excel closes.

enter image description here

If I run the code in the Worksheet_Activate() procedure, it works fine and doesn't crash

Private Sub Worksheet_Activate() Worksheets("testpage").Range("A1:A8").Formula = "=B1+C1" End Sub

But I really need it to work in the Worksheet_Change() procedure.

Has anyone experienced similar crashes when using the Worksheet_Change() event and can anyone point in the right direction to fix this issue ?

Improve this question
3
  • Code in a class does not "crash" there, but rather in the calling code. Does "testpage" exist ? Commented Dec 13, 2012 at 14:04
  • Yes it exist, the error seems to only happen when i run the code in Worksheet_Change Commented Dec 13, 2012 at 14:09
  • Is Range("A1:A8") in the "testpage" sheet clean when you write the formula? There's no pivot table or anything like that already there? Also, just curious, why would you need to write the same formula in the same place after every change in a worksheet? Commented Dec 13, 2012 at 14:12

3 Answers 3

Reset to default
86

I recommend this when using Worksheet_Change

  1. You do not need the sheet name. In a Sheet Code Module, an unqualified Range reference refers to that sheet. That said, it is clearer to use the Me qualifier. If you are trying to use another sheet, then qualify the range reference with that sheet.

  2. Whenever you are working with Worksheet_Change event, always switch Off events if you are writing data to any cell. This is required so that the code doesn't retrigger the Change event, and go into a possible endless loop

  3. Whenever you are switching off events, use error handling to turn it back on, else if you get an error, the code will not run the next time.

Try this

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoa Application.EnableEvents = False Me.Range("A1:A8").Formula = "=B1+C1" Letscontinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume Letscontinue End Sub

Few other things that you may want to know when working with this event.

If you want to ensure that the code doesn't run when more than one cell is changed then add a small check

Private Sub Worksheet_Change(ByVal Target As Range) '~~> For Excel 2003 If Target.Cells.Count > 1 Then Exit Sub ' '~~> Rest of code ' End Sub

The CountLarge was introduced in Excel 2007 onward because Target.Cells.Count returns an Long value which can error out in Excel 2007 becuase of increased total cells count.

Private Sub Worksheet_Change(ByVal Target As Range) '~~> For Excel 2007 If Target.Cells.CountLarge > 1 Then Exit Sub ' '~~> Rest of code ' End Sub

To work with all the cells that were changed use this code

Private Sub Worksheet_Change(ByVal Target As Range) Dim aCell As Range For Each aCell In Target.Cells With aCell '~~> Do Something End With Next End Sub

To detect change in a particular cell, use Intersect. For example, if a change happens in Cell A1, then the below code will fire

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1")) Is Nothing Then MsgBox "Cell A1 was changed" '~~> Your code here End If End Sub

To detect change in a particular set of range, use Intersect again. For example, if a change happens in range A1:A10, then the below code will fire

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then MsgBox "one or more Cells in A1:A10 range was changed" '~~> Your code here End If End Sub

Note: If you were getting an error earlier and you made the above changes and If your code is still not working then it is possible that the events have not been reset. In the Immediate Window, type Application.EnableEvents = True and press the ENTER key. This will reset it to True. If you do not see the Immediate Window, the press the shortcut key Ctl+G to launch the Immediate Window.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

thanks Siddharth when i removed the sheet name it worked fine without any crashes
Gr8! I hope you switched off/on the events as well as suggested above?
+1. One other good practice is to further limit the area whose change triggers the code by surrounding with something like: If Not Intersect(Target, Range("D1:D8")) Is Nothing Then ... End If. In this example, your code would only run if a cell in this range was changed.
@Siddharth, @Doug, why use Range() inside the Worksheet_Change function, instead of Target.Parent.Range(), which I would argue is more correct and safer? Range() refers to the active sheet, which isn't necessarily the one that is being changed (e.g. if VBA code causes the change to this worksheet with another worksheet active).
@ErikEidt, I generally use Me.Range inside the event. But my testing confirms what Siddharth says in his answer, i.e., Range inside a Worksheet's Change event will refer to a range in that sheet, regardless of which sheet is active.
|
16

Excel was crashing, not the VBA function.
The events were not disabled and the call stack was filled by an infinite loop of OnChange events.
A little advice that helps finding this type of errors: set a breakpoint on the first line of the event, then execute it step by step pressing F8.

Improve this answer

Comments

2

Also this solution is good:

Option Explicit Private Busy As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If Not Busy Then Busy = True Range("A1:A8").Formula = "=B1+C1" Busy = False End If End Sub
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.