+ Reply to Thread
Results 1 to 10 of 10

UDF Function to calculate Sheet Offset not calculating correctly

  1. #1
    Registered User
    Join Date
    04-04-2011
    Location
    Cambridge, UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    UDF Function to calculate Sheet Offset not calculating correctly

    Hi,

    I am seeing a very strange problem, which I don't fully understand, which must surely relate to the combined use of Application.Caller and Application.Volatile.

    There is a related post here, which summarises what I have done in my function, so I think it's correct: http://www.excelforum.com/excel-prog...ml#post2502204

    I have added a UDF to update sheets automatically. Essentially I have pairs of sheets for each country, so that I want Country#2 sheet to refer to Country#1 sheet to calculate certain cells. Previously the country name was hardcoded in the formulae, but I want to make it more generic.
    Having given the spreadsheet to a client, he has made multiple copies of the same spreadsheet. What he has observed is that
    a) he opens first spreadsheet and UDF updates values correctly in the relevant cells
    b) he opens second spreadsheet and the cells with UDF contain nonsensical values
    c) he does copy and paste from cells on the second spreadsheet (which forces the recalculate) and the cells with UDF now show the correct values
    BUT
    d) on the first spreadsheet, the cells with UDF have now been updated with non sensical values

    QUESTION: Surely the Application.Caller method should be aware of which is the active spreadsheet, and therefore NOT affect the second spreadsheet? Or am I doing something wrong?

    Any help greatly appreciated!

    Thanks

    My function code is below
    Please Login or Register  to view this content.
    Last edited by maurocam; 04-04-2011 at 09:19 AM. Reason: add code tags

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    Application.Caller is aware of which sheet/book it is referring to, but Sheets isn't as you haven't qualified it with a workbook (so it defaults to the active workbook).

    Note: I have added code tags to your post - can you please ensure you use them in future when posting code as it makes it much easier to read.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    04-04-2011
    Location
    Cambridge, UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    Uhm, not sure I understand. If I force a recalculate on the Active workbook - which works correctly - why does the UDF also recalculate on the non-Active workbook?

    Quote Originally Posted by romperstomper View Post
    Application.Caller is aware of which sheet/book it is referring to, but Sheets isn't as you haven't qualified it with a workbook (so it defaults to the active workbook).

    Note: I have added code tags to your post - can you please ensure you use them in future when posting code as it makes it much easier to read.
    Thanks, you beat me to it as I was just reading the Forum Rules

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    Because your function is volatile.

    Assuming your code is in the workbook itself, rather than a separate workbook, you would use something like:
    Please Login or Register  to view this content.
    Note: it's not a good idea to give a variable a name like Range, so I changed that too.

  5. #5
    Registered User
    Join Date
    04-04-2011
    Location
    Cambridge, UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    Well, the explanation made sense, but unfortunately it hasn't worked. I modified the UDF in both of the workbooks, but the updates across workbooks are still occurring. Any other suggestions?

    P.S. The UDF is located in a module, rather than in "This Workbook". Is that correct?
    Last edited by romperstomper; 04-04-2011 at 07:32 AM. Reason: remove quote

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    I don't see how that is possible. Please copy and paste the exact code you are now using.

  7. #7
    Registered User
    Join Date
    04-04-2011
    Location
    Cambridge, UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    Quote Originally Posted by romperstomper View Post
    I don't see how that is possible. Please copy and paste the exact code you are now using.
    Bizarre. I double-checked my code, and I had not updated the name of the range variable. Changing the function declaration to the following now makes it work correctly.

    Please Login or Register  to view this content.
    Why does a variable name have this effect?

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    The code refers to rngInput - if you haven't changed the input variable name, then rngInput has no value, and you should get an error.

  9. #9
    Registered User
    Join Date
    04-04-2011
    Location
    Cambridge, UK
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    Quote Originally Posted by romperstomper View Post
    The code refers to rngInput - if you haven't changed the input variable name, then rngInput has no value, and you should get an error.
    In order to test the "With ThisWorkbook" I had not made the changes to the function signature or the variable name. So that is why I had no errors.

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: UDF Function to calculate Sheet Offset not calculating correctly

    Then it should have worked. They just need to match.
    Anyway, if it's working now, please mark this as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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