Defined name

[JasonC-VBA]

Is there an easy way to determine if a cell / formula contains a defined name?

 

[Weaver]

Are you using vb or just working within the worksheet?

If vb, then try

debug.print activecell.name.name

Having to use .name property twice threw me for a while.

edit: This only works if the defined name refers to the activecell alone, so isn't going to be much use

 

[JasonC-VBA]

I’m using VB.
I’m copying a worksheet to a new book. When the new book/sheet is created it copies over a lot of defined names from the original book. I need to replace the defined names in the new book with the values the name refers to but leave several other formulas without defined names unchanged.

 

[Rick Rothstein]

Are you looking for code similiar to this?

Sub ReplaceNamesWithTheirAddress()
  Dim N As Name
  For Each N In Names
    Columns("A").Replace N.Name, N.RefersToRange.Address(0, 0)
  Next
End Sub

 

[shg]

That code would cause problems if any of the names were relative, or if short names appeared in longer names, or if the names referred to constants, would it not?