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

I forgot to mention, although the RefersTo string less the "=" would of
course return 123 in the example. Knowing the Refersto will not help for my
particular named formula.

Regards,
Peter T

"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
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

Your example that works (even if Test2.xls is not active) the .Value
property is in effect the RefersTo string, "=123".

Although it works in the sense it does not error, unfortunately it's not the
solution. But thanks for looking.

Regards,
Peter T

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

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

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

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

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

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

Not sure, but does this work?

With ThisWorkbook.Names
'Return String "=123"
v = .Item("abc")
'Return Value 123
v = Evaluate(v)
End With

--
HTH :)
Dana DeLouis


"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 Dana,

It does work but only because I gave an inadequate example for testing.
v = .Item("abc") returns "=123" as the default refersto property of the
name. Then the Evaluate removes the = and returns a number.

I am reposting the question at extreme length!

Thanks for looking,
Peter T

"Dana DeLouis" wrote in message
...
Not sure, but does this work?

With ThisWorkbook.Names
'Return String "=123"
v = .Item("abc")
'Return Value 123
v = Evaluate(v)
End With

--
HTH :)
Dana DeLouis


"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

In my OP I gave a simplifed example of a Named formula I wanted to
'Evaluate', even if the Name is in a Non-active workbook. I forgot people
might use lateral thinking to come up with a solution based on my contrived
post! In hindsight I should have given the bigger picture with a real
example. So here it is, hope you are sitting comfortably:

I want to return a list of all loaded addins. Referencing the addins
collection can be quite slow, particularly the first time in an Excel
session. I prefer to avoid referring to it (.Addins) at all unless really
necessary, and certainly not in a wb load event to check (say) if some addin
is loaded (when it probably is). Further, the Addins collection only
contains addins in the addin manager, and not addins which may have been
loaded by other means.

OK, that's the objective, return a list of all loaded addins and avoid use
of the Addins collection. As it happens there is an old XLM function which
does just that, very efficiently. It returns a horizontal array which, AFAIK
like all arrays returned from XLM's, can only be accessed in full when used
in a Named formula, =DOCUMENTS(2)

The normal way to use this XLM is like this:
define a name (ctrl-F3)
Name: LoadedAddins
Refersto: =DOCUMENTS(2)

Select cells in a row, at least as many as there may be potentially loaded
addins, array enter =LoadedAddins

I could just accept that, return values of cells in the array formula in
cells hidden in my own xla. So far pretty good but by no means perfect. In
particular the named formula in cells requires a total recalc to update, not
what I want to do in a wb load event (before I forget, actually in an OnTime
macro after all wb's have loaded).

A better way would be to evaluate the named formula without touching cells
and/or requiring a recalc. Indeed this is possible along the lines
previously posted:
vArr = [named_XLM_formula_without_quotes]

Except, sadly, the above only appears to work if the workbook containing the
name is the activeworkbook.

Here's a revised example for testing, if anyone's still looking !

Sub EvalXLMtest()
Dim bActive As Boolean
Dim s As String
Dim v

With ThisWorkbook
.Names.Add "XLAs", "=DOCUMENTS(2)"
v = [XLAs]
.Names("XLAs").Delete
bActive = .Names.Parent Is ActiveWorkbook
End With

If IsArray(v) Then
If Not bActive Then
s = "SUCCESS, array returned in NON active wb"
Else
s = "array returned but in active wb"
End If
s = s & vbCr & v(1) & " total " & UBound(v)
Else
If IsError(v) Then v = CStr(v)
s = v & vbCr & "Active = " & bActive
End If
MsgBox s

' Notes
' If while testing there are no loaded addins,
' change the 2 in =DOCUMENTS(2) to 1 or 3

' In normal use the name would not be temporary, but best
' to recreate & delete in testing to ensure the name does not
' get duplicated in another workbook

End Sub

An even better alternative to evaluating the name would to return the array
directly from the XLM. I'm pretty sure that's not possible but would be
delighted to be proved wrong!
Nothing came out of this related thread -
http://tinyurl.com/2bwack
(except privately to Jon, blame KeepItCool for those square brackets <g)

Regards,
Peter T

pmbthornton at gmail dot com