Evaluate(“ “) and [] Differences. Evaluated Array Return Needs extra Bracket for []()

VBA Evaluate Method: “full “ Evaluate(“__”)(1) “works” ; “shorthand” [__](1) doesn't.

Hi

I am simplifying some long code lines by replacing the “full” Evaluate Command

Evaluate(“__”)
with its “shorthand” Command
[__]

Mostly this is going fine. Evem with some very complicated and long code line sections. But I hit a problem when a ( 1 Dimensional ) Array is returned and I try to “pick” of one element.

THE ISSUE HERE is getting here “shorthand” Command to work in the same way that the “full” Command does. ( I am not interested at this stage in alternative ways to get the required ( column here ) information, thanks ! )
_...................................

Consider a simple Demo.
I have a simple Range

Using Excel 2007 32 bit

Row\Col	B	C
4	16	26
5	18	28
6	20	30

I am using the “full” Evaluate Command in various code lines to get at things like the start column, column count , the indicies, etc.. of the Range.
I also show the attempts at replacing some of the "Full" Evaluate command parts with the "shorthand" Evaluate equvalent part..., - ( At least I thought to was was equivalent, in the way I am applying it, at least )

Line 11 in the following code is refusing to compile. This is my main problem currently.
The error returned roughly translated to English is:
Complie Error:
“Wrong number of arguments or invalid assignment to a Property”

Please Login or Register  to view this content.

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


_ I can see no reason why this will not work
11 ______ [column(B4:C6)](1)
When this does:
10 ______ Evaluate("column(B4:C6)")(1)

_.......................
_ It is further puzzling, as my workaround clearly works

13 Dim vTemp As Variant
14 Let vTemp = [column(B4:C6)]_____' Returns 1 Dimensional Array {2, 3}
15 Let Cs = vTemp(1)______________' Returns first Array Element, = 2

Unfortunately this workaround is not of use to me as I need / want to replace the “Full” bit with the “shorthand” bit in the same line.
_.........

Can anyone spread any light on this, seeming, Anomaly... ( I am currently successfully substituting the “working” “shorthands” into some very complex and long code lines with no problem. Just this one Problem is holding me back )

Thank you

Alan

Albert
Using square brackets is only identical to calling the Evaluate method with a string argument.
Just invoke the evaluate engine as all your code is long hand
https://msdn.microsoft.com/en-us/lib.../ff193019.aspx
https://support.microsoft.com/en-us/kb/211601
discussion

Hi Pike,
Thanks for the reply.

_1 ) The second link seems not too relevant to my actual problem, but a useful link none the less, thanks. – I have a separate issue with .Value and .Value2 , resulting in a Date format “evaluation” discrepancy, somewhere else.
http://www.eileenslounge.com/viewtop...177713#p177711
And maybe that could give me an insight into. But I do not think that is of too much relevance here.

_2 ) The second link explains amongst other things the important difference between the ( call it square bracket version rather than “short hand, if you choose ) and the Other “Full” one. - That being
“............. The advantage of using Evaluate is that the argument is a string, so you can either construct the string in your code or use a Visual Basic variable....”
_2a) I think what you mean here

Originally Posted by pike

Using square brackets is only identical to calling the Evaluate method with a string argument.....

is along the lines of the above
_........................
I have tackled Evaluate a lot, and understand all that. But a useful link to have in the thread anyways, so thanks again

_3 ) I have a specific issue here, that being that code line 11 does not work , whilst code 10 line does. In addition code lines 13, 14 and 15 also work - the latter making it seem clear that code line 11 should work

_ At the end of the Evaluation Day, by the square bracket or the other way , I am left with an Array, which appears to be the same in both cases.
_ I see no logical explanation as to why line 10 is not “working”

_4)

Originally Posted by pike

.......
Using square brackets is only identical to calling the Evaluate method with a string argument.
Just invoke the evaluate engine as all your code is long hand......

Are you suggesting that, in terms, for example, of speed that there are no differences in the two ways? I ask as One of the reasons for me trying to “simplify” some code with the square bracket way was that i thought ( in my “only just a forum Expert.. Lol.. ignorance” ), that it might “run” faster. Maybe this is naive if at Compile VBA sees either and says “When I come here, I will “Invoke” “...........the Evaluate Engine, as it were....

Thanks again for the reply, appreciate it

Bill Gelatine

Some Evaluate threads that may be relevant in parsing..
http://www.mrexcel.com/forum/excel-q...s-dangers.html
http://www.excelforum.com/excel-prog...ml#post4368361

Whose thread is this?

Hi Jindon

Originally Posted by jindon

Please Login or Register  to view this content.

Brilliant:

Thanks so much. I am very grateful...

I have seen this before.....

Post 13 here
http://www.mrexcel.com/forum/excel-q...objects-2.html

I even thought I had tried it... I got thrown out by the fact that line 14 and 15 were working.

