Maybe this

Sub EvalNameTest()
Dim v

With Workbooks("Book2")
.Names.Add "abc", "=124"
v = Application.Evaluate(.Names("abc").RefersTo)
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
End If


MsgBox v, , ActiveWorkbook.Name
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Peter T" <peter_t@discussions wrote in message
...
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

Hi Bob,

Yes that does work, but unfortunately it seems my example is not adequate.
Would you care to have a go with this:

Sub EvalNameTest2()
Dim s As String
Dim v

s = ThisWorkbook.Name ' try as active & non-active

With Workbooks(s)
.Names.Add "abc", "=Documents(1)"
'v = Application.Evaluate(.Names("abc").RefersTo) 'error 2015
v = [abc]
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
ElseIf IsArray(v) Then
v = v(LBound(v)) ' first workbook
End If

MsgBox v, , ActiveWorkbook.Name
End Sub

Regards,
Peter T

"Bob Phillips" wrote in message
...
Maybe this

Sub EvalNameTest()
Dim v

With Workbooks("Book2")
.Names.Add "abc", "=124"
v = Application.Evaluate(.Names("abc").RefersTo)
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
End If


MsgBox v, , ActiveWorkbook.Name
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peter T" <peter_t@discussions wrote in message
...
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

Documents() is one of those long lost XLM functions that only exists in
names. So I thought ExecuteExcel4Macro might work, but we have to strip off
the = sign.

Application.ExecuteExcel4Macro(Mid(Workbooks("Book 1").Names("abc").RefersTo,
2))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,

Yes that does work, but unfortunately it seems my example is not adequate.
Would you care to have a go with this:

Sub EvalNameTest2()
Dim s As String
Dim v

s = ThisWorkbook.Name ' try as active & non-active

With Workbooks(s)
.Names.Add "abc", "=Documents(1)"
'v = Application.Evaluate(.Names("abc").RefersTo) 'error 2015
v = [abc]
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
ElseIf IsArray(v) Then
v = v(LBound(v)) ' first workbook
End If

MsgBox v, , ActiveWorkbook.Name
End Sub

Regards,
Peter T

"Bob Phillips" wrote in message
...
Maybe this

Sub EvalNameTest()
Dim v

With Workbooks("Book2")
.Names.Add "abc", "=124"
v = Application.Evaluate(.Names("abc").RefersTo)
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
End If


MsgBox v, , ActiveWorkbook.Name
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peter T" <peter_t@discussions wrote in message
...
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

Thanks for that but unfortunately where an XLM is expected to return an
array ExecuteExcel4Macro only returns the first element.

Regards,
Peter T

"Jon Peltier" wrote in message
...
Documents() is one of those long lost XLM functions that only exists in
names. So I thought ExecuteExcel4Macro might work, but we have to strip
off
the = sign.


Application.ExecuteExcel4Macro(Mid(Workbooks("Book 1").Names("abc").RefersTo,
2))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,

Yes that does work, but unfortunately it seems my example is not
adequate.
Would you care to have a go with this:

Sub EvalNameTest2()
Dim s As String
Dim v

s = ThisWorkbook.Name ' try as active & non-active

With Workbooks(s)
.Names.Add "abc", "=Documents(1)"
'v = Application.Evaluate(.Names("abc").RefersTo) 'error 2015
v = [abc]
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
ElseIf IsArray(v) Then
v = v(LBound(v)) ' first workbook
End If

MsgBox v, , ActiveWorkbook.Name
End Sub

Regards,
Peter T

"Bob Phillips" wrote in message
...
Maybe this

Sub EvalNameTest()
Dim v

With Workbooks("Book2")
.Names.Add "abc", "=124"
v = Application.Evaluate(.Names("abc").RefersTo)
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
End If


MsgBox v, , ActiveWorkbook.Name
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peter T" <peter_t@discussions wrote in message
...
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

Yeah, I got hung up on that one. I could easily get the array in the
worksheet, but not via VBA.

How about temporarily dumping it into a worksheet (using .FormulaArray), and
using a variant array to get that into VBA?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
Thanks for that but unfortunately where an XLM is expected to return an
array ExecuteExcel4Macro only returns the first element.

Regards,
Peter T

"Jon Peltier" wrote in message
...
Documents() is one of those long lost XLM functions that only exists in
names. So I thought ExecuteExcel4Macro might work, but we have to strip
off
the = sign.


Application.ExecuteExcel4Macro(Mid(Workbooks("Book 1").Names("abc").RefersTo,
2))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,

Yes that does work, but unfortunately it seems my example is not
adequate.
Would you care to have a go with this:

Sub EvalNameTest2()
Dim s As String
Dim v

s = ThisWorkbook.Name ' try as active & non-active

With Workbooks(s)
.Names.Add "abc", "=Documents(1)"
'v = Application.Evaluate(.Names("abc").RefersTo) 'error 2015
v = [abc]
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
ElseIf IsArray(v) Then
v = v(LBound(v)) ' first workbook
End If

MsgBox v, , ActiveWorkbook.Name
End Sub

Regards,
Peter T

"Bob Phillips" wrote in message
...
Maybe this

Sub EvalNameTest()
Dim v

With Workbooks("Book2")
.Names.Add "abc", "=124"
v = Application.Evaluate(.Names("abc").RefersTo)
.Names("abc").Delete
End With

If IsError(v) Then
v = CStr(v)
End If


MsgBox v, , ActiveWorkbook.Name
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peter T" <peter_t@discussions wrote in message
...
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