+ Reply to Thread
Results 1 to 5 of 5

Volatile - Calculation Order

  1. #1
    Registered User
    Join Date
    06-03-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Volatile - Calculation Order

    Hi Guys

    I have created a workbook with two different User Defined Functions (one to count the number of sheets in my workbook, the other one to force a formula (which is created by combining several text fragments).

    I need the formulae to recalculate every time I change data on any of the sheets in the workbook, so I have set them to be Application.Volatile = True. However, this gives me two problems:

    (1) The formula also attempts to recalculate if I change something in a different workbook, and then gives me an error message. I would thus need to recalculate the field only if i change something in the containing workbook, but not in any other workbook that might be open at the same time.

    (2) Application.Volatile = True seems to put the fileds containing its formula at the top of the recalculation tree, and thus does not update certain changes. For example:

    My UDF refers to cells A1 on Sheet1 and Sheet2.
    Cell A1 on Sheet1 is SUM(A2:A5)

    If i change A3 on Sheet1, Excel recalculates first my UDF which sums up A1 from the two worksheets, and afterwards recalculates the fields A1, which leads to a wrong total in my UDF.

    I hope this is written somewhat understandably. I am a pure autodidact on VBA, so I might miss something all too obvious.

    Thanks!

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Volatile - Calculation Order

    Hard to say without the code, but:
    1. This should not be a problem. If it is, then your code needs changing.
    2. This also should not happen, since a change in A1 should then trigger another recalc of your UDF that depends on it, assuming you are passing A1 as an argument to the UDF.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    06-03-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Volatile - Calculation Order

    Sorry, I guess I should have posted my code:

    The function which forces the text to be treated as a formula and thus be calculated is:

    Please Login or Register  to view this content.
    The function which counts the number of worksheets is

    Please Login or Register  to view this content.
    The formula, in which I use these two, reads:
    =calc("sum("&ADDRESS(ROW()+4,COLUMN(),1,TRUE,"Claim 1:Claim "&sheetcount()-1)&")")

    Claim 1, Claim 2 etc are the name of the worksheets. Everytime a new claim is issued, a new worksheet with the consecutive number will be created. The idea is to sum up all equivalent cells on the Summary page, irrespective of how many Claim sheets there are.

    I hope this helps to clarify my problem.

  4. #4
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,206

    Re: Volatile - Calculation Order

    Rather than go three sides around the barn with these UDF's.

    To sum all the Claim sheets, create a new sheet Front in front of all the Claim sheets and Back after all the claim sheets.

    You can hide Front and Back if you like.

    Then =SUM(Front:Back!A1) will return the sum of A1 from all of the Claim sheets. (including the 0 values from Front and Back)
    Even if you add more Claim sheets, they will be added into the sum. as long as they are added between Front and Back, which is what will happen if ClaimI is active when ClaimN is added.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Registered User
    Join Date
    06-03-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Volatile - Calculation Order

    OMG I can't believe I overlooked such a simple and elegant solution!! That solves all my problems at once! Thank you!!

+ 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