Evaluating Names

[Adrian]

I have large system of workbooks and sheets. At start up a
macro reads a set of configuring constants from a sheet
and installs them as names. The code that does that is
below.
Trouble is, this installs the address of the cell instead
of the value in the cell. On worksheets this doesn't
matter, but in other macros its a big problem.
Please advise how to make the Name contain the value in
the cell instead of the address of the cell.

Sub SetConst()
Set rng = Workbooks("macros.xls").Names
("constants").RefersToRange
For Each cell In rng
Workbooks("macros.xls").Names.Add Name:=cell,
RefersTo:=cell.Offset(0, 1)
Next
End Sub

 

[Jean-Yves]

Hi Adrian
Consider declaring your variable, it will help as well with the auto
fill/suggestion.
avoid something like Cell. Use cl instead
Dim cl as range
for your problem
Name:=cell.value
Regards,

Jean-Yves

 

[Bob Phillips]

Hi Adrian,

Nice approach!

Try this code

With Workbooks("macros.xls")
Set rng = .Names("constants").RefersToRange
For Each cell In rng
If IsNumeric(cell.Offset(0, 1).Value) Then
.Names.Add Name:=cell, RefersTo:="=" & cell.Offset(0,
1).Value
Else
.Names.Add Name:=cell, RefersTo:="=""" & cell.Offset(0,
1).Value & """"
End If
Next
End With

it caters for numbers and strings.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Adrian]

Thanks.
I actually used refersto:=cell.offset(0,1).value
which is what I think you meant.

 

[Bob Phillips]

watch the wrap-around on this code

This
.Names.Add Name:=cell, RefersTo:="=" & cell.Offset(0,
1).Value

and this
.Names.Add Name:=cell, RefersTo:="=""" & cell.Offset(0,
1).Value & """"

should both be sibgle lines.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Adrian]

Magic! You actually anticipated my next question!
One of my names was "year" which had the value "03" and
was being reduced to 3.
You may be able to tell, I'm coming from a background of
years of XLM programming, and I'm gradually rewriting
everything in VBA since I believe the next version of
Excel (te one after Excel 2003) will no longer supoort
XLM. I have two years.

Old dog, new tricks...

Thanks for helping.

 

[Tom Ogilvy]

Cell has no special meaning in Excel vba, so there is no reason to avoid
using it as a variable name.

 

[Bob Phillips]

Good testing found that<vbg>.

We old dogs keep a-coming I guess.

Haven't heard about XLM not being supported, but it's a good move to move
on, VBA is far more flexible than XLM.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jean-Yves]

Yes, Tom you are right.
But ist name can lead to confusion for beginner, especially if they don't
delcare variables.
Just a way IMHO to avoid problems.
Regards

Jean-Yves