Using Microsoft Scripting Runtime Dictionary to Store and then Retrieve Range Objects

[DocAElstein]

Hi,


. Since learning here the basic idea about Microsoft Scripting Runtime Dictionary, I have been able to use it successfully for sorting and re organizing lists, particularly where unique values come into play..
. I have read time and time again that all sorts of information can be stored using the Microsoft Scripting Runtime Dictionary, and so I am considering it as an alternative to storing complete Range info as I have been able to do, for example, by creating an Array of Range Objects.
. All my attempts have been unsuccessful to date. I expect I am missing some fundamental points but all googling suggests it should be possible but I have yet to see concrete examples of how to do it.

. Can anyone set me straight or point me in the right direction on this one?

. The following is a simplified example of one of my failed attempts:

. – I consider a Simple spreadsheet with 7 hyperlinks, one in each cells, for cells A21 to A27




[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

20​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

21​

[/td][td]

Apple fresh​

[/td][td] [/td][/tr]

[tr][td]

22​

[/td][td]

'Bierwurst' (coarse heat-treated sausage in bladder and smo​

[/td][td] [/td][/tr]

[tr][td]

23​

[/td][td]

'Breslauer' Lyonaise​

[/td][td] [/td][/tr]

[tr][td]

24​

[/td][td]

'Gaisburger Marsch' (potatoes with beef) (1)​

[/td][td] [/td][/tr]

[tr][td]

25​

[/td][td]

'Göttinger Blasenwurst'/Krakauer​

[/td][td] [/td][/tr]

[tr][td]

26​

[/td][td]

'Heaven and earth' (apples and pot.) with blood sausage (3)​

[/td][td] [/td][/tr]

[tr][td]

27​

[/td][td]

'Jägersoße' (thickened brown sauce with mushrooms) (5)​

[/td][td] [/td][/tr]

[tr][td]

28​

[/td][td] [/td][td] [/td][/tr]
[/table]

(In my actual sheet these are all hyperlinks which open a Web page when they are clicked on)

. With the code below I attempt to create a simple Microsoft Scripting Runtime Dictionary with 7 entries. The key I assign to the name (Value) of the Hyperlink in the cell, and I attempt to set the item to the Cell as a Range Object.
. There are 3 main parts to the code.
. 1). The background stuff to setting up the Dictionary. I have used this many times when using the Microsoft Scripting Runtime Dictionary for simple values and think it is still applicable to what I am trying to do here.
. 2) A loop to attempt to store the keys and corresponding Range Objects
. 3) A second loop to attempt to retrieve the range objects and put them in an Array of Range Objects

. Observations in the Watch Window show me clearly that 2) and 3) are only dealing with values rather than range objects.

( . 4) Finally the attempt to output the Range confirms that I only have values (Indeed I expect this simple method for outputting an Array in one go might not work fro an Array or range Objects and would possibly need to be replaced by some looping method anyway.?)

. Any help would be appreciated
Thanks
Alan Elston.

Full Code:

[color=green]'[/color]
[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]
 
[color=green]'[/color]
[color=darkblue]Sub[/color] ScriptingRuntimeDictionaryToStoreRanges()
 
[color=green]' 1)  'Part 1: Setting up Scriptimg Runtime Stuff-----------------------------[/color]
[color=green]' Attempting Using the Microsooft Scripting Runtime Dictionary to store Range Objects[/color]
 
[color=green]'We put the unique values now into a Dictionary for later look up purposes:[/color]
[color=green]'--requireslibrary reference to MS Scripting Runtime (Early Binding)-[/color]
[color=green]'        Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime[/color]
[color=green]'  ..Or crashes at next line.....[/color]
 [color=darkblue]Dim[/color] dicLookupTable [color=darkblue]As[/color] Scripting.Dictionary [color=green]'Data held with a unique "Key"or Part Number.[/color]
 [color=darkblue]Set[/color] dicLookupTable = [color=darkblue]New[/color] Scripting.Dictionary
[color=green]' The next two lines are an alternative called Late binding. (But note some Dictionary methods and properties( Such as at the end ####) will not work with it  - in those cases Early Binging must be used.[/color]
[color=green]'        Dim dicLookupTable As Object[/color]
[color=green]'        Set dicLookupTable = CreateObject("Scripting.Dictionary")[/color]
[color=green]' Late Binding is better when sharing files as I am here. Early Binding has the advantage that Excel intellisense[/color]
[color=green]' will then work for the Microsoft Scripüting Runtime stuff and give you suggestions after you type the .dot thing[/color]
 
     dicLookupTable.CompareMode = vbTextCompare [color=green]'Not quite sure wot this does yet[/color]
 
 [color=darkblue]Dim[/color] sKey [color=darkblue]As[/color] [color=darkblue]String[/color] [color=green]'Tempory string for part number or "key" - In this case the name of the thing in first column[/color]