I would really like to understand what is going on here...
Can you help ?

My Variant is maybe coercing somehow to get the right result? –
Is the [ ] doing something different here.

The error sort of ties up with Rory’s explanation in the above link –
„…….parentheses are only necessary ….. to indicate that the last part is intended to be indices to the resulting array and not arguments passed ………“

So although I maybe do not need this

Please Login or Register  to view this content.

_... why never the less does it not work..?


I would really like to understand this
Thanks again, this has got me "unstuck"

Alan

Originally Posted by TMS

Whose thread is this?

I was so excited by Jindon's answer i jumped on the nearest computer, which was my wife's to quickly post Pike's reply.. She was logged in !! I did not notice, I thought i was still logged in from using her computer yesterday-- That's my story ...

Not that you have an alter ego to respond to your threads ... instead of talking to yourself?

Worksheetfunction has a lower overhead than Evaluate
worksheet.evaluate is faster than application.evaluate but has large overhead
Worksheet.evaluate has comparable speed to a cell formula but a higher overhead
beware of the quirks of evaluate

Originally Posted by pike

Worksheetfunction has a lower overhead than Evaluate
worksheet.evaluate is faster than application.evaluate but has large overhead
Worksheet.evaluate has comparable speed to a cell formula but a higher overhead
beware of the quirks of evaluate

Thanks again Pike,
Can you clarify which is which there please?
How does that tie up with what I was doing?

I did see some very intersting results here
Post # 15
http://www.mrexcel.com/forum/excel-q...ml#post4332606

and here
http://www.excelforum.com/excel-prog...orkbook-2.html

Something seemed to going on differently in where
[ ]
and
Evaluate(" ")
"went"
somehow suggesting that [ ] was more "tied" to a Worksheet, not being as dependant on which module it was in as was the Evaluate(" ")

Hmmm....
_... I maybe need to review my own Old Posts ...

Don't over evoke the evaluate engine and always determine which syntax is quicker, has less overheads, easiest to debug and change...its very good to replace loops or used in the those elegant one liners.
Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast

Originally Posted by Doc.AElstein

Hi Jindon
So although I maybe do not need this

Please Login or Register  to view this content.

_... why never the less does it not work..?

Only my guess,
Evaluate method can return Array itself, however short cut notation of evaluate[] will act as array(element can be referenced by an index) only when it loaded to the variable/output.
So you need to add () in order to explicit to Array for [], but error when you add to the array itself for Evaluate.

Again, no clear reson.
Ask directly to the developer...

Originally Posted by TMS

Not that you have an alter ego to respond to your threads ... instead of talking to yourself?

I find I often talk to in / myself, or whatever, or when writing a Post, or ramble... .
Actually ,by preparing the question fully, I often find I answer the question myself.
It is quite healthy. I would probably post loads more questions, if I did not think carefully about my question, before.
It is bad, IOurVHO, to ask too spontaneously, - seems to waste a lot of time.
Answering a bit too quick can also be bad - Well meant quick answers can confuse the issue ( And lose the OP the chance of getting picked up on a “Unanswered Threads” Search Button Wonk )

And some of the few questions I ask, no one seems to know the answer to anyway ... Often my Thread stretches the bounds of Knowledge, which is also healthy, I think.

alter ego ... Hmm that made him think..

I think we might have something there,
Made us think.....

Very frustrating, -where’s He's gone again...I always seem to miss him by a few seconds...

Me .... ?? Bill Gelatine

Invoke Pike Evaluate Rabbit Rabit. How’s the Bunny ? Bunnytation Banters

Hi Pike
Thanks for the Reply. I am still trying to understand a few things here...
_1)

Originally Posted by pike

Don't over evoke the evaluate engine ....

I fear I shall continue to evoke, quite substantially, maybe a couple days more at least, amongst other reasons as the Wife is away..
I know I should not do it. Lol !! RL will likely cause an abrupt end soon. Maybe a relief for everyone.
_....................................

2 ) Just some comments on that Charles Williams Link you gave. ( Its not much use too me )

Originally Posted by pike

......Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast

I have been to that site a lot. It is a good one. I had that link to Charles Williams Site given me a while back.
https://usefulgyaan.wordpress.com/20...e/#comment-361

_..But I got a lot more from discussing in that Gyan Blog that gave me that Link..
_..That Evaluate Blog by Charles Williams is useful maybe if you are doing something very similar to what he has done his timing experiments on. Otherwise it is not Telling me anything,.
I have many Threads kicking around myself where I compare the Evaluate Method ( Evaluate ( “ “ ) ) to other methods. I know it can be quicker in some Looping.
Mostly in a Range Evalute Type way that seems to be the case.. *****
But Some of the other Elegant one Liners do not always come out faster than Looping, apparantly..
_................but....

_3) Different Evaluate versions?? - What are they exactly....
.......But.........I am no further in getting a clear picture of the differences,.. - this is still open:

Originally Posted by pike

Worksheetfunction has a lower overhead than Evaluate
worksheet.evaluate is faster than application.evaluate but has large overhead
Worksheet.evaluate has comparable speed to a cell formula but a higher overhead
.....

Originally Posted by Doc.AElstein

Thanks again Pike,
Can you clarify which is which there please?
How does that tie up with what I was doing?
.......

_ What I meant there was which is which ? how many are there of these
Application
Worksheet
Or
whatever else
how many versions are there ? ??

Up until now I was just comparing
Evaluate(“ “)
and
[ ].
That was bad enough!
_3a) How many more variations are there? ???

3b) Implicitly Implied version of Evaluate when using = Evaluate(“ “)
_...What I have been using by ignorantly relying in the Implicit default – when I always do this in my code...
Evaluate(“ “)
_....what has that been defaulting to?? ????

3c) Assuming for now that there is two versions of the Evaluate ( other than the [ ] ), then is there some significant difference between
Application.WorksheetFunction.Evaluate("
and
Application.Evaluate("
_.. compared to other thiings like the Index and Match, as example.?? The reson why I ask is...
_.....I have had some quite intensive discussions on things Like Index or Match when it comes to which one of two I am using, for example
Application.WorksheetFunction.Index(__ __ 'Worksheet Function Index Worksheet Fuction
Application.Index(____________________ 'Application Index Worksheet Fuction

_. Now I have there
Application Index
And
Worksheet Function Index
_.. But most ( real ) experts tell me they are both Worksheet Functions. ( And I have discussed to death the differences, - no one is 100 % sure about that !! )

I note here that I cannot do
Index ' --- This will error - neads Application.Index or Application.WorksheetFunction.
_.. what is this telling me ( if anything ) about Evaluate - Is there some signiificance to this ?????
...


_........

_4) Just some comments again here *****

Originally Posted by pike

.....its very good to replace loops or used in the those elegant one liners......

.. I certainly concur (agree ) with that...? ... mostly....I have some epic Threads on that Theme, comparing the speeds etc.
http://www.mrexcel.com/forum/excel-q...e-vlookup.html
http://www.excelforum.com/showthread...45#post4326245
_..... my next instalment to Evaluate one liners Wonk will be published shortly here:
http://www.excelfox.com/forum/showth...-Variant-Array
_... worth a subscription, IMHO..
Thanks to Jindon I have been able to get on with that one!! – be posting shortly... The “ Daily ( Morning ) dose of Excel Fox.. Lol.. Wonk..poo “ ; )
But I do note that it seems to be best in Range Evaluate type situations, pseodo code:
rngOut.Value=Evaluate("rngIn & some action on it____ ")

_ that beiing a faste option often then the loopiing ( pseudo ) code
__For Cnt = 1 To N
___Let rngOut(Cnt) = rngIn(Cnt) and some action on it _____
__Next Cnt

Thanks again for the Replies
Alstein Coffeemacher Allan Bill Gelatine making a Coffee

Evaluate Function [ ] and Evaluate Function Evaluate(“ “)
Just adding some solutions before marking the Thread as solved.... maybe..
The answer to the original question seems to require some understanding of Evaluate Function [ ] and Evaluate(“ “) differences.....


_1 ) What’s the Difference
[WhatYouWriteInACell]
versus
Evaluate(---here a “ “ type string thing--).......Evaluate (stringexpression) -- Post #7 http://www.mrexcel.com/forum/excel-questions/696820-quotes-visual-basic-applications.html

The main difference is that [ ] does not take a string, it takes exactly as you write , for example, in a Spreadsheet cell.
The advantage of using Evaluate(“ “ ) is that the argument is a string, so you can use Visual Basic variables within it.”

It is Often said that this limits to hard coding, for example not being able to build up some dynamic ( for example ( cell ) referencing. ( I have often read this: “ can't build up the address string if you use the [] notation ). - Incorrect IMO. So here, Just a quick demo – extending my code from Post #1, ( which “works” now!! Thanks to Jindon!! ) .. “..

So I demo the dynamic referencing of an Area of contiguous cells in a Spreadsheet whilst using the [ ] way of Evaluate. And I compare it with variations of the Evaluate(“ “) alternative as well as the standard VBA Range Referencing way
_.............................

_..._2 ) Dynamic ( Non hardcoding ) range referencing of a an Area of contiguous cells in a Spreadsheet using [ ] , Evaluate(“ “) and VBA Range

( This is based on an idea by and kalak http://www.mrexcel.com/forum/excel-q...s-dangers.html
And snb
http://www.excelfox.com/forum/showth...=9714#post9714
_....................)

A Code showing a bit more detail to that is here, ( in particular it shows in detail how to Obtain grid coordinates for an Area of contiguous cells in a Spreadsheet using [ ] and Evaluate(“ “) through the use of a Named Range for that Area:
http://www.excelforum.com/showthread...50#post4399150
http://www.excelfox.com/forum/showth...=9820#post9820

The key ( to the [ ] way “working” in the non hard Coding sense ) is the use of a Named Worksheet Range. This does not require use of quotes “ “. The dynamicy non hard coding is achieved by being able to Create a Named Range, and from there on referencing it by its name as you would in a Spreadsheet without any quotes. “ “

_2 b) So Brief description of simplified code presented below
Being already overwhelmed with VBA I try to limit using as many different things as possible, but it is so easy and one less step than assigning a variable to a Range Object, so worth “learning” it......

From Line 20
Simple assigning of a Spreadsheet range of contiguous cells to a Name held within a Worksheet Names Register.

Line 80 The “Thing” used to reference the contiguous cells is assigned.

Line 130 is the standard range Object assignment ,
and
Line 140 is the comparable assigning of a Name to the same Area within a worksheet.
There appears something subtle in the difference here of the two versions,
http://www.mrexcel.com/forum/excel-q...ml#post4332606
_.. more on that in a very final post here if I sort out why the extra () is or is not needed..

From Line 150 Properties assignment is demonstrated for the different ways

From Line 180 ,
the rest of the code gives extensive variations and combinations of then 3 ways to get at some typical Properties. These are the Referenced “Area” Row, Column, Rows Count, Columns Count, Properties and finally the Stop Row and Stop Column Area of Contiguous Cells "AloPerties".
......
Alan
William Turner, Bill Gelatine

Code:

Please Login or Register  to view this content.

@rorya: heh, heh, love the location. Not as bad as it could be then?

Lol

Originally Posted by TMS

@rorya: heh, heh, love the location. Not as bad as it could be then?

The power it is urging in me us , .. erge ... erge.... oh no


Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

_....... Applolgies - i erged too soon

EDIT: or too late

Hi Rory
Thanks for the Input
_1) Thanks, - I had not got around to checking that. ( still not sure what you or anyone are referring to by Application and Worksheet evaluate – seems to be separate to [ ] Or Not . No one has a clear answer to that yet

_2) Evaluate are not the same. They are very similar but clearly not using the same code base.
That is the basis of a “closing “Blog” I will do later to finish this thread
– that is all “hidden” in what I have written.
a couple of points
_2(i) you can include a vba variable in [] (though it is fairly pointless) whereas it will simply fail in Evaluate
_ One of us is mixed up there, as I see it: Not sure if you're Drunk ??
you can include a vba variable in Evaluate(“ “ & vbVariablleyWonk &” “ ) (and it is very un pointless ) whereas it will simply fail in [ ]. .. well maybe not..........
_2(ii)........ as I showed , through the use of a Worksheet Range Name, you can to all intents and purpose do very close to doing vba Un Hard coded in [ ]- ( Not sure if a Workbooks .Name is a Variable is a vba Variable. If it is then clearly you can put a vba Variable in [ ] ......

_3) ,Depending on which Evaluate your talking about, . – something related to that may help solve the mystery of the extra () needed in my codes with the [ ]

Alan
P.s

tl;dr - I sympathise, I just needed a few hours to fathererm out what the f__k I was talking about here:
http://www.mrexcel.com/forum/excel-q...ml#post4069018
_ but it was worth it - .....................
_................ it seems I came close to solving this problem there... some one else, ( use a simple pseudo made up name like Rompering in the grounds until his soul can be laid to rest” to save his reputation, - he did a Jindon magic there. )... But i’d lost the Thread there, if you take my meaning

Sounds like we both have not quite got this one clear, I guess I will probably strigten it out later..
_ No one seems to know which evalute they are talking about.... I read 2 - 3 depending on how you read what they say, even here
https://fastexcel.wordpress.com/2011...twice-as-fast/
he maybe talking about 3 verssions or no one is sure if [ ] is ashorthand for one of the other two... or more likely as often, even the best people do not know, and are not too happy to admit it.. I will have a think asnd maybe sort it later...
but always welcome your comments; thanks again tl;dr have a feeling you might have been TD TR too drunk to read as you appear mixed up .... : rolley rolley drunk eyes :

Albert
Κοιλάδα του θανάτου


EDIT: Thanks for the Bunny Rabbit Rep

Hi Alan

I hate to say this, but it just sounds like you're struggling to go to the toilet

Ναι , αν και θα περπατήσει ...

[ ] ..__ Evaluate(“ “) ....__ . Range(“ “) ..__ . Named Ranges ......__ ....... Summary

Hi
This is just adding a Penultimate Thread as part of a Final Summarising Solution to this Thread.

A key to solving fully this thread an understanding of the use of Named Ranges in VBA ( and Spreadsheets ) , in particular with their different ways of usage associated with the different forms of VBA Evaluate, the_...
[ ]
_....And various
Evaluate(“ “)
_........ways , along with the “standard” VBA Range referencing through
_...............Range(“ “)

_. In support of this post I have done a large amount of measurements trying to consider every possible combination of code lines using all of the above methods when referencing a range which for simplicity I have taken as the first cell in a Worksheet, ( having the “reserved by Bill Gates or whoever” Named Range “A1” “ !! )
( These results are “farmed out to an appendix thread. Here I attempt to just summarize the results )

The emphasis was on using a “Alan made” Named Range for that first cell. The name used is “CoN” – A close to comparison to “reserved by Bill Gates or whoever” Named Range “A1” “

This Named Range was originally used in the first Post in this Thread ( and reflecting the point of the Thread and the consequent initial problem ) to make the
[ ]
Way of evaluate more “non hard cody stylio” (in other words not restricted to hard coding of a Range object within the [ ] )
This lead to the original problem prompting this Thread, and the solution was found to ( at least in order to understand it ) require some understanding of how “differently”
[ ]
“works” compared to the other methods which by nature are much more “non hard cody stylo”.

_ The last few Posts actually covered all of the above fairly well, ( in particular Post #24 and Post # 25 ) but Scope has also reared its ugly head a bit, and so some discussion of that is given here.
Scope in this context is very generally talking in a very wide sense about on and / or from “where” our codes will work when accessing from or pasting to, or generally interacting with the named Range.

Three long codes are considered which are virtually the same Code. ( Each code had to be split in two to get it in a Post, but they are not separate parts: the First part second part of each code needs to be copies immediately under the first in the same Appropriate Modulee### )
These are used to repeat ( mostly the same ) experiments in 3 different ### Code Modules:

_1) The “correct” ( for want of a better word ) Worksheet Code Module, that is to say the Worksheet code Module of the Worksheet in which our Named Range is, ( Worksheet "BracketWonk" )
Code: Sub EvaluateRngNmesInWorksheet_BracketInWorksheetModuleBracketWonk()
http://www.excelforum.com/showthread...t=#post4404122
http://www.excelforum.com/showthread...t=#post4404125

Results and discussions: “Re: Range(“ “)__ Evaluate(“ “) __ [ ] __ Differences in Named Range Referencing”
http://www.excelforum.com/showthread...83#post4403661

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

_2) The “wrong” Worksheet Code Module, that is to say any other Worksheet code Module
Code Sub EvaluateRngNmesInWorksheet_BracketInWorksheetModuleStan()
http://www.excelforum.com/showthread...t=#post4404134
http://www.excelforum.com/showthread...t=#post4404138

Results and discussions: “Foraying down Worksheet Scope Named ranges further than any uneducated man has ever been” http://www.excelforum.com/showthread...t=#post4403770

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

_3) a Normal Code Module
Code Sub EvaluateRngNmesInWorksheet_InNormalCodeModule()
http://www.excelforum.com/showthread...t=#post4404141
http://www.excelforum.com/showthread...t=#post4404146

