ExcelBanter - Adding a Name Crashes Excel

Excel doesn't allow worksheet functions to change (or potentially change) the
worksheet environment because of the confusion this would create with formula
dependancies and calculation order. You are not allowed to create or change a
named range with a user-defined worksheet function because of the potential
for screwing things up.

You can call this from a worksheet cell:

Function myStarNight() As String
myStarNight = "Hi!"
End Function

If you want to create a named range I think you want this syntax:
ActiveWorkbook.Names.Add("Hi", "=$A$1:$A$5")
However, a user-defined function containing the above can only be run by a
macro.

The term "crash" usually means the workbook or application in some way gets
screwed up; e.g. freezes, closes for no reason or starts doing weird things.
Your post seems to imply that all that happens is that the function doesn't
return a result. Or did I get it wrong?

Greg

" wrote:

Hi! Whenever I call the "ActiveWorkbook.Names.Add" function, Excel
will simply panic, and quit altogether. This happens no matter what
the worksheet the name is directed to, or what the name or range
actually is (or if it already exists). The same goes for trying to
delete names.

However, this only happens when I run it from a worksheet function. If
I simply call it from the VBA editor, it works without issue.

Here is some example code that crashes things --

Public Function myStarNight(lookupStr As Variant) As String
Debug.Assert False
Dim hat As Variant
Set hat = ActiveWorkbook.Names.Add("Hi", "A1:A5")
Let myStarNight = "Hi!"
End Function

I insert this into a worksheet, and when I do that, the code simply
exits with no result. However, if I call it directly from VBA, it
works fine. Any insight?