It's doesn't work with EXECL 2003 (it's OK with 97) why

[Guest]

hi

This function doesn't work but in the immediate windows that works :

Private Function Range_Table(Table As String) As Range
On Error GoTo TableGen
Table = Application.Substitute(Table, " ", "")
Set Range_Table = Names(Table).RefersToRange
Exit Function
TableGen:
Set Range_Table = ThisWorkbook.Names(Table).RefersToRange
Resume Next
End Function

Do you have a answer,

Thanks

 

[Niek Otten]

You can't change anything in a worksheet directly from a function that is
called from a worksheet. Functions can only replace their call with a value.
I don't think it works (from a function) in Excel97 either.

 

[Tom Ogilvy]

I don't have xl2003 to test it on, so xl2003 might react differently to the
use of Resume next used this way.

Try it this way

Private Function Range_Table(Table As String) As Range
Dim rng as Range
Table = Application.Substitute(Table, " ", "")
On Error Resume Next
Set Rrng = Names(Table).RefersToRange
if rng is nothing then
Set rng = ThisWorkbook.Names(Table).RefersToRange
end if
On Error goto 0
Set Range_Table = rng
End Function

That said, I don't see the point of checking without ThisWorkbook and then
Checking with ThisWorkbook - Although you might have a reason.

 

[Guest]

Thanks for your advice but it doesn't work either

 

[Guest]

Tx for your advice