-1

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?

eagleye
  • 582
  • 1
  • 8
  • 12
  • 3
    In the immediate window, try using `Range(NmdRng).activate`, and in case the range is really working, it will be selected in Excel. I'd guess the problem is because VBA is not reaching this range (wrong name, probably). – Tiago Cardoso Sep 10 '11 at 12:12

4 Answers4

0

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
Garrett Hyde
  • 5,409
  • 8
  • 49
  • 55
deasa
  • 606
  • 9
  • 24
0

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
Qbik
  • 5,885
  • 14
  • 62
  • 93
0

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.

Gaijinhunter
  • 14,587
  • 4
  • 51
  • 57
  • Setting a 'variable with a name as the function' is how you specify the return value of a VBA function. – Eddy Sep 10 '11 at 17:13
  • I know that, I'm just saying I've never really seen the actual Set keyword used in the return line. I suppose it's optional? – Gaijinhunter Sep 11 '11 at 00:12
  • In this it's required. Not doing so will throw an error since you are returning a reference to an (range) object. (See http://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba) – Eddy Sep 11 '11 at 10:59
0

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.

JMax
  • 26,109
  • 12
  • 69
  • 88