Range Dimensioning, Range and Value Referencing and Referring to Arrays

EDIT: I have seen my stupid mistake on that last one!!
….. L13c_RangeObjectCapture …

Please Login or Register  to view this content.

……. Spot where the idiot put
Let vTemp =
In the second of the first two codes. Version c is now one of the fastest!!!!!

. Still got my weird idea in the head….. but after the improvement in this last code to the speed – it is extremely unlikely to give any advantage. So even I may forget it (And save a years work!!)

. Amazing Rory. (This application in the above code of the intermediate variable vTemp is not exactly the same but very similar to how you originally presented it to me) ... Your persistence with me has got me at lot further anyway again..
. Alan

Code For two Questions in next Post (#19)… It is basically for demonstration purposes of Questions in next post (#19)

. It works on a lot of Rows in Column A to give an output in Column D (The Psuedo Code is:
Range D = Left ( Range A , 4 )
.
. The code is working on up to 33928 Rows.
. Input and output of this Form..

	A	B	C	D
19	Range For Input			Output Value
20
21	Apfel			Apfe
22	"Cordon bleu" vom Schwein, bofrost			"Cor
23	"Peperonata" Paprikazubereitung Vogeley GV			"Pep
24	"Pomona" Tomtenpόree-Konzentrat Vogeley GV			"Pom
25	'Biene-Maja' Banane-Mandel Fruchtschnitte, Evers Naturkost			Bie

Code:

Please Login or Register  to view this content.

Hi romperstomper (or anyone else kindly taking the time to looking in to the latest ramblings of an Excel inflicted idiot)

…… I am afraid I am back again here. I have the last stuff 98% clear now. If I can clear up the last 2% that is nagging me then I will hopefully most just come back here with some useful contributions (rather than questions) with some codes I have “waiting in the wings..” Comparing different range referencing and data thereof manipulating etc…
. Anyways I will try and simplify and summaries my two questions…(Note Maybe the answer to Question #2) automatically answers #1) at the same time…!?!...
. Here is a sample code , simplified to try and help explain my problem…. ###I appreciate there are many unnecessary and unneeded steps in the program. They are simply there to help clarify my two questions.. (###And I have it finally in my thick head that working with values generally is both more efficient and easier to get out… and I have many hours (Or days) of Speed tests to prove that!!!
(. The Full code is Shown in last Thread due to Excel Forum Editor size constraints..)

. It works like this
.a) Captures a whole Range as a Big Range Object with all data that entails in one neat “one liner”...
.b) Does another neat one liner to pull out a particular property from the Range object.
( ###Note I realize a) and b) could (and should!!) be replaced by one line in this simplified example… for demonstration purposes here I am doing it in this way..?!??!
.c) In a loop an output Array is produced within VBA. For demonstration and comparison the vTempIntermediateArray is and / or is not used within the loop.
.d) Finally and not important to the questions in hand the Array of Values is outputted in the typical one liner exclusively allowed (I think?) to values only

(Some processing times for different code line variations in the loop for producing the output are shown in the comments and table below. These refer to 2 sheets with approximately 1654Rows and 33928 Rows (of Hyperlinks in this case.) )

