Setting named range in VBA- how to set as formula/reference instead of text string?

[Keith R]

I want to toggle between two ranges (i.e. reset the same named range to
either option, depending on user input).

The named ranges are used by graphs, and show either (option 1) a baseline
data point and 12 months of current data, or
(option 2) 24 months of data.

I tried doing this change on the worksheet and ran into big problems, and
decided I should also look at setting these in VBA and
see if it would be easier. The problem is that when I go back into the
named range, it shows the correct "formula" but
it is returned as a string instead of a reference, e.g.
="OH2!$C$49,OH2!$C$20:$C$31"
instead of
=OH2!$C$49,OH2!$C$20:$C$31

Any/all help greatly appreciated,
Keith R
XL97

Here's what I've got so far:
----------------------------------------------------------------------------
-----------------------------
"Active" is a named range =VLOOKUP(Info!$B$2,Info!$B$4:$C$12,2), and works
properly-
it returns a sheet name, for example, OH2, GMC2, etc.
----------------------------------------------------------------------------
-----------------------------
Public Function ChangeGraph(GType As Range, ActiveSite As Range)
'function can't be put in put in the same cell as Gtype, which is fine

If GType.Value = 1 Then
ActiveWorkbook.Names.Add Name:="Graph01_A", _
RefersTo:=Evaluate(Names("active").Value) & "!$C$49," & _
Evaluate(Names("active").Value) & "!$C$20:$C$31"
ActiveWorkbook.Names.Add Name:="GraphsXAxis", _
RefersTo:=Evaluate(Names("active").Value) & "!$A$7," & _
Evaluate(Names("active").Value) & "!$A$20:$A$31"

ElseIf GType.Value = 2 Then
ActiveWorkbook.Names.Add Name:="Graph01_A", _
RefersTo:=Evaluate(Names("active").Value) & "!$C$8:$C$31"
ActiveWorkbook.Names.Add Name:="GraphsXAxis", _
RefersTo:=Evaluate(Names("active").Value) & "!$A$8:$A$31"

Else
MsgBox "Only values of 1 or 2 can be accepted in ChangeGraph
Function", , "Error: Value Out Of Range"
End If

'I don't set Gtype back to a value so it returns zero- which is
'fine because I just use it to trigger the named range change
'while avoiding the volatile issue

End Function

 

[steve]

Keith,

Check this code. Note the 2 = signs...

ActiveWorkbook.Names.Add Name:="myrange", RefersTo:= _
"=OH2!$C$49,OH2!$C$20:$C$31"

In cases like this - I like to build the string from pieces of code and
than substitute the string in the code (easier to trouble shoot)


ActiveWorkbook.Names.Add Name:="myrange", RefersTo:= mystring

if you decide to do this, make sure your string starts with an = sign.

steve