Results 1 to 4 of 4
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User Defined Functions (XL XP)

    We are adding some user defined functions to an excel project that we are working on but we are having difficulty getting them to fire / calculate. I have one function that returns the number of tabs in a file. I've added this function to two excel files and when I flick from one to the other they both show the tab count from the last time I forced a calculation.

    Is there a way to get these soft of functions to calculate?

    <pre> Public Function NumberOfTabs() As Long
    ' this function returns the number of tabs in the workbook
    NumberOfTabs = ActiveWorkbook.Sheets.Count
    End Function</pre>

    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: User Defined Functions (XL XP)

    You need to tell Excel that it has to re-calculate whenever a change is made, do this by inserting the following line in the function

    Application.Volatile True

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Defined Functions (XL XP)

    You can add a line

    Application.Volatile

    at the beginning of such functions. This means that the result will be updated whenever any recalculation occurs in the spreadsheet. Just inserting a new worksheet may not always trigger a recalculation, however.

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Defined Functions (XL XP)

    Thanks Tony / Hans
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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