(Full Code is in last Post #18)
Extract: (Pseudo Code)

General outlook of Code Line (pseudo code Line)’: Seconds 1654Rows ; Seconds 33928Rows

1 arrOut(___) = Object.Value2(_________) ‘: 3.85 ; 1651.5
'Works
2 arrOut(___) = Object.Value2 () (_________) ‘: 3.85 ; 1667
'Works
3 arrOut () (___) = Object.Value2(_________) ‘: 3.85 ; 991.5
'Returns Empties
4 arrOut () (___) = Object.Value2 () (_________)’: 3.85 ; 1696
'Returns Empties


5 arrOut(___) = vTemp(_________)’: .018 ; 0.325
'Works
6 arrOut(___) = vTemp () (_________)’: --- ; ---
'wont work ????? #1 Error 9: Index out of valid Range
7 arrOut () (___) = vTemp(_________)’: .089 ; 30.5
'Returns Empties.
8 arrOut () (___) = vTemp () (_________)’: ---- ; ----
'wont work ????? #1 Error 9: Index out of valid Range


????? # 1)

. One last bit I do not quite get is that this new vTemp(_____) will not tolerate such an assigning
vTemp () (______)
… … I have been happily for learning purpose including the extra () where it is not needed and seeing, for example occasionally increased times as a result. All suggesting to me that it is some sort of explicit that by the implicit default almost always gets it right and so therefore is only occasionally needed….
('.. because of how VBA “works”, This extra () will ensure that indices are always returned to the resulting array (and not occasionally, for example, arguments ) by any following method or property, or passed to any method or property)

????? # 2)
I had mistakenly thought this
Let arrOut(rws, 1) =
.. and this
Let arrOut () (rws, 1) =
Were the same. (Again Thinking that it is some sort of explicit that by the implicit default almost always gets it right and so therefore is only occasionally needed……etc..) But in fact I was mistaken. The second sort of does not work and seems to mostly return an empty arrOut
.. so I am not quite “with it” on those two points yet.. ?? (I do grasp that it is not always needed and mostly I thought understand why. I do not understand why it messes things up sometimes by being there ?


… I realize “As a beginner I may be going a bit above my head!!.”....but I have a problem going further without at least having a bit of understanding of wot and why…
…..
. There is no rush in giving an answer here. I would be very grateful for explanations to the differences in workings in as much detail as possible to the 8 colorful code lines. And I think it could be a very useful contribution to understanding the general Theme of Range Referencing to Arrays.
. I am answering the questions myself in parallel with endless empirical measurements, but another angle from someone in the know would be very welcome..


.Many thanks again
. Alan..

It works when using .Value() since .Value is a function/property. A function that returns an array - the same thing is true with other functions that return arrays:

Please Login or Register  to view this content.

All you are really doing is accessing the returned array without assigning it to a variable first.

vTemp isn't a function, it is an array - it doesn't return anything so additional parentheses will do nothing

Thanks Kyle.
. An extra angle from someone in the know is always extremely helpful. I think it is helping me to finally get this one..It is helping me get the full picture. And it does tie up with an explanation already given to me. (Just hearing something similar helps to knock it in..)

Originally Posted by romperstomper

You do not ever need to do this:

Please Login or Register  to view this content.

The parentheses are only necessary for the .Value2 call to indicate that the last part is intended to be indices to the resulting array and not arguments passed to the Value2 method itself.

… a couple of follow up questions..

. 1 ) Is my last Red Code line working by Luck (The implicit default getting it right?). The original extra () thing came up because in something similar here (Post #13)
http://www.mrexcel.com/forum/excel-q...bjects-2.html?
Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2 () (rws - sr + 1, 1)

It would not work without it?
.
.
.
.

. 2 ) Your demo was good (Once I got it in my head what you meant..)…

Please Login or Register  to view this content.

… so how does this sound for an explanation here ?:

The second parenthesis (1) (2) (3) (4) bit is the usual indicie for the Array. The first parenthesis just “belongs” to the function/property. By luck in 1) VBA just guessed right with
CapturedRangeObject.Value2(rws - sr + 1, 1)
But by bad luck did not guess right with
Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2 (rws - sr + 1, 1)
….. ?

(P.s. Here – I saw some interesting speed improvements sometimes when I relied on the implicit guessing.. so sometimes it is maybe worth a try!!)

. 3a) I think if I let it go painfully through my head for a week I may realize why the extra () on the LHS returns empties.. If you have time can you spare me the pain and explain that one.

