Why doesn’t VBA Evaluate work on a closed Workbook reference

[Doc.AElstein]

Why doesn’t VBA Evaluate(“ “) work on the reference to a closed File

Hi
I have seen statements of “ Evaluate(“ “) doesn't work on the reference to a closed File “ , and have just experienced it myself.

I am asking here
_ 1 ) why
and
_2 ) If it really is true.

No rush on this one. I can live for now with that “It don’t work”.
Any insight into this could help me generally into a few other things I have been looking at recently.

To elaborate ( as briefly as I can ). Code Lines relate to the demo code below

This File: ( “myFileToClose.xlsm” ) IS CLOSED
https://app.box.com/s/8x5bugdnha4c2ybzrj9jlav503jn277y" onclick="window.open(this.href);return false;
I have CLOSED ( initially ). This is what Worksheet item 1 ( First Tab ) looks like:
Using Excel 2007 32 bit

Row\Col	A	B
1	HeadingCellA1	HeadingCellB1
2	CellA2	CellB2

|< < > >|_Sheet1_/___//

This file: ( “ClosedWorkbook.xlsm” ) IS OPEN
https://app.box.com/s/ffx8yquyouvox2ih2j5m5jwpex507t4c" onclick="window.open(this.href);return false;
This File has the demo code shown below and in the first cell of Worksheet “BracketWonk” has_...

Row\Col	A
1	SomeFink

|< < > >|_BracketWonk_/___//
_..in it. So this is OPEN

So based on the above screen shots and File names….( “myFileToClose.xlsm” is CLOSED, and is in the same Folder as “ClosedWorkbook.xlsm” which is OPEN )_.....this is what my demo code does:

Open Workbook referencing: ( just for comparison )
Rem 2 As far as my Open workbook is concerned I can use various versions of the Worksheets Range Property ** to “get at” , for example the first Cell in the open workbook, for example
= Ws1.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
= Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1")
= Evaluate("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1")

The third one here I suspect is changed “internally” by Excel to the second… and note:
_** the last two will work in a different Class Module to BracketWonk as Evalute somehow “puts” the Formula where it should go ??
_ I can get a Range Object or a string ( “SomeFink” ) returned back depending on how I declare the receiving variable.

Rem 3 Similar to Rem 2 , using instead the Application Range Object . As example , I can use the closed workbook reference on the open Workbook,
= Application.Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") and once again VBA will probably I suspect be changing that reference string “internally” to the ““open” copy” File reference, so it “sees” this
= Application.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")

Closed Workbook referencing
Rem 4 This is actually attempting the Closed workbook referencing. On the closed workbook, “myFileToClose.xlsm”
230 If I drop this string formula in a Cell,
"='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1"
then all is well and I get my closed workbook cell value of “HeadingCellA1” returned.

220 But this
Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1")
Does not work.

( That same non working code line will work if I open that closed workbook,
290 once again I suspect VBA to be changing “internally” to the ““open” copy” File reference )

_.................

The point of all that is…
_ Evaluate seems to know ** very well what particular cell it is referring to. One of the Evaluates functions, or its simplest definitions is, that it “does” what would be done by that Cell.
_ If we take this argument a little further, … I think when Excel sees a = it goes off and looks for an appropriate macro . Which one depends on what it recognizes after the = . This ties up with this code line possibly
260 = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!R1C1")
You see we don’t need the = . In fact it error if you include that – which makes sense , - As I understand it this “does” one of these macros, so it assumes you put something in the brackets to be done, so you do not need to include the = to be an indication of it to do something. I think this version of the ExecuteExcel4Macro( ) is called getting a reference, which all ties up.

So my suggestion is that maybe in principal Evaluate does work on the reference to a closed File, but does not get a chance as someone forgot not to disable for the case of an evaluate the code that changes a closed workbook reference string to an “open” workbook reference string.
I can see the reasoning for changing a closed workbook reference string to an “open” workbook reference string for Range(“ “) as there may be good reasons to prevent getting a Range Object from a Closed Workbook. But the Evaluate should be allowed to do its job of “performing a reference”

No rush on this one. I can live for now with that “It don’t work”.
I straightened out a few things in my mind about Range Objects, Properties and Referrences in posting anway :)
But any enlightenment as to why VBA Evaluate(“ “ ) doesn’t work on the reference to a closed File might reveal something to help me in my other work.


