Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Need to know when calculated value has changed

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'd like to be able to tell which cell changed (As in WorksheetChange).
    WorksheetCalculate doesn't tell me which
    cell has changed.
    Usually I use InitializeLinks because my
    data is coming from an outside source and
    this works perfectly. In this case however,
    it's being calculated from within the worksheet. Can I fake out excel into thinking it's coming in from an external source and use initializeLinks?
    Or is there an easy way to do this?

    Many thanks in advance.

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi chcculle,

    The reason why the worksheet's Calculate event doesn't tell which cell has changed (like the Change event does) is that, typically, hundreds or even thousands or millions of cells can change on a Calculate event. If Volatile is True, then all formulas re-calculate, and if iteration is allowed and there are circular references, the number of cell value changes can get VERY large. If you really want to know all the cells that change when a calculation is done, you can trap the Change event to determine which cell change triggered the re-calculate event, then look at all the dependents of the cell using the Dependents property that provides a Range object that contains all the dependent cells. These cells will all have changed. But then you must keep going down the Dependents tree because other cells might be dependent on these dependents. So you really need a recursive algorithm to search the entire tree structure of dependents to determine all of the cells that change as a result of a calculation.

    I realize that this has gotten a bit complicated, but unfortunately that is because there is a lot more to this question than first meets the eye.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Damon,
    Thanks for your response. I'm still trying to figure out the best way to do this.
    I'd like to explore further your suggestion
    of trapping the event. I don't think it
    will be quite as difficult as what you were mentioning because I don't care about the
    dependants who changed, I only care about the
    calculated value.
    For example Cell1 and Cell2 are DDE inputs.
    Lets say Cell3 = Cell2 - Cell1, I only care that Cell3 has changed.
    Could you or someone explain how I can trap this event?
    Thanks.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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