Change Defined Name Constant String

[Ronster]

I'm trying to change the defined name string constant using VBA. Is it
possible? Here is my code:

Sub ChangeNameString()

Dim Next_String As String

' this works ok, defined name shows "First_String" (with quotes) (not a
variable)
ActiveWorkbook.Names.Add Name:="MyString", _
RefersToR1C1:="=""First_String"""

Next_String = "Second_String"

' doesn't work when attempting to change name with Next_String varible
(no quotes)
ActiveWorkbook.Names.Add Name:="MyString", _
RefersToR1C1:="=" & Next_String

End Sub

I've tried several variations but nothing works. Any ideas?

 

[Rob Bovey]

The line of code where you use the string variable to create the name
should look like this:

ActiveWorkbook.Names.Add Name:="MyString", _
RefersToR1C1:="=""" & Next_String & """"

Note the embedded double quotes that are required for a named constant of
type String.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Peter T]

' doesn't work when attempting to change name with Next_String varible

(no quotes)

I assume you mean you can define the name OK but you can't use it in the way
you intend, to return a string in a cell formula.

That's because you've defined a name that expects to refer to another Name
named Second_String, which presumably doesn't exist. Try instead -

Next_String = "Second_String"

ActiveWorkbook.Names.Add Name:="MyString", _
RefersToR1C1:="=" & Chr(34) & Next_String & Chr(34)

Regards,
Peter T

 

[Ronster]

I assume you mean you can define the name OK but you can't use it in the way
you intend, to return a string in a cell formula.

That's because you've defined a name that expects to refer to another Name
named Second_String, which presumably doesn't exist. Try instead -

Next_String = "Second_String"

ActiveWorkbook.Names.Add Name:="MyString", _
RefersToR1C1:="=" & Chr(34) & Next_String & Chr(34)

Regards,
Peter T

Both seem to work great. Thanks!