Thanks

Alan

_...


Demo Code:
Sub RangeObjectRefEileensLounge()

Code: Select all

Sub RangeObjectRefEileensLounge() '    http://www.eileenslounge.com/viewtopic.php?f=30&t=25213&p=195481#p195465      http://www.eileenslounge.com/viewtopic.php?f=30&t=25213&p=195481#p195481
10   Dim vTemp As Variant ' Variant so as to catch Errors or se what it gives Bach when it has a choice
20   Rem 1 Worksheets referencing
30   Dim Ws1 As Worksheet, Wb As Workbook '
40   Set Wb = ThisWorkbook: Set Ws1 = Wb.Worksheets("BracketWonk")
50   Dim rng As Range ' variable assigned to Range Object
60   Rem Refs "What does Excel do after it sees a ="    "Getting at cell values"
70   Rem 2  Range Objects of a Worksheet. Worksheets Range Property
80   Dim GetTheStringSBach As String
90    Let GetTheStringSBach = Ws1.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1").Value 'works returns String
100   Set rng = Ws1.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'works returns Returns Range Object
110   Let vTemp = Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'works returns String
120   Set rng = Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'works returns Returns Range Object
130   Set rng = Evaluate("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'works returns Returns Range Object
140  Rem 3 Excel.Application Range (Object) ..referrences  .. and stuff ;). Range Object
150  '3a) Open Workbook Range Object
160   Let GetTheStringSBach = Application.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
170   Let GetTheStringSBach = Application.Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'Works - String probably defaults to that for the opened Workbook
180   Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'Works - String probably defaults to that for the opened Workbook
190   Let vTemp = Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'Works - String probably defaults to that for the opened Workbook
200   Set rng = Application.Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'Works - String probably defaults to that for the opened Workbook
210  Rem 4 Closed Workbook
220   Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Errors
230   Let Range("A2").Value = "='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1" 'Works in cell ( Application Range object ( ActiveSheet ) referrenced in Normal Code Module, or Range Property of Worksheet of, referrenced in Worksheet Class Module
240   '                               vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1")
250         'Let GetTheStringSBach = Range("='" & ThisWorkbook.Path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")'Error syntax at complie
260   Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")
270  '4b Closed workbook File myFileToClose.xlsm Open  ( which must be in the same Folder as This Workbook, ClosedWorkbook.xlsm, and ...
280   Workbooks.Open Filename:=ThisWorkbook.path & "\" & "myFileToClose.xlsm" '
290   Let GetTheStringSBach = Application.Range("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") '...will...
300   Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Works, I expect the string reverts to thhe following
310   Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!R1C1")
320  ' close the workbook myFileToClose.xlsm ......
330   Workbooks("myFileToClose.xlsm").Close SaveChanges:=False '

End Sub






























'
' Rem Ref
' http://excelmatters.com/referring-to-ranges-in-vba/#comment-185793
' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
' '1. http://www.excelforum.com/excel-programming-vba-macros/1156725-executeexcel4macro-pulling-data-from-a-cell-in-closed-workbook-slow-for-large-file.html
' '2. http://www.excelforum.com/excel-programming-vba-macros/1161798-read-closed-wb.html
' http://www.excelforum.com/excel-programming-vba-macros/1126860-is-it-possible-to-sum-entire-column-with-out-opening-excel-2.html#Post4321006
' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
' http://listenonrepeat.com/watch/?v=ivFYVAntpw0#Skid_Row_-_I_Remember_You
' http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for.html



' ' Rem Ref
' http://www.excelforum.com/excel-programming-vba-macros/1126860-is-it-possible-to-sum-entire-column-with-out-opening-excel-2.html#Post4321006
' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
' http://listenonrepeat.com/watch/?v=ivFYVAntpw0#Skid_Row_-_I_Remember_You
' http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for.html

