+ Reply to Thread
Results 1 to 4 of 4

Nested function calls from Worksheet_Calculate()

  1. #1
    Registered User
    Join Date
    11-04-2004
    Location
    Athens
    MS-Off Ver
    2019
    Posts
    48

    Nested function calls from Worksheet_Calculate()

    Hi all,

    I have created a function which runs on Worksheet_Calculate() event. i used Worksheet_Calculate event as a workaround in order to capture the state in table filters and display the selected filters as there is no Worksheet_Filterupdate() event. so far all is good but,

    as i got sheet A and B with B having references to A, the problem is that every time i change data in sheet A, sheet B Worksheet_Calculate is also triggered creating nested function calls.

    i tried using a doNotRun boolean flag which is set to false in the beginging of my function and to true at the end as the function itself triggers Worksheet_Calculate. but some times the reference sheet Worksheet_Calculate() fires before the sheet that was directly modified so this solution doesnt seem to be very stable.

    i also tried setting Application.Calculation = xlCalculationSemiautomatic or xlCalculationManual but no luck...


    i was thinking if there was a way to know the sheet who's Worksheet_Calculate() is running i could ommit it if it is not the intentional


    any ideas/advice as to how to work around this?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,425

    Re: Nested function calls from Worksheet_Calculate()

    Be good to see your code, but it sounds like you need

    In the Workbook Code Module, you could use the code below, put all your sheet calculate in there and execute one depending on the sh parameter.
    Please Login or Register  to view this content.
    Or, you could try, in each sheet code module:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-04-2004
    Location
    Athens
    MS-Off Ver
    2019
    Posts
    48

    Re: Nested function calls from Worksheet_Calculate()

    Oh! this is the solution... i just tried it and it works sweet!

    inside Workbook_SheetCalculate(ByVal Sh As Object) I only run my code If Sh.Name = ActiveSheet.Name

    this is what i was imaggining when i was saying
    i was thinking if there was a way to know the sheet who's Worksheet_Calculate() is running
    i didnt realise that workbook had its own version of calculate - Thx!
    Last edited by S@S; 07-21-2019 at 05:06 PM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,425

    Re: Nested function calls from Worksheet_Calculate()

    You're welcome.

+ 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: 2
    Last Post: 01-05-2019, 03:33 AM
  2. [SOLVED] Function that calls other functions
    By chipp300 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2014, 08:03 PM
  3. reset function calls
    By kbka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2014, 09:00 AM
  4. Using ODBC function calls
    By Dave in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-15-2006, 10:40 AM
  5. VBA: How to pass arrays in Function Calls?
    By Mac Lingo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2005, 04:09 PM
  6. [SOLVED] VB macro - Nested Calls ( Parameter Passing )
    By Deepak in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-03-2005, 12:05 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