Results and discussions: “Doing it Normal Code Module Styleo”
http://www.excelforum.com/showthread...t=#post4403783

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





In the next Post the basic Code Structure will be discussed along with all the important Results and Conclusions.

The first code _1) will be gone through in detail, but any differences in the other two codes are just minor ones to help bring up any impotent points.

( Note the last code given which goes in a Normal Code Module also included the KillWBNamedRanges Pubic Sub Code required in all three main codes )_........................









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

The first code _1)
Sub EvaluateRngNmesInWorksheet_BracketInWorksheetModuleBracketWonk()
will be gone through in detail, but any differences in the other two codes are just minor ones to help bring up any impotent points.
( Note the last code given which goes in a Normal Code Module also included the KillWBNamedRanges Pubic Sub Code required in all three main codes )


General Code explanation for
Sub EvaluateRngNmesInWorksheet_BracketInWorksheetModuleBracket()

Rem 1) Worksheets Info
_1a) The usual Declaring of a Variable and assigning to our main Worksheet “BracketWonk” has some more significance when talking about Evaluate(“ “). This is because one way , referred to as the Worksheets Evaluate, does not alone have some distinguishing bit before, resulting in something like
Worksheets. Evaluate(“ “).
Or
WorksheetFunction.Evaluate(“ “)