Sub RangeObjectRefMacroModul5() '  http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
Dim vTemp As Variant ' To catch Errors
30   Rem 1 Worksheets referencing
40   Dim Ws1 As Worksheet, Wb As Workbook '                                         , Ws2 As Worksheet '
50    Set Wb = ThisWorkbook                                                                            '
60    Set Ws1 = Wb.Worksheets("BracketWonk")
70   '
80   Rem Refs "What does Excel do after it sees a ="    "Getting at cell values"
90   Rem 2  Range Objects of a Worksheet
100  Dim GetTheStringSBach As String
110   Let GetTheStringSBach = Ws1.Range("A1").Value
120   Let GetTheStringSBach = Ws1.Range("A1")
130   Let GetTheStringSBach = Ws1.Range("BracketWonk!A1")
140   Let GetTheStringSBach = Ws1.Range("=BracketWonk!A1")
150   Let GetTheStringSBach = Ws1.Range("[ClosedWorkbook.xlsm]BracketWonk!A1")
160   Let GetTheStringSBach = Ws1.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
162   Let vTemp = Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'works
170  '
180  Rem 3 Excel Application Range ( Object )
181  '3a) Open Workbook Range Object
183   Let GetTheStringSBach = Range("A1")
186   Let GetTheStringSBach = Range("BracketWonk!A1")
190   Let GetTheStringSBach = Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
200   Let GetTheStringSBach = Excel.Application.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
205  Dim rng As Range: Set rng = Excel.Application.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
210   Let GetTheStringSBach = Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1")
212   Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'Works - String probably defaults to that for the opened Workbook
215   Set rng = Excel.Application.Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1")
216  '3b) Closed Workbook
217   'Let GetTheStringSBach = Range("='" & ThisWorkbook.Path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Error
218   Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Errors
219   Let Range("A2").Value = "='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1" 'Works in cell
220   'Let GetTheStringSBach = Range("='" & ThisWorkbook.Path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")'Error syntax at complie
230   Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")
240   'Let GetTheStringSBach = ExecuteExcel4Macro("='" & ThisWorkbook.Path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")' Error syntax at Compile
250  ' Open File myFileToClose.xlsm ( which must be in the same Folder as This Workbook, ClosedWorkbook.xlsm, and ...
260  Workbooks.Open Filename:=ThisWorkbook.path & "\" & "myFileToClose.xlsm" '                                         ... if you put a new value in worksheet Sheet1, Cell A1 then this...
270   Let GetTheStringSBach = Range("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") '...will...
272   Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Works, I expect the string reverts to thhe following
280  '...  return that Value to the variable GetTheStringSBach, and so will the next line...
290   Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!R1C1")
300  ' close the workbook myFileToClose.xlsm ......
310  Workbooks("myFileToClose.xlsm").Close SaveChanges:=False '...without saving, then the next line....--
320   Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!R1C1")
330   '   --... will now fill GetTheStringSBach with the previous value
340 '
350  Rem Range Member Address Wonks "Cell Addressing"
360  ' Range Member Address Property   https://msdn.microsoft.com/en-us/library/office/ff837625.aspx
370  Dim strAddress As String
380   Let strAddress = Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=False, RelativeTo:=Cells(32664, 32))  'Format required. A link with RC for cell format. All Address arguments are demonstrated, but the last is ignored due to the first two. External:=True is required in other uses to allow acces to external Workbooks. We need to build the referrence to include the two ' ' and the Full Workbook Path. False is needed. First two and forth argumenrts are the default, and fifth is not needed here. Hence typically seen as (, , xlR1C1)
390   Debug.Print strAddress ' Returns R1C1, what we want
400   Let strAddress = Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=True, RelativeTo:=Cells(32664, 32))  'A link with RC for cell format. All Address arguments are demonstrated, but the last is ignored due to the first two. External:=False is required in other uses to allow access to other open Workbooks. Here that would error and Falkse is needed. First two and forth argumenrts are the default, and fifth is not needed here. hence typically seen as (, , xlR1C1)
410  ' Returns R1C1, what we want
420   Let strAddress = Ws1.Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=True, RelativeTo:=Cells(32664, 32))
430 '
Dim FullPath As String: Let FullPath = ThisWorkbook.path
Dim FullFileName As String: Let FullFileName = "ClosedWorkbook.xlsm"
Dim wsBgAcsName As String: Let wsBgAcsName = "ASheetToIgnoor"
Dim RefClsdws As String: Let RefClsdws = "'" & FullPath & "\" & "[" & FullFileName & "]" & wsBgAcsName & "'" & "!": Debug.Print RefClsdws '  In Immediate Window (Ctrl+G) is seen    'H:\ALERMK2014Marz2016\NährwerteTabelle\Sept2016\Sept2016\[NutritionalValues2016.xlsx]NutritionalValues'!
Let GetTheStringSBach = ExecuteExcel4Macro(RefClsdws & Range("J" & 1 & "").Address(, , xlR1C1))
End Sub

