How to return a range object from a user defined function in vba

I have this piece of code in excel:

Private Function RelCell(NmdRng as String) as Range
Set RelCell = Range(NmdRng).Cells(1,1)
End Function

it gives the runtime error "91': object variable or with block variable not set.

I really don't know what is the problem with开发者_运维技巧 my function.. someone does?

---

I don't know if this is the problem but your are only setting the range and aren't returning anything from the function.

Try declaring a range variable with a different name as the function and return that.

---

Actually, you should be able to return a range from a UDF as described in this MSDN Thread.

Here is the code given by the MVP:

Function GetMeRange(rStartCell As Range, lRows As Long, iColumns As Integer) As Range
  Set GetMe = rStartCell.Resize(lRows, iColumns)  ' note the use of Set here since we are setting an object variable
End Function

(and it works)

Tiago's comment points out a very right thing, as you want to access a named range, it should be defined first.
You can try to set a breakpoint in your UDF and see if the Range(NmdRng) is defined.

---

Your named range already has a cell reference attached to it, so you shouldn't need to have the .Cells(1,1) at the end of it.

Using the .Range(nmdRng) property alone will return the range object you are looking for.

Try:

Private Function RelCell(NmdRng as String) as Range
Set RelCell = Range("NmdRng")
End Function

---

Please rewrite your code and test it as follows :

Private Function RelCell(NmdRng as String) as Range
Dim TestRange As Range

Set TestRange=Range(NmdRng)
TestRange.Activate 'I think that error will occur here because, NmdRng is somehow invalid
Set RelCell = TestRange.Cells(1,1)
End Function