Clumsy VBA code slows down Excel

[BenRichards]

Hi,

I've had some 25 of these kinds of IF-statements, and the worksheet has worked quickly:

Private Sub Worksheet_Change(ByVal Target As Range)
.
.
.

If Not Intersect(Target, Range("T82:AA457")) Is Nothing And ActiveSheet.Range("V80").Value = "-" Then
Application.EnableEvents = False
ActiveSheet.Range("T82:AA457").Value = ""
Application.EnableEvents = True
End If

However, I now added 25 other IFs, like this:

If Intersect(Target, Range("V80")) Is Nothing And ActiveSheet.Range("V80").Value = "-" Then
Application.EnableEvents = False
ActiveSheet.Range("T82:AA457").Value = ""
Application.EnableEvents = True
End If

As a result, whenever a cell is changed (any cell, not just the ones determined as the target ranges), Excel "thinks" for some five seconds and the mouse cursor indicates processing before the new cell value is displayed. Can someone help me?

 

[BenRichards]

And this gives the error type mismatch:

If Not ActiveSheet.Range("AC82:AJ457").Value = "" And ActiveSheet.Range("V80").Value = "-" Then
Application.EnableEvents = False
ActiveSheet.Range("AC82:AJ457").Value = ""
Application.EnableEvents = True
End If

 

[Andrew Poulsom]

The Worksheet_Calculate event procedure doesn't have a Target argument, hence the error in your first piece of code. Your second piece of code fails because you can't test the Value of more than one cell for equality. Why does it matter that AC82:AJ457 is empty or not?

 

[BenRichards]

In short: if I use Private Sub Worksheet_Calculate(), how can I pinpoint the code to fire only if a certain cell was changed?

 

[BenRichards]

Andrew Poulsom, if I leave them like this, all 25 of them:

If ActiveSheet.Range("AE80").Value = "-" Then
Application.EnableEvents = False
ActiveSheet.Range("AC82:AJ457").Value = ""
Application.EnableEvents = True

Then I'm stuck with the some five seconds Excel thinks after each cell-change.

 

[Andrew Poulsom]

if I use Private Sub Worksheet_Calculate(), how can I pinpoint the code to fire only if a certain cell was changed?

You can't. What are V80's dependents? If they are changed manually you can use those in Worksheet_Change.

 

[BenRichards]

You can't. What are V80's dependents? If they are changed manually you can use those in Worksheet_Change.

Sadly they are the result of a formula. V80 can be any letter or number or "-". But this is part of a bigger worksheet, so I can't alter how they are changed.

And if I use the straight IFs that fire at every turn, I'm stuck with the 5-second delay that makes the worksheet user-unfriendly...

 

[Andrew Poulsom]

Then I think you are stuck.