Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

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

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

    Suppose in Sheet1, I have given the cell C4 the name "MaxValue" and MaxValue (C4) contains the value "100".

    Is there a way that a UDF called from any cell in Sheet1 can access the value in C4 using the name MaxValue without passing it as an argument?

    I am working on a UDF that needs a bunch of values (8-10 and growing). Passing them as parameters is getting tedious and error-prone as they can easily get out of order. I would prefer that the UDF be able to access the values in the named cells using the cell names.

    This is what I tried, but it gets a Value error.
    Code:
    Public Function MyFun()
      . . .
    Const MaxValue as String = "MaxValue"   'Name of cell in Sheet1
    Dim MaxVal as Integer
    MaxVal = Range(MaxValue).Value
      . . .
    End Function
    Thanks
    Using Office 2007 Pro on Win XP Pro

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,524
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

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

    Code:
    Public Function MyFun()
    'if your range is named maxvalue
    MyFun = Range("MaxValue").Value
    End Function
    You also need to include what your function equals.
    Last edited by Scott T; May 1st, 2019 at 09:43 AM.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,122
    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 Scott T View Post
    Code:
    Public Function MyFun()
    'if your range is named maxvalue
    MyFun = Range("MaxValue").Value
    End Function
    You also need to include what your function equals.
    I thought I did exactly what you did, but mine didn't work. I just tried it again and now it does. I guess I'm losing it.

    Thanks
    Using Office 2007 Pro on Win XP Pro

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

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

    Is there a way that I can test whether the named range exists before trying to access the value?

    I came up with this, which works, but it is ugly. Is there a simpler, more elegant way to test if the name exists?

    Code:
    'Test function to get accessing named variables working
    Public Function MyFun()
    
    Const MaxValueName As String = "MaxValue"
    
    Dim MaxValue
    MaxValue = 1
    
    On Error GoTo NotDefined
    MaxValue = Range(MaxValueName).Value
    GoTo IsDefined
    
    NotDefined:
    MsgBox "Name does not exist"
    Exit Function
    
    IsDefined:
    MyFun = MaxValue + 1
    
    End Function
    Using Office 2007 Pro on Win XP Pro

  5. #5
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,524
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

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

    Unless you need to for other reasons you do not need to put the range name into a variable. You can just use it in place of the cell references.

    Code:
    'instead of 
    funname=Range("S7")+1
    
    'you would use 
    funname=Range("MaxValue")+1

    You can do this to instead of using gotto
    Code:
    Public Function MyFun()
    
    Dim rngck As Range
    On Error Resume Next
    Set rngck = Range("MaxValue")
    On Error GoTo 0
    If rngck Is Nothing Then
         'put code here for if the named range does not exist
         MsgBox "Name does not exist"
          Exit Function
    Else
         'put code here for if named range exists
         MyFun = Range("MaxValue") + 1
    End If
    End Function
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  6. #6
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,122
    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 Scott T View Post
    You can do this to instead of using gotto
    Code:
    Public Function MyFun()
    
    Dim rngck As Range
    On Error Resume Next
    Set rngck = Range("MaxValue")
    On Error GoTo 0
    If rngck Is Nothing Then
         'put code here for if the named range does not exist
         MsgBox "Name does not exist"
          Exit Function
    Else
         'put code here for if named range exists
         MyFun = Range("MaxValue") + 1
    End If
    End Function
    Ok, I'll give that a try. I was hoping for something like,
    Code:
    If Range("MaxValue").Exists then . . .
    
    or
    
    If IsError(Range("MaxValue")) then . . .
    Thanks
    Using Office 2007 Pro on Win XP Pro

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

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

    OK. Here's my test code:

    Code:
    'Test function to get access to named variables working
    Public Function MyFun()
    
    Const MaxValueName As String = "MaxValue"
    Dim RangeCheck As Range
    Dim MaxValue
    MaxValue = 1
    
    On Error Resume Next
    Set RangeCheck = Range(MaxValueName)
    On Error GoTo 0
    
    If RangeCheck Is Nothing Then
      MsgBox "Name does not exist"
      MyFun = CVErr(xlErrValue)
      Exit Function
    End If
    
    MaxValue = Range(MaxValueName).Value
    
    MyFun = MaxValue + 1
    
    End Function
    One question: What's the difference between RangeCheck and MaxValue? I know that RangeCheck is type Range, whereas MaxValue is type Variant, but when I step through the code, they both get the contents of the named cell, which is "100". Can I use RangeCheck as a numeric variable?
    Using Office 2007 Pro on Win XP Pro

  8. #8
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,524
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

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

    When the range name does not exist the RangeCheck will be nothing. The IF statement then checks if RangeCheck is nothing. This lets the it work regardless of what is in the range or if the range is a single cell or many cells.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  9. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,686
    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
    Suppose in Sheet1, I have given the cell C4 the name "MaxValue" and MaxValue (C4) contains the value "100".

    Is there a way that a UDF called from any cell in Sheet1 can access the value in C4 using the name MaxValue without passing it as an argument?
    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.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  10. #10
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,122
    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.
    I don't understand what you mean by "monitor". Does a UDF monitor the value of a passed parameter?

    In case the value changes the udf result may display a wrong result until refreshed.
    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?
    Using Office 2007 Pro on Win XP Pro

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
  •