I have an external DDE connection that updates a range of cells in a
sheet.
I need to verify the data sent. I'm trying to do this in the
Worksheet_Calculate() subroutine. However it requires that I verify
every cell every time even if only a single cell has been updated. Is
there a way to identify the delta without iterating through all the
cells?

Thanks

Avian

Lots of ways to approach this. Need more information to point to best way.
How many cells are likely to change each update?,
how often do you update?
Do you want just to know what changed or both old and new values for cell?
Is it values you need to track or include format, font, settings?
Are values strings, values, other?
Precision? - does new value of 12.6 = old value of 13


" wrote:

I have an external DDE connection that updates a range of cells in a
sheet.
I need to verify the data sent. I'm trying to do this in the
Worksheet_Calculate() subroutine. However it requires that I verify
every cell every time even if only a single cell has been updated. Is
there a way to identify the delta without iterating through all the
cells?

Thanks

Avian

There are about 50 cells that can change as a result of prices being
pushed to us from an external vendor.
The rate at which they change range from about one a minute to 1 or 2 a
second.
We don't need to know the old value.
We don't care about formatting.
The values should all be numbers.

The values that get send to us need to validated as numeric and then
rounded up or down before being copied to another area in the sheet
where another 3rd party picks them up via DDE.
The input numbers have a precision of 6 decimals and are rounded to 3
decimals with the final digit always being either 0 or 5.
The code I have now iterates through the range 'swapRateBid' each time
calculate is called - which is every time one of the prices changes and
is pushed to us. I'd like to validate just the cell that changed as
you could with Worksheet_Change but but I can't see a way to do this in
Worksheet_Calculate(). The issue is that at times when there is a lot
of price changes going on we hit 100% CPU....

The bones of the code is

Private Sub Worksheet_Calculate()
roundDown ("swapRateBid")
roundUp("swapRateOffer")
........
End Sub

Sub roundDown(rngStr As String)
'Round to half ticks
Dim c As Range
Dim val
For Each c In Range(rngStr)
Debug.Print ("A")
If IsNumeric(c) Then
val = c.Value * 100
If ((val - Int(val)) 0.5) Then
val = 0.5 + Int(val)
ElseIf ((val - Int(val)) < 0.5) Then
val = Int(val)
End If
val = val / 100
c.Offset(0, -4).Value = val
End If
Next c
End Sub


Thanks!

Avian

Two approaches come to mind

First: use worksheet_Change event instead of calculation event
worksheet_change returns a range named Target
which is the range that was changed
check if target is in your monitored range then
recalculate only dependents of the changed cell

Second: with CPU maxing and possible 1 - 2 changes per second
you may want to build a delay into the routine. How timely does the info
really need to be? 1 second, 3 seconds - 15 seconds?
Then you could accumulate changes and send through in a batch
remember that your clock speed is churning faster than you can possibly see
on the screen

Even resetting to updating at most once per second could address your CPU
issue

If these seem appropriate post back and we can proceed with code


" wrote:

There are about 50 cells that can change as a result of prices being
pushed to us from an external vendor.
The rate at which they change range from about one a minute to 1 or 2 a
second.
We don't need to know the old value.
We don't care about formatting.
The values should all be numbers.

The values that get send to us need to validated as numeric and then
rounded up or down before being copied to another area in the sheet
where another 3rd party picks them up via DDE.
The input numbers have a precision of 6 decimals and are rounded to 3
decimals with the final digit always being either 0 or 5.
The code I have now iterates through the range 'swapRateBid' each time
calculate is called - which is every time one of the prices changes and
is pushed to us. I'd like to validate just the cell that changed as
you could with Worksheet_Change but but I can't see a way to do this in
Worksheet_Calculate(). The issue is that at times when there is a lot
of price changes going on we hit 100% CPU....

The bones of the code is

Private Sub Worksheet_Calculate()
roundDown ("swapRateBid")
roundUp("swapRateOffer")
........
End Sub

Sub roundDown(rngStr As String)
'Round to half ticks
Dim c As Range
Dim val
For Each c In Range(rngStr)
Debug.Print ("A")
If IsNumeric(c) Then
val = c.Value * 100
If ((val - Int(val)) 0.5) Then
val = 0.5 + Int(val)
ElseIf ((val - Int(val)) < 0.5) Then
val = Int(val)
End If
val = val / 100
c.Offset(0, -4).Value = val
End If
Next c
End Sub


Thanks!

Avian

The problem with using worksheet_change is that it's not fired as a
result of calculations; and the problem with using a timer is that
we're producing a live pricing feeds to a third party and cannot to
miss a single price. I ascertained - through some helpful debug code
that Microsoft forgot to remove from their YIELD function, that we were
calculating a yield curve and interpolating far more often that needed.
I fixed this and it looks good now. Thanks very much for your help
though!

Avian