+ Reply to Thread
Results 1 to 6 of 6

Recalculation of UDF

  1. #1
    Registered User
    Join Date
    09-13-2010
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    9

    Recalculation of UDF

    I created a UDF in VBA to use in a workbook that takes the number of a sheet , e.g. Sheets(3) and returns the name of the sheet which is possible in VBA. The Sheets workbook function works in reverse fashion by returning the sheet number when supplied the name. I have a column of numbers and the next column uses these to list the worksheets by name corresponding to the values in the previous column. However if I rename a sheet, and I do this with VBA macros when appropriate to my application, the UDF in the aforementioned column on the worksheet does not automatically refresh. How can this be accomplished? Can I do a Range.calculate or something like that in VBA?

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

    Re: Recalculation of UDF

    Yes, you can calculate a specified range. Range.Calculate method help file: https://docs.microsoft.com/en-us/off...ange.calculate
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    09-13-2010
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Recalculation of UDF

    Would the same problem exist in a VLOOKUP if the cell pointed to is changed but the lookup value does not. The cell is dependent on the lookup value which doesn't change but the value that would be returned changes because it is changed by another routine that writes an updated value. The dependency does not see that the the antecendent cell is changed so it is not updated? Or is the system able to mark this as dirty.

  4. #4
    Registered User
    Join Date
    09-13-2010
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Recalculation of UDF

    The Lookup is not a problem. I just checked it out. If the target value changes due to a routine setting a new value, the lookup fires and finds the new value. Couldn't figure out how to delete my post so posting this comment.

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

    Re: Recalculation of UDF

    Is there more to your question, then, or does that cover it?

  6. #6
    Registered User
    Join Date
    09-13-2010
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Recalculation of UDF

    SOLVED Recalculation of UDF can be accomplished range.calculate code in VBA if required.

+ 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. Automatic recalculation
    By KalEl in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-04-2012, 05:03 PM
  2. Recalculation
    By xlearner in forum Excel General
    Replies: 3
    Last Post: 02-20-2011, 01:42 PM
  3. disable VB recalculation
    By jmhultin in forum Excel General
    Replies: 3
    Last Post: 05-14-2010, 11:36 AM
  4. Recalculation
    By CorbinDallis in forum Excel General
    Replies: 1
    Last Post: 10-10-2008, 09:58 PM
  5. Recalculation
    By blatham in forum Excel General
    Replies: 0
    Last Post: 12-27-2006, 11:46 AM
  6. Recalculation
    By workerboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2006, 01:20 PM
  7. [SOLVED] RE: Recalculation
    By r van setten in forum Excel General
    Replies: 3
    Last Post: 03-19-2006, 01:45 PM
  8. [SOLVED] Recalculation
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-20-2005, 08:05 AM

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