[color=green]'.  A Dictionary in VBA is a collection of objects :you can store all kinds of things in it.[/color]
[color=green]'.  Every item in a Dictionary gets its own unique key, a very important characteristic. A Dictionary can only contain unique keys. That's why you can use the property .keys to create a list of unique strings, numbers or dates.[/color]
[color=green]'.  (Although the Dictionary has not been designed for that purpose it's a nice side effect.)[/color]
 [color=darkblue]Dim[/color] rItem [color=darkblue]As[/color] Range [color=green]'Tempory Range Object for Each cell. I am hoping that assigning a dictionary item to this will force the entry to be taken as a Range.[/color]
[color=green]'End of Part 1 initial set up Of Scripting Runtime------------------------[/color]
 
 [color=darkblue]Dim[/color] wksLkUp [color=darkblue]As[/color] Worksheet: [color=darkblue]Set[/color] wksLkUp = ThisWorkbook.Worksheets("debiNetEnglish") [color=green]'Give Abbreviation methods and properties of Object Worksheets (Intellisense then gives suggestions through use of . Dot[/color]
 [color=darkblue]Dim[/color] Hyp_LinkRow [color=darkblue]As[/color] [color=darkblue]Long[/color], LastRowHyp_Link [color=darkblue]As[/color] Long: [color=darkblue]Let[/color] LastRowHyp_Link = wksLkUp.Cells(Rows.Count, 1).End(xlUp).Row [color=green]'BoundLoopVariable (Rows Count), and Last entry in column 1 found by going to end of Spreadsheet, then coming back up until an entry is found, then get the row using row property from that Range (Cell) Object[/color]
 
 [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'LoopBoundVariableCount used in looping here and at end-----[/color]
 
 [color=green]'  2) 'First Loop: We Try to get the String key as the value/ name of the Hyperlink and the item as the Range Object[/color]
  [color=darkblue]For[/color] i = 21 [color=darkblue]To[/color] LastRowHyp_Link [color=darkblue]Step[/color] 1 [color=green]'Going down each Row..[/color]
    [color=darkblue]If[/color] wksLkUp.Cells(i, 1).Value <> "" [color=darkblue]Then[/color] [color=green]'Only look to assign a unique key if a Hyperlink is there[/color]
    sKey = wksLkUp.Cells(i, 1).Value [color=green]'Give each part to the tempory string variable for comparison below[/color]
         [color=darkblue]If[/color] [color=darkblue]Not[/color] dicLookupTable.Exists(sKey) [color=darkblue]Then[/color] [color=green]' check that the unique value does not already exist.[/color]
         [color=darkblue]Let[/color] dicLookupTable(sKey) = sKey [color=green]'Assigns the part Number a unique(Key) in the Dictionary[/color]
         [color=darkblue]Set[/color] rItem = wksLkUp.Cells(i, 1)
         [color=darkblue]Set[/color] dicLookupTable.Item(sKey) = rItem
         [color=darkblue]Else[/color] [color=green]'Do Noting, that is to say Do not give a unique part nimber if this unique value already exists[/color]
         [color=darkblue]End[/color] [color=darkblue]If[/color]
     [color=darkblue]Else[/color] [color=green]'Make no look for unique key or dictionary entry if cell is empty[/color]
     [color=darkblue]End[/color] [color=darkblue]If[/color]
  [color=darkblue]Next[/color] i
  [color=green]'End of first Loop--------------------------------------------------------[/color]
 
 [color=green]'  ..So the dictionary of unique Range Object items is Hopefully made! So we have all the info we need to make an output array[/color]
 [color=darkblue]Dim[/color] Results() [color=darkblue]As[/color] [color=darkblue]Variant[/color] [color=green]'Array for Output Results. I am hoping that Variant will allow Array to initially see The Dictionary Object and further more accept the (hopefully) given Range Object[/color]
 [color=darkblue]ReDim[/color] Results(1 [color=darkblue]To[/color] dicLookupTable.Count, 1 [color=darkblue]To[/color] 1) [color=green]'Set Row Size from Dictionary size. Must use ReDim as DIM only takes actual numbers, not variables[/color]
[color=green]' 3)  'Start of Second Loop. Attempting to retrieve The stored range objects int an Array[/color]
  [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] dicLookupTable.Count - 1 [color=darkblue]Step[/color] 1 [color=green]'Go throgth each Distionary entry (noting that the Dictionary index starts at 0, a common annoying practice with these things..)...[/color]
  [color=darkblue]Let[/color] Results(i + 1, 1) = dicLookupTable.Items(i)  [color=green]' Give the unique Range Object item to output array[/color]
  [color=darkblue]Next[/color] i
 [color=green]'End of Second Loop---------------------------------------------------------[/color]
 
[color=green]' 4)  'Finally Output Results-----------------------------------------------------[/color]
[color=darkblue]Let[/color] wksLkUp.Range("E21").Resize(dicLookupTable.Count, 1) = Results() [color=green]'Just a convenient way to put in the output in one go: Resize cell C2 to the size of the output array then make its values equal to the output array NOTE: #### This bit will not work with late binding![/color]
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'ScriptingRuntimeDictionaryToStoreRanges[/color]

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

Sihimpglified basic Code:

Sub ScriptingRuntimeDictionaryToStoreRangesSiHimpfGlified()
 
 Dim dicLookupTable As Scripting.Dictionary: Set dicLookupTable = New Scripting.Dictionary
     dicLookupTable.CompareMode = vbTextCompare
 
 Dim rItem As Range
 
  For i = 21 To Cells(Rows.Count, 1).End(xlUp).Row Step 1
    If Cells(i, 1).Value <> "" Then
    sKey = Cells(i, 1).Value
         If Not dicLookupTable.Exists(sKey) Then
         Let dicLookupTable(sKey) = sKey
         Set rItem = Cells(i, 1)
         Set dicLookupTable.Item(sKey) = rItem
         End If
     End If
  Next i
 
 ReDim Results(1 To dicLookupTable.Count, 1 To 1)
  For i = 0 To dicLookupTable.Count - 1 Step 1
  Results(i + 1, 1) = dicLookupTable.Items(i)
  Next i
 
 Range("E21").Resize(dicLookupTable.Count, 1) = Results()
 
End Sub

File (XL 2007 “PEListScriptingRuntime.xlsm” Macros in Module “MrExcelScrip_Dick”
https://app.box.com/s/lpjmw8wq9ld39hi5gplgtbfkxwiom02d




Both codes '—require library reference to MS Scripting Runtime (Early Binding)-
' Tools>>References>>scroll down and check the box next to Microsoft Scripting Runtime

For second code delete or comment out Option Explicit

 

[DocAElstein]

Hi,

. I have some follow up questions to this Thread
. I am answering the questions partly myself by currently investigating a dozen or so variations of my Final code from post #10. These codes involve many jumbled up mixtures of codes using Spreadsheet techniques, VBA Array techniques and Microsoft Scripting Runtime Dictionary techniques. All have the basic goal of taking an extremely large amount of data / information from the spreadsheet and putting it back in once used for some arbitrary process or alteration has been done….
. I am answering my questions partly with
. 1) speed tests / comparisons, and partly
.2 ) through watch window investigations of some mighty big Objects to compare what and how much info I have!!!!!
.
. With 2) I would like some help / comments from any experienced people looking in that have the time..

