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

[JenniferMurphy]

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.

Public Function MyFun()
  . . .
Const MaxValue as String = "MaxValue"   'Name of cell in Sheet1
Dim MaxVal as Integer
MaxVal = Range(MaxValue).Value
  . . .
End Function

Thanks

 

[Scott T]

Public Function MyFun()
'if your range is named maxvalue
MyFun = Range("MaxValue").Value
End Function

You also need to include what your function equals.

 

[JenniferMurphy]

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

 

[JenniferMurphy]

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?

'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

 

[Scott T]

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.

'instead of 
funname=Range("S7")+1

'you would use 
funname=Range("MaxValue")+1

You can do this to instead of using gotto

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

 

[JenniferMurphy]

You can do this to instead of using gotto

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,

If Range("MaxValue").Exists then . . .

or

If IsError(Range("MaxValue")) then . . .

Thanks

 

[JenniferMurphy]

OK. Here's my test 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?

 

[Scott T]

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.

 

[pgc01]

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.

 

[JenniferMurphy]

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?