+ Reply to Thread
Results 1 to 5 of 5

Identifying a cell that has changed

  1. #1

    Identifying a cell that has changed

    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


  2. #2
    Vacation's Over
    Guest

    RE: Identifying a cell that has changed

    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


    "[email protected]" 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
    >
    >


  3. #3

    Re: Identifying a cell that has changed

    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


  4. #4
    Vacation's Over
    Guest

    Re: Identifying a cell that has changed

    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


    "[email protected]" 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
    >
    >


  5. #5

    Re: Identifying a cell that has changed

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1