Hi Jon,
I should have posted a more realistic example for testing, please see my
followup to Bob. I cannot get Evaluate() to work at all, the
[name-without-quotes] approach works but only if the workbook containing the
name is Active.
Regards,
Peter T
"Jon Peltier" wrote in message
...
The square brackets are a shorthand which should not be used in regular
code. They essentially tell the compiler the evaluate the enclosed
expression. If all you enclose is a cell address or a name, without
further
referencing, the evaluation will be assumed to refer to the active sheet.
Notice in Bob's response that he uses Application.Evaluate, and he fully
references the name by workbook.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
wrote in message
...
Hi
This syntax seems to work
With Workbooks("Test2.xls").Names
.Add "abc", "=124"
v = .Item("abc").Value
.Item("abc").Delete
End With
but this one does not
With Workbooks("Test2.xls").Names
.Add "abc", "=124"
v = [abc]
.Item("abc").Delete
End With
i don't know why not. Anyone?
regards
Paul
On Mar 4, 11:45 am, "Peter T" <peter_t@discussions wrote:
Anyone know the syntax to evaluate a Name in a Workbook that's not the
activeworkbook.
For example, in the following how to return v = 123 if ThisWorkbook is
NOT
the active workbook (without using cells).
Sub EvalNameTest()
Dim v
With ThisWorkbook.Names
.Add "abc", "=123"
v = [abc]
.Item("abc").Delete
End With
If IsError(v) Then
v = CStr(v)
End If
MsgBox v, , ActiveWorkbook.Name
End Sub
TIA, Peter T