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
"(E-Mail Removed)" 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?
>
>
|
Similar Threads