. In particular…
. At some point it is usually necessary to input the entire information contained in a large range of many cells. In my last code I was looping through cells to produce an Array of Range Objects. Each of the Range Objects then occupied a unique position in the Microsoft Scripting Runtime Dictionary.
.
. An alternative to how I looped in the code in post #10 would be to simply capture the entire Range as one single Range Object with a straight forward “one liner” code bit of the form

 [color=blue]Dim[/color] CapturedRangeObject [color=blue]As[/color] Range [color=lightgreen]'One Range Object for entire range[/color]
 [color=blue]Set[/color] CapturedRangeObject = RngName [color=lightgreen]'Direct assignmet to existing Dimensioned Array[/color]

.
………..So

Questions Specifically:
.2a). I am straining my eyes considerably currently in the Watch window to confirm that in total I do have the same amount of information. Could somebody in the know save me the pain and confirm that this is so (or tell me what bits are missing in the “one liner” approach as opposed to my original looping in cells as Range Objects one after the other..)

.2b). Assuming the answer to .2a) is Yes, I would like to consider another alternative for my comparison purposes that would split that One Range Object into Range Objects in an Array of Range Objects similar to those obtained by looping each cell as a range in one by one in the loop..
. Before I start attempting a code to do that.. does anyone know of one. (Might save me a year or two!! )…

2c). General question. Any comments or advice on advantages / pit-falls from using one method over the other…
(Speed I will be checking).
(one pit–full I found by painful experience… some (or is it All????) items are listed in the one Range Object in the order they came in the sheet…. !! I did a very un elegant code solution to that one here….
Sheet Range Object Item List Reorder [SOLVED]
.. which probably does away with any speed advantage of using the “one line” Range capture Object method….)
.. anyone know of a better way..?






. These general question are not too urgent and I am looking for general answers / comments to benefit from the experience of people “in the know”.

