These Named Ranges are acting so weird

[N1Martian]

I created this simple UDF to convert a dynamic named range to a fixed one.

Public Function D2F(namedrange As String) As String

    'Application.Volatile

    Dim rng As Range
    Set rng = Range(namedrange)
        
    ActiveWorkbook.Names.Add namedrange & "2", rng
     
    D2F = namedrange & "2"
    
End Function

The weird thing that's happening is when I run

= D2F("namedrange")

in VBA it works 100% perfectly. But when I run the exact same command from a cell, it dies at names.add line without any errors, it just goes blah.

Any help would be great.

 

[Rick Rothstein]

UDF's (functions used on a worksheet) have the same restrictions as worksheet functions do... they cannot make changes to anything other than the displayed value for the cell they are placed in. Scroll down to the text portion of the following webpage to see a listing of these restrictions...

http://www.ozgrid.com/VBA/Functions.htm

 

[N1Martian]

Wow its so simple, and yet so annoying. Thank you much.