So in order to “get it” , it is simply preceded by a Worksheet Object, resulting in such a code bit
Worksheets(“Sheet1”).Evaluate(“ “)
Or in our case due to the declaration we have
ws.Evaluate(“ “)

It is not clear from the documentation, if this is really a separate way or just how Evalute works when used after a
ws.
or
Worksheets(“Sheet1”).
I exploit this uncertainty to allow for a number of Evalute “ways” to be discussed later.. !!!

_1b), 1c) “Resets”, as it were the Worksheets:

_1b)
Putting some arbitrary value , “Text in A1” in the “correct” Worksheet, Worksheets ”BracketWonk”
Putting some arbitrary value , “Text in Stan's A1” in the “wrong” Worksheet, Worksheets ”Stan”

_1c)
A simple Routine is called which Loops through the Workbook Names Collection Object and deletes any Named Ranges in the Workbook, effectively removing all Named Range Objects in the Workbook.


80 Rem 2) Named Ranges are made using initially the simple "one Liner" already discussed in detail
85 '2a) Workbooks Scope....
90 Let ws.Range("A1").Name = "CoN" ‘ Our original Named Range used from the outset of this Thread.
The discussion is extended a bit here, to cover the Scope ideas...That original “Short cut” way is more fully done with
120 ThisWorkbook.Names.Add Name:="CoN", RefersTo:=ws.Range("A1")
http://www.thespreadsheetguru.com/bl...o-named-ranges
The simple code line was defaulting to this “Workbooks scope”