. 3b) Probably related to .3a) When I give vTemp additional parentheses it does not do nothing but rather gives Error 9: Index out of valid Range. Again if I go away for another week experimenting putting various indicies in there it may get me further to fully understand…. Any ideas on that one..(While I now play around experimenting….



Thanks
Alan..

Since it's a read only getter,I suspect the compiler interprets:

Please Login or Register  to view this content.

As:

Please Login or Register  to view this content.

Dodgy test to prove:

Please Login or Register  to view this content.

Originally Posted by Kyle123

Scrap my earlier post, I'm wrong but the behaviour was right

I'd never tried it before, but it appears that an specifying an indices immediately after an array accesses the given element - this acts as a readonly getter, not a setter. So your code simply doesn't work as implicitly accessing an array in that way is read only.

…. I am not quite with it here…

… I thought we were all a bit in agreement with the idea…
.". The parentheses are only necessary for the .Value2 call (.Value is a function/property. ) to indicate that the last part is intended to be indices to the resulting array and not arguments passed to the Value2 method itself.
. vTemp isn't a function, it is an array - it doesn't return anything so additional parentheses will"……………………… ah I see Yous have have just answered again… i will check that out

Originally Posted by Kyle123

.Value / .Value2 is indeed a function/property and my answer was correct, however you can use that syntax on any array as a getter, just not a setter

?? does that mean
= vTemp() (____)
is "working", but we are not quite sure what it is doing
and
Let arrOut()(_____) =
is also "working" but we are not quite sure what that is doing either??


..................


.... I think it is still not clear if this workijng
= CapturedRangeObject.Value2(rws - sr + 1, 1)
and this not
= dicLookupTable.Items(rws - sr).Value2 (rws - sr + 1, 1)
is still down to the luck of VBA guessing right sometimes??

. I think I will leave my thoughts on the () (_____) idea and the Thread as unsolved for a bit. Possibly some of the codes and experiments** I am doing in the next day or two may clear it up in my mind a bit. Then I will report back and possibly mark the Thread as solved..


. I think for the time being a consolidation of the thoughts here is something like the following listed .1) – .3) below, (but it is not telling clearly the full story (for me) yet..)…..

. 1) After a Property or (as in the case of .Value2) a Method, an extra parentheses may be needed. This is some sort of direction to make sure the next bit (______) is taken as indices (I struggle to see what else it could expect in the case of.Value2.. I cannot see any arguments for value2, but maybe VBA just looks often for some arguments (everywhere / anywhereas it feels like!?) )

. 2) Sometimes you do not need it, and maybe VBA is just then guessing right with its implicit default. It seems initially to me that it is Always worth checking that if speed is an issue. Some preliminary experimenting suggest to me that maybe the implicit default is sometimes faster.. (.. I was wondering if there is a parallel with the idea of relying on an implicit default with (Pseudo Code) a quicker working one liner
Copy Destination:-
. compared to a sometimes slower 2 line equivalent
Copy
Paste Special Arguments:=
….….?.)
… see RickXL Post #6
http://www.mrexcel.com/forum/excel-q...-formulas.html
…(In this case Rick explained that a bit is missed out by the Copy destination:-(The Clipboard here) ). )

--- maybe then my problem with this working
= CapturedRangeObject.Value2(rws - sr + 1, 1)
but this not
= dicLookupTable.Items(rws - sr).Value2 (rws - sr + 1, 1)
….Is that VBA has a default path / way for a Range Object, but does not have this for a Microsoft Scripting Runtime dictionary .item … (..just a quick note here.. I am seeing some amazing quick results when taking the default way for a Microsoft Scripting Runtime dictionary .key – when the key is made to be a full Big Range in conjunction with an VTempIntermediateArray of Range Objects….see Code G15 variation (34 G15) here..Post #88
http://www.mrexcel.com/forum/excel-q...vlookup-9.html
….….?.)


. 3) There is some thought that for a non dynamic array the first ( ) will always expect the indices of the array in it. So that kind of clears up why my vTemp not liking a () (_____). What exactly happen when you do a () (___) for an Array is not yet clear (to me)

Alan.

**P.s. There may be some “Stuff” here worth a quick read….
https://fastexcel.wordpress.com/?s=Text+vs

Hi kyle, if you happen to look in here again..(Or anyone else dropping by…)

… I’m afraid I am still trying to “set” or “get” a few things clear in my mind…

. 1) …..”getter” or “setter)
. …

Originally Posted by Kyle123

.Value / .Value2 is indeed a function/property and my answer was correct, however you can use that syntax on any array as a getter, just not a setter

