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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,151
Office Version
365, 2016, 2007
Platform
Windows
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
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,564
Office Version
365, 2016
Platform
Windows
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:

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,151
Office Version
365, 2016, 2007
Platform
Windows
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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,151
Office Version
365, 2016, 2007
Platform
Windows
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
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,564
Office Version
365, 2016
Platform
Windows
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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,151
Office Version
365, 2016, 2007
Platform
Windows
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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,151
Office Version
365, 2016, 2007
Platform
Windows
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?
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,564
Office Version
365, 2016
Platform
Windows
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,735
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

Well-known Member
Joined
Jul 23, 2011
Messages
1,151
Office Version
365, 2016, 2007
Platform
Windows
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?
 

Forum statistics

Threads
1,078,134
Messages
5,338,428
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top