' Rem Ref
' http://excelmatters.com/referring-to-ra ... ent-185793" onclick="window.open(this.href);return false;
' http://www.excelforum.com/showthread.ph ... ost4483344" onclick="window.open(this.href);return false;
' '1. http://www.excelforum.com/excel-program ... -file.html" onclick="window.open(this.href);return false;
' '2. http://www.excelforum.com/excel-program ... ed-wb.html" onclick="window.open(this.href);return false;
' http://www.excelforum.com/excel-program ... ost4321006" onclick="window.open(this.href);return false;
' http://www.excelforum.com/showthread.ph ... ost4483344" onclick="window.open(this.href);return false;
' http://listenonrepeat.com/watch/?v=ivFY ... member_You" onclick="window.open(this.href);return false;
' http://www.excelforum.com/excel-program ... t-for.html" onclick="window.open(this.href);return false;

[HansV]

The Evaluate method doesn't work with references to a closed workbook. Why not? Because Microsoft didn't program it that way. I wouldn't be surprised if the failure of the INDIRECT worksheet function to retrieve values from a closed workbook is related.

There are only two ways to get a value from a cell in a closed workbook using VBA, and you know them both:
1) Create a formula in a cell in an open workbook that refers to the cell in the closed workbook, then read the value of the cell in the open workbook.
2) Use ExecuteExcel4Macro. Although this method is very old, it magically still works in recent versions of Excel.

See Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast for some interesting stuff on Evaluate. It explicitly mentions that Evaluate doesn't work with closed workbooks.

[Rudi]

Nice article in your link Hans. I learned a thing or two from that.

[Doc.AElstein]

Hi Hans
Thanks for the reply..

.... Why not? Because Microsoft didn't program it that way.....

I would have put money on that being your quick answer .. Lol.. :)
My argument was that possibly Evaluate does work in principal but possibly, the thing that for the string in Range(“ “) changes a closed Workbook reference to an “open” Workbook reference is also used ( possibly ba an oversight initially ) in the Evaluate(“ “ ) string. But in end effect your answer still agrees with that. Written is written.
Having some more insight into why ,might have been interesting I thought.
Maybe Bill Gates might by chance one day pop by and answer, if he can remember why it was written that way. In principal my guess was that the ExecuteExcel4Macro( ) in the ExecuteExcel4Macro(ClosedWorkbookreferrence) is doing just what the Evaluate(“ “) usually does.
I do not have a real problem here, I just thought if someone pops by sometime with some enlightenment to why Microsoft programmed it that way, then it might be useful to know.

_....


I am not sure why you do not include the ADO way for getting Cell values from a closed Workbook? ( I just started recently having a look at comparing that as an alternative _...
http://www.excelforum.com/excel-program ... ost4516378" onclick="window.open(this.href);return false;
http://www.excelforum.com/excel-program ... ost4516599" onclick="window.open(this.href);return false; _...)
But possibly you are referring specifically to getting from one cell, which I guess is not really the idea with ADO which usually gets all the data, or maybe I think a column. I am only just getting into that.
There is also queer “Query” things. I do not understand them at all , - But I guess they are also in principle about getting a lot of data at a time rather than just one cell

_..................

Thanks for the link. I have seen that Charles Williams Blog before, ironically by chance just after I did a similar much bigger one comparing the different Evaluates, like Charles Williams did .
http://www.excelforum.com/excel-program ... t-for.html" onclick="window.open(this.href);return false;

Evaluate is something I have Fought with quite a bit.

Just out of interest here some links to some popular Threads and stuff
http://www.excelforum.com/showthread.ph ... ost4368361" onclick="window.open(this.href);return false;

The most interesting use I find or Evaluate(" ") is the using of it in place of a loop.....
https://usefulgyaan.wordpress.com/2013/ ... -evaluate/" onclick="window.open(this.href);return false;
The syntax especially when quotes are involved can be very tricky to master, as well as understanding how to “coerce” some functions to give out the whole Range that without the Evaluate you would need to do with looping
http://www.excelfox.com/forum/showthrea ... n#post8786" onclick="window.open(this.href);return false;

I did a simple example of this loop replacing technique in an alternative code here just recently
http://www.eileenslounge.com/viewtopic. ... 81#p194481" onclick="window.open(this.href);return false;
_ - there you could put a formula in over a range in one line without looping and still have it come out as an absolute type reference. Understanding the use of Spreadsheet Functions like Row() and the “coercing” or Evaluate to give out an Array or range can allow for some novel solutions and “one liner” code solutions
http://www.mrexcel.com/forum/excel-ques ... ost4370502" onclick="window.open(this.href);return false;
http://www.excelforum.com/showthread.ph ... ost4402471" onclick="window.open(this.href);return false;


_.........................

Anyways,
You can often find people explicitly mention that Evaluate doesn't work with closed workbooks,_..
http://www.mrexcel.com/forum/excel-ques ... sheet.html" onclick="window.open(this.href);return false;
_.... I was ( am ) just curious as to why ( or why it has been written that way. ) . Just out of interest, - often interesting results come out of getting an insight into these things. But no big deal :)

