Evaluate a Name in Non-active WB
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
|