. Thanks again for the continued help I am receiving with my project.

Alan

.
.
.
.
.


……………………………………………….


p.s. …. While I am here…….

P.s. 1a). Just clarifying / clearing up a point of mine regarding the Watch Window representation of a MicrosoftScriptimeRuntime Dictionary of its items.
. It is clear now that VBA simply chooses to represent a Scripting.Dictionary Object in the watch window by its Key AND limits the number shown to 256.
. A simple two line code inclusion would help to clarify this:
. Put these two code lines in immediately after the code part creating the Full MicrosoftScriptimeRuntime Dictionary and then simply put a stop just after these lines;
. Put a watch on both the MicrosoftScriptimeRuntime Dictionary (here “dicLookupTable” ) and the Array “rResults “;
. run the code and then examine the two “rResults” and
“dicLookupTable”

 [color=lightgreen]'----------transfer range objects from dictionary to array of ranges in one go, typical Array assignment "One Liner" - The quick way to place data into an array is to dimension a variable ( for example rResults ) ....[/color]
 [color=blue]Dim[/color] rResults() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'... As a Variant and then the "Let rResults  = " code line Using a variant allows BOTH .-1) for capturing Objects, text, numbers, blanks  errors etc. from those cells and also 2) allows rResults to be an array variable.....[/color]
 [color=blue]Let[/color] rResults() = dicLookupTable.Items() [color=lightgreen]'... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses rResults()[/color]
 [color=lightgreen]'---------- NOTE: this gives automatically the 0 to ..   convention in rResults Array! and the output held in the Array is Variant type and is an OBJECT of the sort like a Range. (This compares with a similar code utilizing An Array of Ranges rather than the MicrosoftScriptimeRuntimeDictionary. (In the Array of Ranges case we could Dim as Range or Variant here. - Important however in that case is that the Dynamic rResults() Array and the (in that Array of Ranges case non dynamic) arrIn() Array are of the same type. - Either both Range to return an Array of element Type Range or both Variant to Return an Array of element Type Of Objects of the Range Sort[/color]

…

…
Again without ’Green Comments !!!

 [color=blue]Dim[/color] rResults() [color=blue]As[/color] [color=blue]Variant[/color]
 [color=blue]Let[/color] rResults() = dicLookupTable.Items()

.
…………………

P.s 1b). Note also the strange Phenomena which Kyle reminded of in Post #9…... That the keys themselves can be range Objects. When using this possibility ( which I did by accident use in my code in post #8!! And am currently doing on purpose as variations of my test codes…####) Then one has the possibility in the watch window to see the Range Objects as one sees these keys as Range objects, and a very peculiar thing.. Despite us having now Ranges Objects to show in the watch window rather than strings…. VBA shows all of them (at least over thousand that I have up to now tried) compared with only showing 256 when they are strings!?!?!!!?

