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

Thread: Recalculation when changing filter- why/when?

  1. #1
    New Member mikecroom's Avatar
    Join Date
    Oct 2010
    Location
    London, UK
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Recalculation when changing filter- why/when?

    I am using the function GetAdsprop from Query Active Directory from Excel. I have it stored as an Add-In.
    I have a long column of Windows IDs and use GetAdsprop to get the associated email address of each one. Each lookup takes quite a long time.

    Every time I change the filtering all of the GetAdsProp seem to recalculate.

    Can anyone explain to me the rules for when filtering triggers a recalculation and whether it is possible to avoid it.
    I have no volatile functions that I know of and GetAdsProp does not declare itself as volatile

    I am on Excel 2007 in this instance

    Mike

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Recalculation when changing filter- why/when?

    AutoFiltering is a volatile action:

    Volatile Excel Functions -Decision Models
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Aug 2012
    Location
    Auckland, NZ
    Posts
    1,525
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculation when changing filter- why/when?

    Andrew,

    Would setting the Calculate option to manual resolve Mike's problem or are volatile functions always 'auto calculated'?

    Regards
    Peter

    Excel 2016, Windows 10
    Accuracy in posting formula's to the forum will help enormously, clairvoyancy is a skill I've yet to develop!!

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Recalculation when changing filter- why/when?

    Yes.
    Microsoft MVP - Excel

  5. #5
    Board Regular
    Join Date
    Aug 2012
    Location
    Auckland, NZ
    Posts
    1,525
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculation when changing filter- why/when?

    Many thanks.
    Peter

    Excel 2016, Windows 10
    Accuracy in posting formula's to the forum will help enormously, clairvoyancy is a skill I've yet to develop!!

  6. #6
    New Member mikecroom's Avatar
    Join Date
    Oct 2010
    Location
    London, UK
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recalculation when changing filter- why/when?

    Quote Originally Posted by Andrew Poulsom View Post
    AutoFiltering is a volatile action:

    Volatile Excel Functions -Decision Models

    Thank you once again Andrew.
    I didn't realise there were volatile actions as well as volatile functions.
    Just at the moment I can't see why changing autofilter should be volatile - but I expect there is a good reason.

    And the article explains why there was a big recalc on workbook open. The UDF was in an Add-In.

    If I get very motivated I might turn the function into a subroutine to process a whole array at one time and see if that speeds things up.
    I am not a fan of manual calculation

    Mike

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
  •