Accumulator Logic

[Guest]

hi,
I'm using the following code taken from "McGimpsey & Associates" site and it
works nicely for me but I'm not able to undrestand how the code works and
what its logic is.can anybody explain to more about this codes,particulary
the line "Application.EnableEvents = False or true"?thanx.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "D1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("c1").Value = Range("c1").Value + .Value
Application.EnableEvents = True
End If
Next
End With
End Sub

 

[Guest]

Looks like bad code to me
the next should be an end if
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "D1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("c1").Value = Range("c1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

i wouldn't know why enableevents would matter unless there is more code
somewhere.

 

[JE McGimpsey]

I can probably help:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "D1" Then

We only operate on the accumulator if the changed range is the single
cell D1.

If IsNumeric(.Value) Then

Checks that the entry is a number

Application.EnableEvents = False

Since the next line will change the value in C1, if we don't disable
events, doing so will fire the Worksheet_Change code again. Disabling
events allows us to change the C1 value without triggering the _Change
event.

Range("c1").Value = Range("c1").Value + .Value

Adds the value in D1 to the value in C1

Application.EnableEvents = True

Turns on Event macros again.

 

[JE McGimpsey]

Just prevents firing the _Change macro again when the change is made to
C1.

 

[Guest]

Sorry John, because I extracted part of the codes.the "next" must be omitted
here.
thank you.

 

[Guest]

Thanks a lot

I can probably help:


We only operate on the accumulator if the changed range is the single
cell D1.


Checks that the entry is a number


Since the next line will change the value in C1, if we don't disable
events, doing so will fire the Worksheet_Change code again. Disabling
events allows us to change the C1 value without triggering the _Change
event.


Adds the value in D1 to the value in C1


Turns on Event macros again.

 

[Guest]

Sorry,what do you mean by "fire the worksheet_chnage"?

 

[JE McGimpsey]

Cause excel to execute the Worksheet_Change macro.

If a worksheet cell is changed, even if it's by the Worksheet_Change()
macro, the macro will be put on hold, and the Worksheet_Change macro
called with the new Target.

In this case, the second call will exit without doing anything (since
the new Target will be cell C1 rather than cell D1) before resuming
execution of the first instance.

But there's no reason for it to be executed at all.