Change Defined Name Constant String
 

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?

Change Defined Name Constant String
 

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

"Ronster" wrote in message
ups.com...
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?

Change Defined Name Constant String
 

' 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" wrote in message
ups.com...
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?

Change Defined Name Constant String
 

Peter T wrote:
' 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



Both seem to work great. Thanks!
"Ronster" wrote in message
ups.com...
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?