Thanks again for the Reply
Alan

[HansV]

I doubt that Bill Gates would know - he wasn't involved in developing Excel. The teams that develop the Office applications are notoriously tight-lipped. Sometimes they tell us what they decided, but never why...

[Doc.AElstein]

Hi,
I am just adding the ( likely ) solution to this Thread.
I think I actually had it and it is there in my explanations of what happens. …

The Evaluate(“ “) thing uses similar processes as those going on when you do things in the spreadsheet but / and also is doing things that go in VBA.
In terms of what actually is going on “internally” as it were, once any process starts, is such that the distinction between a spreadsheet process and something happening in a VBA code is very fine anyway…. That is to say , after hitting Enter in a spreadsheet some VBA type code kicks in whether it is a code you wrote or a code wrote by some Microsoft person or persons years ago.

I made the point myself of the similarities I had seen between what went on with_...
Range(“ ”) type things
And
Evaluate(“ “)
_.. when a reference is given as the string argument
That is I think the crux of it. I don’t know why I did not remember something I have explained to others time and time again:

Indeed they do appear to do the same.
They both return a Range object
For Evaluate(“ “) it is untypical. Usually a single value or a field of Variant type members is returned for Evaluate(“ “).
The return of a Range object as a result of a reference being given is often overlooked in such a use of Evaluate(“ “), as
_a) usually the shortened version ( using just the address ) of Like_..
Evalute(“A1”) or Evaluate("J22:J23")
_.. is given. It is not recognized ( by us ) as a reference. I have suggested in this thread it is, as Excel effectively adds on “internally” the extra Workbook and Worksheet parts. ( This I believe is also what happens when a single Area Range address such as A1 or J22:J23 is typed in a cell in )
_b) typically the receiving variable is declared ( or left at the default ) of Variant. What then happens, as often in Excel VBA, is that the Range defaults to Range.Value. A field of Variant type members is then returned.



So the answer to the question “Why doesn’t VBA Evaluate work on a closed Workbook reference” is, I think, this:
Initially Evaluate tries to return a Range object. It is simply using a string reference as the “reference” cell or cells. That is because in a single cell such a string is taken as a reference

As discussed in this thread: If a workbook is closed , then the full reference including that workbook name, used in Range(“ “) or Evaluate(“ “), will fail / error.
Why?
Answer: You cannot Set a Range object to a closed workbook.

Why can you not Set a Range object to a closed workbook
I can accept the simple answer to that is that Excel cannot do that.
Why?: - Manipulating cells, ( In other words interacting with the Range object ), is arguably 99% of what Excel is about. If you could do that, then you could probably do “Excel” without having Excel software. I am guessing you could do it with Visual Basic or similar instead. The “interface” necessary for an external reference to return a Range object would, I expect , look like excel software itself.

End of Alan Theory 237 :)

_.........................-__________________
Edit: around March 2017
In "Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel"
here
http://www.excelforum.com/development-t ... ost4595462" onclick="window.open(this.href);return false;
http://www.excelforum.com/development-t ... ost4590208" onclick="window.open(this.href);return false;
Some further ideas support the conclusions here. In breif, the fundamental idea of a Excel "holding" a cell, through a string reference, as a Range object is discussed.