. Could you elaborate a little on that? Maybe you are answering one of my previous questions there, but I am not too familiar with what the term setter and getter is saying here.

. 2) .Value and .Value2 property or method or function.
. A trivial question maybe but it can throw a beginner off-course..
. I have seen experienced users refer to these two things as either property or method or function. And the Object window F2 is not a great help : Often the same name is given to properties and methods/Functions, which means you often need to know the answer before knowing where to look for the answer!!!!
. I note that .Value is not to be seen in the Watch Window for a big Range Object. .Value2 is. So maybe .Value is a Method which is used to get something similar to .Value2 which is then maybe a Property?. Charles Williams is not sure and did some experiments like I did to try and find out. He found that Value2 is always better and quicker especially when used with the variant Array idea similar again to what I have been finding. So maybe it all ties up.
( I expect you may say I should look in the Object Library. But I have been caught out in the past on this one as Methods and Properties sometimes have the same names and you then need to know the answer first to know where to look for the answer!! )

.3) A quick yes to this would do. Have I been continually fooling myself into thinking I have / can “capture” a big Range with all it’s info in it with…

Please Login or Register  to view this content.

But in fact I have nothing but a whole lot of “Directions” or “paths” or “references” which always take me to that Range and hence result in an interaction with the worksheet.?
(…I have been told this I think a few times, for example..

Originally Posted by romperstomper

Whenever you interacted with a range object in the array, you would be interacting with the worksheet, so there would be no benefit to having an array of range objects rather than one…
… .

… but some of my weird experiments have turned up some anomalies… … using an intermediate step with a code which uses an array of range objects but stores them as full Objects as Keys along with an variant itermediateArray of range Objects almost works as fast as when ignoring this intermediate step which otherwise greatly increases times.. I have no idea what use this could be.. … yet!!.….?.)


. No Rush on any answers here, rather an in depth answer should anyone have the time.
. Thanks, sorry for continual questions here, I hope to clear this one up in my head soon!
Alan

………………………………………………………………………………………

Hi kyle,
. Thanks very much for the quick and very detailed reply. I realize I am laboring a few points in my quest to understand, but as things look with this stuff they are hardly learnable form books (at least some points), so I am very grateful for your continued help and patience here and in other Threads

Originally Posted by Kyle123

..1. Getter Setter is really Class/Object terminology, it just means whether you can set or get a particular value, for example:
Class1
…………

Module
……

Here we can get and set/let the value of obj. Try commenting out the Let property and try running the code again
……..

. I am clearly way out of my depth here. I understand nothing… but I will keep at it… I have at least for the first time in my life made a “Class” Module… I have done the experiment you suggested and am making further experiments to try to understand what you have said here..

. If you could spell out a bit clearer in more basic terms what you are saying I would be grateful. But please do not put yourself out. I expect I am way out of my “Class” here. I only have a very basic Idea of Objects and Classes. I did watch a you tube video on this some time back…
https://www.youtube.com/watch?v=Cvug...2mkJC&index=68
I guess I need to watch that again a few times when I can and then look again at what you have kindly written…..

………………………..

Originally Posted by Kyle123

… 2. It's a Property according to the Object browser
…….

. Thanks. These things are not as obvious as they should be in books or the object browser. So It getting the answer as much as possible to a seemingly trivial question from a Profis is very helpful. And indeed necessary. Clearly such things are becoming less and less precisely defined as we “lose control” of how our software is working due to it’s complexity.
. BTW (I assume you are saying both Value and Value2 are properties.?.)

…………………….

Originally Posted by Kyle123

… 3. Yes, you just have a reference/path to the range on the worksheet, so you will still access the worksheet…...

.. Great. I had asked many times this and had answers which almost said this (or maybe did exactly say it!), .. . I think I finally have knocked this one into my head now… (At least until I maybe stumble upon another anomaly!!)


.

Originally Posted by Kyle123

… ….. Value/2 returns an array which is why is is much faster to work with.

… Ties up with what I have been reading, for example the Charles Williams „Stuff“ on this..


.

Thanks again. I will keep at it and try to bother you as little as possible.
. Alan
…