+ Reply to Thread
Results 1 to 5 of 5

UDF in excel updating all tabs to the same number (I'm expecting to see different #s)

  1. #1
    Registered User
    Join Date
    07-02-2021
    Location
    Iowa
    MS-Off Ver
    MS Office for 365
    Posts
    2

    UDF in excel updating all tabs to the same number (I'm expecting to see different #s)

    Hello -
    I have the following UDF set up to calculate the break- even point in excel.

    The UDF is referenced on multiple tabs throughout the workbook. When one tab is refreshed, ALL of the tabs that reference the same UDF are updated to the same number (although the cash flows are different on the various tabs, and I'd expect the number to be different as well).

    For example, let's say tab1-cellA1 should show "5" and tab2-cellA2 should show "10". If I click on tab 1-cell A1 and refresh, all the tabs now show "5" in cell A1. If I then click on tab 2-cell A1 and refresh, all of the tabs show "10" in cell A1.

    Is it possible to update the entire workbook at once and have the correct values be calculated? I can't seem to figure out why excel is updating all tabs to the same value.

    Thank you!


    Please Login or Register  to view this content.
    Last edited by ali0485; 07-02-2021 at 12:07 PM.

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

    Re: UDF in excel updating all tabs to the same number (I'm expecting to see different #s)

    This forum can be strict about some rules, like putting code in code tags. Edit your post to include these tags to avoid getting the topic locked.

    Until that is done, this hint: what does the SumProfit=SumProfit+ActiveSheet.Cells(r,c).Value statement do?
    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
    07-02-2021
    Location
    Iowa
    MS-Off Ver
    MS Office for 365
    Posts
    2

    Re: UDF in excel updating all tabs to the same number (I'm expecting to see different #s)

    Sorry about that! Thanks for letting me know. Code is updated in original post.

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

    Re: UDF in excel updating all tabs to the same number (I'm expecting to see different #s)

    Did you look at that statement? What is going to happen here is that VBA is taking values from ActiveSheet (which may or may not be the same sheet that the function resides in) and adding them up. Since there can only be one active sheet at a time and you have multiple copies of the function in multiple sheets, all of those copies will return the same answer based on whatever the one active sheet is.

    Structured like this, you need to use something like Application.Caller to get at the actual sheet each copy of the function resided in. I never use Application.Caller, so I'm not sure exactly what that looks like, but some experimentation should help you discover how that might work.

    The way I would structure this would be to pass the actual range with the values into the function:
    Please Login or Register  to view this content.
    Note that, doing it this way, I should not need application.volatile nor should I need to refer to application.caller or activesheet. The function knows what data it is processing because that data is passed to the function.

    Hopefully that helps.

  5. #5
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: UDF in excel updating all tabs to the same number (I'm expecting to see different #s)

    I think solver was invented for this. Unable to test due to lack of sample file
    Messages have been translated from Dutch to English by means of google translate.

+ 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. updating multiple tabs with a workbook
    By martiniscross in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2016, 09:59 AM
  2. Replies: 2
    Last Post: 10-19-2015, 03:14 AM
  3. Tabs, Can I add multiple tabs and name then in a repeating number?
    By mazzonem002 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-01-2014, 08:52 AM
  4. [SOLVED] NEw tabs, auto updating summary tab
    By Mechanical Pencil in forum Excel General
    Replies: 3
    Last Post: 11-11-2013, 09:57 AM
  5. [SOLVED] Updating ID Number from Another Excel
    By nymacaco in forum Excel General
    Replies: 2
    Last Post: 06-19-2012, 01:03 PM
  6. Replies: 4
    Last Post: 05-20-2010, 07:52 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