125 '2b) Worksheets Scope
We need to consider a fairly complicated variation of when / where / what different Named ranges are accessed. Four different Named Ranges are scoped here to Worksheets Scope, in different ways

140 '2c) Name Object, Name Property , Name Name Wonks
A badly documented concept in VBA seems to be the “Name” or “Names” “things” So this code section tries t clear that up a bit, ( at least in relation to the Named Ranges as discussed in this Thread ) , .Name is used a lot in the experiments. I am not probably being 100 % technically correct with my explanations, as they are concentrating on applying to the current problem and I am already wandering of down the various Pit Holes that have cropped up while trying to get an adequate understanding to solve ( with explanations ) the original Problem.......

.Name is an Object, a “under” Object I think, at least here it is an under Object of a Range Object, specifically in out discussions an Object “under” or “belonging to” the first Cell which we have “Named” originally “CoN”

Amongst other things .Name has a .Name Property

So to return our “CoN” we need to do this
= ws.Range("A1").Name.Name
= “Con”

What confuses the issue a bit is that, as often with Objects, VBA, will return you a string that is somehow related to the Object if you “use” .Name by
_....either
Declaring it to a String Variable like
Dim wsNamestrObjA1_CoN As String: Let wsNamestrObjA1_CoN = ws.Range("A1").Name
_...Or just “using it” in some way such as
Debug.Print ws.Range("A1").Name

In our case the above gives us, in general, this form of string
=BracketWonk!$A$1

It is important to grasp that so as to distinguish “what we get” in the experiments.
So a few code lines go through various ways of “getting .Name and .Name
Just step through in Debug Mode ( F8 ) asnd hover over the various variables in the VB Editor code window, and it can easily be seen what is going on
_.................................

170 Rem 3 Some Preparation
Some final preparation includes clearing and opening the Immediate Window, as the idea of the rest of the Code is to print out various results alongside what was done too achieve those results..

I Note here that if you run any of the three codes outright, then for some strange reason the results in the Immediate window “vanish” at the End. - ..... I have no idea why that is, ???? but never mind, - it is intended to run the Code in Debug ( F8 ) anyways to see what is going on !!!!
_...........................

180 Rem 4) get / use Named Range
A large amount of experiments are made, broadly grouped by three Main Groups [ ] ..__ Evaluate(“ “) ....__ Range(“ “) .., but this is further grouped as Evalute(“ “) splits up, ......

190 '4a)... Range(" ")

390 '4b)... [ ]

590 '4c)... Application Evaluate ( Application.Evaluate(" ") )

790 '4d)... Worksheet Evaluate ( ws.Evaluate(" ") )

In fact it finally falls out that we have available

Range(" ")

[ ]

Worksheets [ ] ____ ___ ________( ws.[ ] )

