Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
3

I am having a problem with excel crashing when i run VBA code on an excel sheet. I have some code to add a formula to a range of cells as follows:

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.

IF i run the code in the worksheet_active() class 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() class.

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

share|improve this question
Code in a class does not "crash" there, but rather in the calling code. Does "testpage" exist ? – iDevlop Dec 13 '12 at 14:04
Yes it exist, the error seems to only happen when i run the code in Worksheet_Change – derek Dec 13 '12 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? – Scott Holtzman Dec 13 '12 at 14:12

2 Answers

up vote 12 down vote accepted

I always recommend this when using Worksheet_Change

  1. You do not need the sheet name. It is understood that the code is to be run on current sheet UNLESS you are trying to use another sheet as a reference. Is "testpage" the Activesheet name or is it a different sheet?

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

  3. Whenever you are switching off events, use error handling 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

    Range("A1:A8").Formula = "=B1+C1"

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

HTH

share|improve this answer
thanks Siddharth when i removed the sheet name it worked fine without any crashes – derek Dec 13 '12 at 14:18
Gr8! I hope you switched off/on the events as well as suggested above? – Siddharth Rout Dec 13 '12 at 14:19
No will i have to do that aswell ? – derek Dec 13 '12 at 14:21
It is recommended that you do it. See above code example... – Siddharth Rout Dec 13 '12 at 14:22
3  
+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. – Doug Glancy Dec 13 '12 at 14:36
show 4 more comments
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.