Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
How can I change the tab but keep the row and column constant when | Excel Worksheet Functions | |||
Change a constant from VB | Excel Programming | |||
How to replace defined portion of string | Excel Programming | |||
How will I do Excel user-defined function to extract letters from string | Excel Programming |