Worksheet Evaluate(" “) ___ ______ ( ws.Evaluate(" ") )

Application Evaluate(" “) ___ ________ ( Application.Evaluate(" ") )

Worksheet Application Evaluate(" “)__ ( ws.Application.Evaluate(" ") )

Evaluate(“ “) ___________________ ( Evaluate(" ") )

( and that is ignoring ws.Range(" ") ... for now, which might arguably be regareded as as valid as ws.Evaluate(" ")

Some attempt is made to give Results and Conclusions in detail for usage of them all in the Three different code Modules,

_1) The “correct” ( for want of a better word ) Worksheet Code Module, that is to say the Worksheet code Module of the Worksheet in which our Named Range is, ( Worksheet "BracketWonk" )
“Re: Range(“ “)__ Evaluate(“ “) __ [ ] __ Differences in Named Range Referencing”
http://www.excelforum.com/showthread...83#post4403661
_...............................


_2) The “wrong” Worksheet Code Module, that is to say any other Worksheet code Module
“Foraying down Worksheet Scope Named ranges further than any uneducated man has ever been” http://www.excelforum.com/showthread...t=#post4403770
_.....................

_3) a Normal Code Module
“Doing it Normal Code Module Styleo”
http://www.excelforum.com/showthread...t=#post4403783

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

( Note the last code given which goes in a Normal Code Module also included the KillWBNamedRanges Function Code required in all three main codes )

In the next Posts it is attempted just to summarise important Conclusions from those three codes and results
_.................................................................................

How do Range(“ “) , [ ], Named Ranges & more Evaluate combinations than anyone knew existed Evaluate(“ “) work then?

Codes in the “correct” Worksheets Code Module
http://www.excelforum.com/showthread...25#post4403661
http://www.excelforum.com/showthread...t=#post4404122
http://www.excelforum.com/showthread...t=#post4404125

Range(“ “) and [ ]
We do not learn too much here just a refresh of memory we will always “get from” or “go to” or “put in” the Worksheet of whose Code Module the code is in. We see for the first time the often given as basic difference in these two in terms of the difficulties in building in taking a variable such as a String variable strNme and using that to get at something like a Range Object in the usual way ( other than the Named range way the discussion of which were made earlier in the Thread.
That could in some cases an Advantage, for example, if we want the string Name of the named range
450 [strNme] ' Stops at evaluating the vba String variable, returning its string value "CoN"
being quicker than this
455 Range(""A1"").Name.Name ' The Name Object of the Range Object, ( returning on "asking" by default a string of form "=BracketWonk!$A$1 referrence stylio" ). That in turn has the .Name Property applied to finally get the String Name "Con"
_...............

Evaluate(" ")

_. The next set of code sections dealing with the various Evaluate(" ") ways are not in this case of the code in the “correct” Worksheet code Module telling us too much. Even if we are not yet sure exactly how things work from the referencing / Scopy side of things, we are quasi in a “Belt and Braces” situation, initially our default Range Name being in the Sheet having Workbook scope, and unqualified references generally by default going to the Worksheet if a code is in that sheet Module. So we do not see many “fails” at first glance, and all references are seeming to go to this Worksheet.

_a) because of the use of quotes “ “ within the Evaluate(" ") and that we see Evaluate will build up first that string before quasi by default adding a “=” then doing as if in a cell the evaluation. Coming back to the limit of the [strNme] from previously we , explained here as
Evaluate(”=” & “” & strNme & “”),
Or simply
Evaluate(strNme)
All „versions“ give here the same results….and we see…
_b) there is in fact 4 versions of the Evaluate(“ “) , at least at this point of the discussion, and / or at least in terms of that "available in Code" to us

Application Evaluate(" ")

Worksheet Application Evaluate(" ")

Worksheet Evaluate(" ")

Evaluate(" ")

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

Codes in the “wrong” Worksheets Code Module
http://www.excelforum.com/showthread...t=#post4403770
http://www.excelforum.com/showthread...t=#post4404134
http://www.excelforum.com/showthread...t=#post4404138
This gets more revealing as we might expect having taken the Braces off now we are in a Code Module of a different Worksheet, and I decided to make it fun by loosening the belt and making some Scopy things Worksheet scope, instead of the “safe” global..... just to see......and make it interesting..

Range(“ “)._____
Two main differences come up here when comparing with the last post test ( same code running in Worksheet “BracketWonk”, the "right" one as it were )
_1) The Unqualified range references attempting to reference the Named Range ( “CoN” ) fail despite that the Named Range has Global Scope.
Interesting but very understandable:. - The code lines failing are attempting to return a Range Object for the first cell in Worksheet “Stan”, The error accordingly is “" Runtime Error 1004 Application- or object-defined error " . ( I note in passing a reference to the Named Range "Con" within the Spreadsheet does “work”. Clearly the Implicitly implied or Explicitly explied Range reference to "Con" will then look in the Workbook, by virue of the Workbooks Scope, for that Range in the "correct" sheet. )
These errors also occur for either Worksheets "BracketWonk" or "Stan" selected -This is expected as is normal with Range referencing in the Worksheet Code Module unqualified Worksheet references for a Range will go to the Worksheet whose Code Module contains the code

_1b) Note in addition that Line 210 is failing, ( and that also when selecting any Worksheet ) Once Again Worksheet "Stan" is being referenced here, and there is no Name Object ( under Object ) for the Range Object of the first cell in Worksheet "Stan"

_2) Unqualified range references for "putting things in" are going to the Worksheet in which the code is in. This is as expected. No big surprises there either.
http://excelmatters.com/referring-to-ranges-in-vba/

The selected Worksheet did not affect any results for Range(“ “)

_3) Some extra lines have been included just for this code. These go a bit off at a tangent conveniently to Foray the Worksheet Scope a bit more.._...... Append I did it then ** here...
_............ Append I do - Foraying the Worksheet Scope Named Ranges a bit more for Chris Guru Blog
http://www.excelforum.com/showthread...t=#post4403654

[ ].________

