Sigh.. Macro delay still not working

[Tornados]

Hi all,

I have this code implemented, which should prohibit the CDO_Send macr
to run if it already ran 10 minutes ago..

However, somehow it currently does not trigger the macro at al
anymore. Sometimes when i just opened the file and something changes i
does trigger.., there is no logic behind it though. I've tried to mov
the dlastsent line between the 'end if' at the bottom.. yet no matte
what i do, it does not work.

So now and then it s) also gives a type mismatch error in thi
line:

If myVals(iRow, iCol) = myRng.Cells(iRow, iCol) Then

Cells in the range change through an if function which itselfs relate
to another cell in which values change due to an dde function in agai
another cell......

I hope someone can help me out here....

============================
Option Explicit
Private Sub Worksheet_Calculate()
Static dLastSent As Double
If dLastSent = 0 Then dLastSent = Now - TimeValue("00:11:00")
If Now >= (dLastSent + TimeValue("00:10:00")) Then

Static myVals As Variant
Dim iRow As Long
Dim iCol As Long
Dim myRng As Range
Dim SomethingChanged As Boolean

Set myRng = Me.Range("i4:n27")

If IsEmpty(myVals) Then
myVals = myRng.Value
Else
SomethingChanged = False
For iRow = LBound(myVals, 1) To UBound(myVals, 1)
For iCol = LBound(myVals, 2) To UBound(myVals, 2)
If myVals(iRow, iCol) = myRng.Cells(iRow, iCol) Then
'do nothing--no change here
Else
SomethingChanged = True
CDO_Send
dLastSent = Now
End If
Next iCol
Next iRow
If SomethingChanged Then
myVals = myRng.Value
End If

End If

End If

End Sub
===============

 

[Dave Peterson]

It looks to me your existing code tries to call CDO_Send for each change in the
range--not just once for any change in i4:n27.

And if the cell contained an error, you could get a mismatch. Do your formulas
evaluate to errors?

And I'm not sure which macro you're writing about when you say "somehow it
currently does not trigger the macro at all". Do you mean the CDO_Send or the
worksheet_calculate?

This seemed to work ok for me when I changed things manually (I don't know
anything about updating via DDE):

Option Explicit
Private Sub Worksheet_Calculate()
Static dLastSent As Double
Static myVals As Variant
Dim iRow As Long
Dim iCol As Long
Dim myRng As Range
Dim SomethingChanged As Boolean


If dLastSent = 0 Then
dLastSent = Now - TimeValue("00:11:00")
End If

If Now >= (dLastSent + TimeValue("00:10:00")) Then
Set myRng = Me.Range("i4:n27")

If IsEmpty(myVals) Then
myVals = myRng.Value
Else
SomethingChanged = False
For iRow = LBound(myVals, 1) To UBound(myVals, 1)
For iCol = LBound(myVals, 2) To UBound(myVals, 2)
If CStr(myVals(iRow, iCol)) _
= CStr(myRng.Cells(iRow, iCol).Value) Then
'do nothing--no change here
Else
SomethingChanged = True
End If
Next iCol
Next iRow
If SomethingChanged Then
CDO_Send
dLastSent = Now
myVals = myRng.Value
End If
End If
End If

End Sub