(.P.s.2 ….####... I shall be posting some test code for speed comparisons with the codes I am writing here or elsewhere shortly and will include the links to those Threads if appropriate here. )

 

[DocAElstein]

Hi.,

Possibly this needs a new thread, but it appears very relevant to this Thread so initially I post as Follow up question..

Re Problem in retrieving data from a Microsoft Scriptime Runtime Dictionary held as a large Range Object containing multiple cells
.
. In attempting some further work to help me understand and possibly answer my questions 2) from post #11 I have hit a further specific problem that is holding me back.
. Can anyone help.
. I have tried to clarify the specific problem by writing two fairly simple code which demonstrate the problem.
. Basically I was attempting an intermediate “Bodged” set of codes where I replaced what I hope to be individual Range Objects with the Full Range Object containing all of those individual ranges
. ( I realize that here my dictionary items are all the same in the my first code below: this was intended as an intermediate step to take me further before I found a way within VBA to construct an Array of single Cell Range Objects from a Captured Object of a Range of multiply cells (my question 2b) Post #11) )
.
. Code 1 is a shortened version of codes I am using to Capture A Range of multiple cells in one go as a Range Object, and then similarly Paste the individual Ranges out in one go. Up to now the codes I was developing with help from this Thread Inputted the Large range of cells by looping through and bring in each cell as a Range one by one.
.
. I am currently stuck at why the lines indicated in purple below are failing. For comparison I show the corresponding code lines in orange in the second code which work. The second code is a simplified version of the codes developed to date in this thread based on looping cells in as Ranges one by one.

For demonstration purposes, both codes should take this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

21​

[/td][td]

Apple fresh​

[/td][td] [/td][/tr]

[tr][td]

22​

[/td][td]

'Bierwurst' (coarse heat-treated sausage in bladder and smo​

[/td][td] [/td][/tr]

[tr][td]

23​

[/td][td]

'Breslauer' Lyonaise​

[/td][td] [/td][/tr]

[tr][td]

24​

[/td][td]

'Gaisburger Marsch' (potatoes with beef) (1)​

[/td][td] [/td][/tr]

[tr][td]

25​

[/td][td]

'Göttinger Blasenwurst'/Krakauer​

[/td][td] [/td][/tr]

[tr][td]

26​

[/td][td]

'Heaven and earth' (apples and pot.) with blood sausage (3)​

[/td][td] [/td][/tr]

[tr][td]

27​

[/td][td]

'Jägersoße' (thickened brown sauce with mushrooms) (5)​

[/td][td] [/td][/tr]
[/table]

…

And from that produce this..



[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

21​

[/td][td]

Apple fresh​

[/td][td] [/td][td] [/td][td]

Apple fresh​

[/td][/tr]

[tr][td]

22​

[/td][td]

'Bierwurst' (coarse heat-treated sausage in bladder and smo​

[/td][td] [/td][td] [/td][td]

Bierwurst' (coarse heat-treated sausage in bladder and smo​

[/td][/tr]

[tr][td]

23​

[/td][td]

'Breslauer' Lyonaise​

[/td][td] [/td][td] [/td][td]

Breslauer' Lyonaise​

[/td][/tr]

[tr][td]

24​

[/td][td]

'Gaisburger Marsch' (potatoes with beef) (1)​

[/td][td] [/td][td] [/td][td]

Gaisburger Marsch' (potatoes with beef) (1)​

[/td][/tr]

[tr][td]

25​

[/td][td]

'Göttinger Blasenwurst'/Krakauer​

[/td][td] [/td][td] [/td][td]

Göttinger Blasenwurst'/Krakauer​

[/td][/tr]

[tr][td]

26​

[/td][td]

'Heaven and earth' (apples and pot.) with blood sausage (3)​

[/td][td] [/td][td] [/td][td]

Heaven and earth' (apples and pot.) with blood sausage (3)​

[/td][/tr]

[tr][td]

27​

[/td][td]

'Jägersoße' (thickened brown sauce with mushrooms) (5)​

[/td][td] [/td][td] [/td][td]

Jägersoße' (thickened brown sauce with mushrooms) (5)​

[/td][/tr]
[/table]

Code 1: Direct inputting Large range as One Range Object, storing that in a Microsoft ScriptimeRuntime Dictionary and attempting to retrieve data from that Microsoft ScriptimeRuntime Dictionary

[color=lightgreen]'[/color]
[color=blue]Sub[/color] MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItems21_27()
[color=lightgreen]'................   Ranges are held in a full MSRD[/color]
[color=lightgreen]'Less interaction with the spreadsheet,[/color]
[color=lightgreen]'Hopefully can get all infomation from that one big Range..Don't know for sure yet...Post #11 http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-2.html[/color]
[color=lightgreen]'So Temporarily put entire CapturedRangeObject in each item.....[/color]
 [color=blue]Dim[/color] wksLE [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLE = ThisWorkbook.Worksheets("LeftSpeedsEnglish")
 [color=blue]Dim[/color] RngName [color=blue]As[/color] Range, RngD [color=blue]As[/color] Range
 [color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color], rws [color=blue]As[/color] [color=blue]Long[/color]
 [color=blue]Let[/color] lr = 27
 [color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
 [color=blue]Set[/color] RngName = Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = Range("D" & sr & ":D" & lr & "")
 
[color=lightgreen]' 1)  'Part 1b: Setting up Scriptimg Runtime Stuff-----------------------------[/color]
[color=lightgreen]'--requires library reference to MS Scripting Runtime (Early Binding)-[/color]
 [color=blue]Dim[/color] dicLookupTable [color=blue]As[/color] Scripting.Dictionary [color=lightgreen]'Data held with a unique "Key"or Part Number.[/color]
 [color=blue]Set[/color] dicLookupTable = [color=blue]New[/color] Scripting.Dictionary
 
     dicLookupTable.CompareMode = vbTextCompare [color=lightgreen]'Not quite sure wot this does yet[/color]
 
[color=lightgreen]'End of Part 1b initial set up Of Scripting Runtime------------------------[/color]
 
 [color=lightgreen]'2a) Direct Array  Capture[/color]
 [color=blue]Dim[/color] CapturedRangeObject [color=blue]As[/color] Range [color=lightgreen]'One Range Object for entire range[/color]
 [color=blue]Set[/color] CapturedRangeObject = RngName [color=lightgreen]'Direct assignmet to existing Dimensioned Array[/color]
 
 [color=lightgreen]'2b) Part2b) Looping to put Range Objects in MRSD[/color]
 
        [color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
               [color=blue]If[/color] [color=blue]Not[/color] dicLookupTable.Exists(wksLE.Cells(rws, 1).Value) [color=blue]Then[/color] [color=lightgreen]'[/color]
               dicLookupTable.Add CapturedRangeObject.Value2(rws - sr + 1, 1), CapturedRangeObject
                  [color=blue]Else[/color]
               [color=blue]End[/color] [color=blue]If[/color]
        [color=blue]Next[/color] rws
 
[color=lightgreen]'End Part 2-----------------------------------------------------------[/color]
 
 [color=lightgreen]'3) Part 3)--transfer range objects from dictionary to array of ranges in one go,[/color]
 [color=blue]Dim[/color] rResults() [color=blue]As[/color] [color=blue]Variant[/color]
 [color=blue]Let[/color] rResults() = dicLookupTable.Items()
 [color=lightgreen]'End part 3)--- NOTE: this gives automatically the 0 to _ convention in rResults Array!.[/color]
 
 [color=lightgreen]'4) Part 4)---Produce output array by looping in VBA[/color]
 [color=blue]Dim[/color] arrOut() [color=blue]As[/color] [color=blue]String[/color]
 [color=blue]ReDim[/color] arrOut(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1)
    [color=blue]For[/color] rws = sr To lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
    [color=blue]Let[/color] arrOut(rws, 1) = CapturedRangeObject.Value2(rws - sr + 1, 1)
    [color=purple]'Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2(rws - sr + 1, 1)[/color]
    [color=purple]'Let arrOut(rws, 1) = rResults(rws - sr).Value2(rws - sr + 1, 1)[/color]
    [color=blue]Next[/color] rws
 [color=lightgreen]'End part 4)----------------------------------[/color]
 
 [color=blue]Let[/color] RngD.Value = arrOut()
 
 [color=blue]Set[/color] dicLookupTable = [color=blue]Nothing[/color] [color=lightgreen]'Genarally good practice to turn these thimgs off.[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItems21_27()[/color]

….






Code 2: Inputing Cells as Range Objects one by one through looping into a Microsoft ScriptimeRuntime Dictionary and retrieving data from that Microsoft ScriptimeRuntime Dictionary

[color=lightgreen]'[/color]
[color=blue]Sub[/color] MicrosoftScriptimeRuntimeDictionaryRangeOfRangesKeysItems21_27()
[color=lightgreen]'Similar to the Array of Range ranges, except that the Ranges are held in a full MSRD[/color]
[color=lightgreen]'Lots of interaction with the spreadsheet,[/color]
 [color=blue]Dim[/color] wksLE [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLE = ThisWorkbook.Worksheets("LeftSpeedsEnglish")
 [color=blue]Dim[/color] RngName [color=blue]As[/color] Range, RngD [color=blue]As[/color] Range
 [color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color], rws [color=blue]As[/color] [color=blue]Long[/color]
 [color=blue]Let[/color] lr = 27
 [color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
 [color=blue]Set[/color] RngName = Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = Range("D" & sr & ":D" & lr & "")
 
[color=lightgreen]' 1)  'Part 1b: Setting up Scriptimg Runtime Stuff-----------------------------[/color]
[color=lightgreen]'--requires library reference to MS Scripting Runtime (Early Binding)-[/color]
 [color=blue]Dim[/color] dicLookupTable [color=blue]As[/color] Scripting.Dictionary [color=lightgreen]'Data held with a unique "Key"or Part Number.[/color]
 [color=blue]Set[/color] dicLookupTable = [color=blue]New[/color] Scripting.Dictionary
 
     dicLookupTable.CompareMode = vbTextCompare [color=lightgreen]'Not quite sure wot this does yet[/color]
 
[color=lightgreen]'End of Part 1b initial set up Of Scripting Runtime------------------------[/color]
 
[color=lightgreen]'2) Part2) Looping to put Range Objects in MRSD[/color]
        [color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
               [color=blue]If[/color] [color=blue]Not[/color] dicLookupTable.Exists(wksLE.Cells(rws, 1).Value) [color=blue]Then[/color]
               dicLookupTable.Add wksLE.Cells(rws, 1).Value, wksLE.Cells(rws, 1)
                  [color=blue]Else[/color]
               [color=blue]End[/color] [color=blue]If[/color]
        [color=blue]Next[/color] rws
[color=lightgreen]'End Part 2-----------------------------------------------------------[/color]
 
 [color=lightgreen]'3) Part 3)--transfer range objects from dictionary items to Array[/color]
 [color=blue]Dim[/color] rResults() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'[/color]
 [color=blue]Let[/color] rResults() = dicLookupTable.Items()
 [color=lightgreen]'End part 3)--- NOTE: this gives automatically the 0 to _ convention in rResults Array!.[/color]
 
 [color=lightgreen]'4) Part 4)---Produce output array by looping in VBA[/color]
 [color=blue]Dim[/color] arrOut() [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'[/color]
 [color=blue]ReDim[/color] arrOut(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1)
 
    [color=blue]For[/color] rws = sr To lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
    [color=blue]Let[/color] arrOut(rws, 1) = [color=orange]rResults(rws - sr).Value[/color]
    [color=blue]Let[/color] arrOut(rws, 1) = [color=orange]dicLookupTable.Items(rws - sr)[/color]
    [color=blue]Next[/color] rws
 [color=lightgreen]'End part 4)----------------------------------[/color]
 
 [color=blue]Let[/color] RngD.Value = arrOut()
 
 [color=blue]Set[/color] dicLookupTable = [color=blue]Nothing[/color] [color=lightgreen]'Genarally good practice to turn these thimgs off.[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'MicrosoftScriptimeRuntimeDictionaryRangeOfRangesKeysItems21_27()[/color]

…

. I expect I have hit on some fundamental problem of retrieving information from a large Range Object of multiple cells within a Microsoft Scriptime Runtime Dictionary which for some reason I do not undertand does not seem to be a problem when the Microsoft Scriptime Runtime Dictionary is storing just one Range Object. This is where I need some help ​currently

Thanks
Alan



…….







P.s. I appreciate simply doing away with using the Microsoft Scriptime Runtime Dictionary in this simple case (again shown below in code 3 greatly simplified for clarity) would do away with the problem, but I am attempting to compare and become familiar with the various ways to handle, store and retrieve data

Code 3. Direct storing and retrieving from One Range Object

[color=lightgreen]'[/color]
[color=blue]Sub[/color] RangeObjectCapture21_27()
[color=lightgreen]'The entire range is capturead as One Range Object and its Value2 values accesed and used[/color]
[color=lightgreen]'then as before VBA Make Array for Output. Output in a "Output Value One Liner"[/color]
 [color=blue]Dim[/color] wksLE [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLE = ThisWorkbook.Worksheets("LeftSpeedsEnglish")
 [color=blue]Dim[/color] RngName [color=blue]As[/color] Range, RngD [color=blue]As[/color] Range
 [color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color], rws [color=blue]As[/color] [color=blue]Long[/color]
 [color=blue]Let[/color] lr = 27
 [color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
 [color=blue]Set[/color] RngName = Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = Range("D" & sr & ":D" & lr & "")
 
 [color=blue]Dim[/color] CapturedRangeObject [color=blue]As[/color] Range [color=lightgreen]'One Range Object for entire range[/color]
 [color=blue]Set[/color] CapturedRangeObject = RngName [color=lightgreen]'Direct assignmet to existing Dimensioned Array[/color]
 
 Dim arrOut() [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'We can use string as in the looping below as we are not relying on (/seeing) a range object to return us an array of values.[/color]
 [color=blue]ReDim[/color] arrOut(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1) [color=lightgreen]'Antoher way to look at it is to say we have a fixed, rather than dynamiy array, and as VBA does not allow a direct assigning one liner in this case it is also not necerssarily expecting to see an object. For Let it is likely to expect to see some value, and for Set an object (Would expect an Range Object if indicies of arrOut were so defined by arrOut() As String initially[/color]
 
    [color=blue]For[/color] rws = sr To lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
    [color=blue]Let[/color] arrOut(rws, 1) = CapturedRangeObject.Value2(rws - sr + 1, 1) [color=lightgreen]'Conventionally the Value2 values are in an Array starting at ( 1, 1) and extending over the two (in this case 1) dimensional array. So a bit of adjusting with the indicies is necerssary[/color]
    [color=blue]Next[/color] rws
   
 [color=blue]Let[/color] RngD.Value = arrOut()
 
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'RangeObjectCapture21_27()[/color]

 

[RoryA]

You have too many levels of indirection there, so you need to make it clear that Value2 is returning an array:

 Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2()(rws - sr + 1, 1)

 

[Kyle123]

Why do you even want to store cells in a dictionary - just their values is both more efficient and easier to get out

 

[DocAElstein]

Why do you even want to store cells in a dictionary - just their values is both more efficient and easier to get out

Hi Kyle.
. Thanks for your input here and previously, much appreciated.
. I apologies if my questions often come across somewhat academic and that I am (in the simplified cases here – for my project I may need all cell Info…) going about something in an unnecessary way. I know from personal experience how much that can annoy someone “in the know” …but
.1 ) As I mentioned I am Attempting to compare and become familiar with the various ways to handle, store and retrieve data
. 2) Sometimes going off in these “Wild tangents” I do learn some pretty useful things thanks to answers from people like you and…..
…
… ………………..Rory!…..


Thanks again
Alan

 

[DocAElstein]

You have too many levels of indirection there, so you need to make it clear that Value2 is returning an array:..........

…..Rory!,
. Thanks so much. That is brilliant and so quick..- (you had the answer there quicker than it took me to make a coffee!! – I went crazy over the whole weekend with this one!…)..
. I would like to understand this a bit more, (if for no other reason to avoid bugging you or others here with similar questions…)..
.
… Without your mod The error(Runtime ‘451’) roughly translated is that the “Let Procedure for the Property is not defined, and get procedure did not return an Object”…..and your explanation… “You have too many levels of indirection there, so you need to make it clear that Value2 is returning an array:”…. These two are leading me to think.. and therefore…..…
…..
. Some follow – up questions

. 1a) (Main question) Have I simply foolishly fallen into another “Implicit Default” trap again? – I always assumed that the parenthesis in defining
Dim AnArray() as ….
And the assigning
Let AnArray(somethingHere) = …..
Were the same. Clearly they are not..


I note that my final (Simple Array Code without Microsoft Scripting Runtime Dictionary) I may also add the extra parenthesis () )

    [color=blue]Let[/color] arrOut(rws, 1) = CapturedRangeObject.Value2[color=red]()[/color](rws - sr + 1, 1) [color=lightgreen]'Works[/color]
    [color=blue]Let[/color] arrOut[color=red]()[/color]( (rws, 1) = CapturedRangeObject.Value2[color=red]()[/color](rws - sr + 1, 1) 'Works

..Similarly I have just gone off randomly adding in an extra () in every code line of the form

    [color=blue]Let[/color] arrOut(rws, 1) = arrIn(rws, 1).Value

Which I have to hand…
. Modifications of the following form have proven to work up to now

    [color=blue]Let[/color] arrOut(rws, 1) = Left(arrIn[color=red]()[/color](rws, 1).Value, 4) [color=lightgreen]'Works[/color]
    [color=blue]Let[/color] arrOut[color=red]()[/color](rws, 1) = Left(arrIn[color=red]()[/color](rws, 1).Value, 4) 'Works

……………..

. 1b) .. I do note however, that changing a code line such as this

    [color=blue]Set[/color] arrIn(rws, 1) = Range("A" & rws & "")

To this

    [color=blue]Set[/color] arrIn[color=red]()[/color](rws, 1) = Range("A" & rws & "")

Puts a “Spanner in the works”, leading to an error later at following lines of this form

    [color=blue]Let[/color] arrOut(rws, 1) = rResults(rws, 1).Value
    [color=blue]Let[/color] arrOut()(rws, 1) = rResults(rws, 1).Value
    [color=blue]Let[/color] arrOut(rws, 1) = rResults()(rws, 1).Value
    [color=blue]Let[/color] arrOut()(rws, 1) = rResults()(rws, 1).Value

. (The error (Runtime 91) says that the Object variable or Block variable has not been defined)
.
… Is this simply that the extra () syntax for Let (which VBA may or may not guess you mean if you miss it out) is not therefore Set. That appears peculiar to me as the syntax we are talking about is referring to the Array arrOut()???? (I thought?)

………….

. 2) .. from my questions 2a-c) post #11. –summarized!-
. Do you coincidentally know of a code anywhere that would within VBA split One Range Object of a Range of multiple cells into an Array of Range Objects identical to an Array of Range Objects that are obtained by looping through the spreadsheet putting each range Object in the array one at a time (As done in the Initial codes in this Thread)

Many thanks again for the help (and patience!!)
Alan

 

[RoryA]

You do not ever need to do this:

arrOut()(rws, 1)

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. (this is one of those things I think you'll just have to accept)

 

[DocAElstein]

.......... (this is one of those things I think you'll just have to accept)

Ok,
- I have learnt that when you say it is so, then it is most likely something that can be “accepted” as fact!!

…. Would it be OK do you think as a compromise if I limited myself to always doing this but only on the RHS of the = …. and then for my obsession with at least some following ‘comment to write something like..
…’.. 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.

.. sorry I am just a ****** for trying to get the best understanding that my brain is capable of.

Thanks
Alan

 

[DocAElstein]

......

…. Would it be OK do you think as a compromise if I limited myself to always doing this but only on the RHS of the = …. and then for my obsession with at least some following ‘comment to write something like..
…’.. 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.

....

Thanks
Alan

. or maybe more correctly:
…’.. 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

 

[RoryA]

You can if you wish. I can't really think of a situation in which it wouldn't make more sense to return the array to a variable and then reference the elements through that variable, in which case you don't need to worry about it.