There are some interesting results here, but again mostly understandable...
_The results for [ ] are very similar to the last code test ( that for [ ] working in the Worksheet where the Named Range is the "correct" one, Worksheet “BracketWonk” ). (We do not get the many errors as in Range(“ above – we are referencing the Named Range, not looking for that named range in Worksheet “Stan” – but we did also not get those errors for the last code )
Only the first few lines referencing A1 are different. I thought This could be explained as the following: It would appear for
[ ]
the unqualified references are going the Worksheet scope of the Named Range in question. The thinking here was that Bill Gates or whoever, scoped A! To the Worksheet Stan in this case.
But the extra code lines that use my Worksheet scoped range "ShtCon" do not work. So it appeared to be something special going on here that a simple logic cannot explain.
But you note that
[ ]
is catching our Workbook scope Range Name “CoN”. Now this could be telling us that Bill Gates or whoever, scope A! To the Workbook.
But somehow there is an internal “memory” bit to do with Excel that knows which of the A!!it is... Or more complex , the same Template is slid across the same cell and this “allowed” in terms of such reserved Named Ranges for the all worksheets. This is just another way of explaining how Workbook scope works. ( Or for that matter how Worksheets scope works.. there the slide will not allow through when passing the Excel cell. The fact that the errors are bit different could be a further subtlety far to down for anyone to Foray . ( This could however be some of the "complex wiring referred to that is a contributing factor to that we mortals are not permitted to Set a New Worksheet Object )
http://www.excelforum.com/excel-prog...ml#post4386105

_ It Just to clarify: Lines 400 420 430 440 445 are all returning the Range Object. So is 410. But Line 410 is further trying to access the Name Object for that unqualified Range Object , which will , because it is unqualified here apply to Worksheets "Stan" which has no Name Object associated with it. Line 480 is OK as ws. Qualifies the Range Object to refer to ws. , that is Worksheets "BracketWonk"


We see an advantage of [ ] over Range(“ “) for the unqualified Worksheet case in that it is “evaluating” correctly the correct “Reference / Address / Object” from the Range Name. It is not being “pulled off course as it were as Range(“ “) is by virtual of first “evaluating fully” the string then applying that to the implied Worksheet ( the “wrong” one in this case )
But ... a very important result from the transgression to “Append I did it then **” was that when changing the Scope to Worksheet, our name which we “Worksheet Scopied” to the “correct” Worksheet “ShtCoN” could no longer be “got at” with [ ].
This is getting to the Bottom of the effect of Scoping.. the Scoping is talking about "from where" something that is "Scopied" may "get to it" .... Very important coming right back to the original problem here. ...
[ ] is quasi "taking a Dump " in the ( here our fist cell ) or dumping what it has directly there. it does not "fanny about" first making a String then loading it into a (Range) variable to get some direction. Translating to our case here [ ] drops the correct Named Range which is still going to the correct sheet, but from there is not allowed to reference it.
And note the
ws.[ShtCoN]
did "circumvent" and it did then "work"

The selected Worksheet did not effect any results for [ ]
_...........................

Evaluate(" ")
I repeated the experiments as shown in the given Link at the start of this Post, taking care to repeat them for one or the other Worksheets selected...
An important result with
ws.Application.Evaluate and Application.Evaluate
is that they are performing identical to
[ ],
except that the unqualified Range references are going to the Active Sheet rather than the Worksheet of the Code Module ( I think I may have heard that that is the case usually for “Application things” )

For Worksheet Evaluate(" ") Regardless of sheet selected, un surprisingly "All" is "Working" - going to or working on Worksheets "BracketWonk"
It does slightly beg the question as to whether we have a Worksheet Evaluate or rather ws.Evaluate is like ws.Range... In any case if we do have a Worksheet Evaluate then I can have all my versions of evaluate.. at least for now....

_...

Evaluate(“ “)
Gives Identical results to Application Evaluate and Worksheet Application Evaluate, suggesting that is the default.

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

Finally

_3) a Normal Code Module
Code Sub EvaluateRngNmesInWorksheet_InNormalCodeModule()
http://www.excelforum.com/showthread...t=#post4403783
http://www.excelforum.com/showthread...t=#post4404141
http://www.excelforum.com/showthread...t=#post4404146

“BracketWonk” Worksheet selected.

Everything is going to the “Worksheet “BracketWonk” . Almost everything is working except the discussed attempt to work on_.....
[strNme]
_...............as if it were an Object after the evaluation
_........................................


Worksheets “Stan” Selected

As expected only difference to last run is that unqualified references to A1 are going to the Active Sheet

More detailed results in the Link given above

'Code for last Post, Post 31


' and Reply 22 http://www.thespreadsheetguru.com/bl...o-named-ranges

Please Login or Register  to view this content.

Please ignore this post. I am only reading about evaluate vs square brackets, but I do not intend to enter into any conversations.

In fact I am only making this post because the excelforum website forced me to. It kept hiding the code examples and kept asking me to introduce myself. After 5 times I finally gave in.


I am a retired consultant. I now have time to pursue some vba topics that were too time consuming in the past.

Bob