Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Can a UDF access the value in a named worksheet cell?

  1. #11
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,271
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    Quote Originally Posted by JenniferMurphy View Post
    I don't understand what you mean by "monitor". Does a UDF monitor the value of a passed parameter?



    I don't understand what you mean by "refreshed".

    When a UDF is called, control remain with the UDF until it exits. However a value gets into a UDF, whether passed as a parameter or extracted by the UDF from the sheet, once it has it, it will not change until the next call, no?
    I think what pgc is trying to say is

    Suppose you have these two functions (notice in the second function the named range is passed as an argument)
    Code:
    Function FuncV1()
        FuncV1 = Range("MaxValue").Value
    End Function
    
    Function FuncV2(r As Range)
        FuncV2 = r.Value
    End Function
    Excel

    A
    B
    C
    D
    1
    MaxValue
    FuncV1
    FuncV2
    2
    100
    100
    100


    Range A2 named as MaxValue

    Formula in C2
    =FuncV1()

    Formula in D2
    =FuncV2(MaxValue)

    Then change the value in A2 to 200. You see...

    A
    B
    C
    D
    1
    MaxValue
    FuncV1
    FuncV2
    2
    200
    100
    200


    The value in C2 remains (???) - this means the function doesn't monitor the named range.

    To get the proper value you need to re-enter the formula in C2 (refresh)

    Hope i made myself clear

    M.

  2. #12
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,708
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    Hi

    I see that Marcelo has already explained it, but since I had finished writing this maybe it will complement the help.

    Quote Originally Posted by JenniferMurphy View Post
    I don't understand what you mean by "monitor". Does a UDF monitor the value of a passed parameter?
    Yes, or, more exactly, excel does.

    When you use a formula, for ex. C1 = SUM(A1, B1), you expect that when you change A1 or B1 the formula in C1 is reevaluated and the value in C1 is updated.
    That's the point in excel, you write formulas and when any of its precedents changes value the formula value is updated.
    When you define a udf with a parameter, like

    Code:
    Public Function MyFun(lMaxValue as Long) as Long
    You'll use in a cell something like =MyFun(A1) and excel knows that if A1 changes value this formula should be reevaluated

    Let's say now that you use

    Code:
    Public Function MyFun() as Long
    and you use the value of A1 in the code of the udf.

    You'll use in a cell something like =MyFun() and if you change the value of A1 excel will not know that the formula should be reevaluated.

    This means that the only way you have to be sure that a formula that uses a udf like this one is displaying the correct result is by refreshing it, meaning recalculating the worksheet or the workbook, or confirming the formula again.

    This is why I said that it is usually considered bad practice to use an external value in a udf that is not included in the parameters. You cannot trust the result unless after a recaclulation.

    In this specific case, you are using the named range MaxValueName reading it inside your code, not passing it as a parameter.
    This means that if you change it's value all the formulas that use the udf may display an incorrect value until they are recalculated.

    Hope it helps.
    Last edited by pgc01; May 1st, 2019 at 03:07 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #13
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,124
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    Quote Originally Posted by Marcelo Branco View Post
    I think what pgc is trying to say is

    . . .

    The value in C2 remains (???) - this means the function doesn't monitor the named range.

    To get the proper value you need to re-enter the formula in C2 (refresh)

    Hope i made myself clear

    M.
    OK, now I get it, thanks. I tested it and it works as you describe. However, I added an Application.Volatile statement to the UDF and now it does update automatically.

    Any problems with that?
    Using Office 2007 Pro on Win XP Pro

  4. #14
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,124
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    Quote Originally Posted by pgc01 View Post
    Hi

    I see that Marcelo has already explained it, but since I had finished writing this maybe it will complement the help.

    . . . snip . . .

    Hope it helps.
    Got it, thanks.
    Using Office 2007 Pro on Win XP Pro

  5. #15
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,708
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    Quote Originally Posted by JenniferMurphy View Post
    OK, now I get it, thanks. I tested it and it works as you describe. However, I added an Application.Volatile statement to the UDF and now it does update automatically. Any problems with that?
    Well, the usual problems with volatile functions.
    I'd point out 2:
    . performance - They're very inefficient, that's why they are avoided.
    . dependence logic: hidden

    Performance:
    As you may know, a volatile function is recalculated everytime any value in the workbook changes.
    If you use it in many places it may render the workbook unusable.

    Ex. you have a table with 10000 rows and 2 columns A and B. Each row has just a value in column A and in column B a function that depends only on the cell to the left.
    In case of a non-volatile function, you change A123 and B123 is automatically recalculated.
    In case of a volatile function, you change A123 and all the 10000 cells in column B are recalculated.

    This said, if you use just a few volatile functions, you may not notice any performance problem.

    Dependence logic
    When you look at a formula you don't just see how the value is calculated, you also see what it depends on.
    This is important when you want to understand what's happening, especially when you don't know the workbook (or you forgot how it works).
    For ex., I see in a cell =A1+MIN(F2,F5), I know that the value of the cell depends on A1, F2 and F5. If I change F2 and the value changes, I understand why.
    If I have volatile functions like =MyFun(), if I change A1 I have no idea which cells values will be affected.
    If you have tried to understand a workbook that you did not build you understand what this means.

    Conclusion: I'm not saying don't write volatile udfs. It's just important to understand how they will impact the workbook.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #16
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,124
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    Quote Originally Posted by pgc01 View Post
    Just a reminder

    It is usually bad practice to use an external value not passed as a parameter to the udf.
    The udf will not monitor the value.
    In case the value changes the udf result may display a wrong result until refreshed.
    I just discovered another reason in support of your warning.

    The UDF I am working on needs to access 6-7 rows of data. They all start and stop on the same columns, but occupy different rows. All but one of them are static (absolute). That is, they are defined with double absolute addresses, such as $D$10:$U$10. These are all "reference" rows. But one of the rows has absolute columns, but a relative row. This is the row that is compared to the reference rows. The function is called from a cell on that row.

    Because I didn't fully understand your warning, I went ahead and coded the UDF to access all of these rows internally by their names rather than passing them as parameters. As you predicted, I found out that changing values in those rows did not cause the UDF to be re-executed, so the results were incorrect. While debugging, I got into the habit of forcing the sheet to recalculate using
    Alt+Ctrl+Shift+F9.

    Today, I spent about an hour trying to find a bug that turned out to be the other reason not to access data not passed as an argument. I had changed a value in one of the static rows and wanted to see the results, so I did the ACS+F9. I got a data error. After a lot of head scratching, I realized that the named range with the variable row looked to the UDF as if it were on the row where the cursor was and not where the call was.

    So, lesson learned. Now I have to go put the code back to pass everything as a parameter.

    Thanks for the tip!
    Using Office 2007 Pro on Win XP Pro

  7. #17
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,708
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    I'm glad it helped.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #18
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,124
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    Quote Originally Posted by pgc01 View Post
    Just a reminder

    It is usually bad practice to use an external value not passed as a parameter to the udf.
    The udf will not monitor the value.
    In case the value changes the udf result may display a wrong result until refreshed.
    As a result of this warning, which I have since discovered is entirely valid, I am in the process of converting the way data is passed to the UDF, but have run into a bizarre (to me) situation that I cannot understand.

    This UDF currently needs access to 10 ranges in the calling sheet and there may be more in the future. Previously, in an effort to avoid a long, complicated, error-prone calling syntax, I decided to require the calling sheet to give the ranges standard names that the UDF could then access by those names without passing any parameters. I am now going back to the longer calling syntax with the calling sheet needing to explicitly pass each range to the UDF (10+ parameters).

    I am about half way through that process. I have 6 of the 10 ranges now being passed as range parameters. The other 4 are still being accessed by the UDF using standard range names. If I run test calls, the UDF works correctly. But if I put a breakpoint (F9) on the Function statement of the UDF, the UDF gets called 4 times and returns the wrong answer. It also sometimes gets a circular reference error. If I remove the breakpoint, everything works properly.

    Why would a breakpoint cause the UDF to operate differently? I am not doing anything when the break occurs other than hitting resume (F5).
    Using Office 2007 Pro on Win XP Pro

  9. #19
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,708
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can a UDF access the value in a named worksheet cell?

    Sorry, I cannot think of a reason for that to happen.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Tags for this Thread

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
  •