+ Reply to Thread
Results 1 to 5 of 5

UDF seem to be calculating when non dependant cells are edited

  1. #1
    Registered User
    Join Date
    04-15-2015
    Location
    Falmouth
    MS-Off Ver
    2007
    Posts
    4

    UDF seem to be calculating when non dependant cells are edited

    I am new to VBA so I do not know fully how Excel handles UDF

    I have about 33 UDFs that are used for analysing waves, non of them are volatile. I have calculations across 10 worksheets on one workbook with a few thousand cells being used in each worksheet

    My issue is that I can type in any cell that is empty with no cells referencing it and when I enter the data in the cell, excel seems to be calculating stuff and this can slow down the use of the worksheet by a few seconds after typing in any cell.

    I have tried using the watch window and I can see that all my UDFs seem to do something even though I am typing in non dependant cells

    I was hoping someone could help explain why UDFs would seem to calculate when typing in any cell and how to stop it. I want it to stay on automatically calculate but I only want the UDFs to work when a cell they're referencing changes

    Any help on this would be very much appreciated, thanks!

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: UDF seem to be calculating when non dependant cells are edited

    UDFs are functions and will therefore be calculated whenever the sheet is recalculated.

    Is it possible to replace the UDFs with macros?
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    04-15-2015
    Location
    Falmouth
    MS-Off Ver
    2007
    Posts
    4

    Re: UDF seem to be calculating when non dependant cells are edited

    The aim of the worksheet is to eventually lock everything down and to give it to anyone that has no experience of wave analyses and can easily use it, therefore the sheet ideally needs to provide quick feedback when different inputs are changed and show how the different inputs effect the outcome. Basically an idiot proof program, therefore I am not sure macros will quite achieve what I am looking for.

    UDFs are functions and will therefore be calculated whenever the sheet is recalculated.
    So you're saying all cells with udf's will recalculate whether or not the changed cell is referenced?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: UDF seem to be calculating when non dependant cells are edited

    I will agree with Hayley_ on this issue, mehmetcik. UDF's normally should not calculate with each and every calculation event. The main point of having Excel calculate dependencies and dependency trees is to avoid calculating any function (native Excel or user-defined) unless it is needed.

    Hayley_, I must admit that I cannot recreate the behavior you describe. You may need to upload a sample spreadsheet that illustrates the problem. Over the years that I have been using UDF's for various calculations, I do not see those UDF's recalculate with every calculation event. They will calculate when cells the UDF is dependent on change, but I can edit other areas of the spreadsheet all day without triggering the UDF calculation. I think there is more to you issue than just that you are using a UDF. Look for other volatile functions (INDIRECT(), RAND(), TODAY(), OFFSET(), etc.) in the dependecy tree feeding into your UDF. My first suspicion is that you have some other volatile function in your spreadsheet that is causing your UDF to recalculate.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: UDF seem to be calculating when non dependant cells are edited

    Since you found the abbreviated list of volatile function useful for identifying the real volatile function in your spreadsheet, I would suggest this, more complete list and discussion of Excel's volatile functions: http://www.decisionmodels.com/calcsecretsi.htm

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 01-09-2014, 10:15 AM
  2. UDF is Not Calculating when dependant cells are changed
    By mac.young in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-29-2013, 08:22 AM
  3. Replies: 0
    Last Post: 07-19-2012, 07:31 AM
  4. [SOLVED] Locking edited cells
    By ANDREWA in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-27-2009, 03:47 AM
  5. pROTECT OR lOCK CELLS SO THAT THEY CANNOT BE EDITED
    By facmess1 in forum Excel General
    Replies: 1
    Last Post: 05-31-2007, 05:26 PM

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