DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
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
(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:
...............
Sihimpglified basic Code:
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
. 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
A | B | |
20 | ||
21 | Apple fresh | |
22 | 'Bierwurst' (coarse heat-treated sausage in bladder and smo | |
23 | 'Breslauer' Lyonaise | |
24 | 'Gaisburger Marsch' (potatoes with beef) (1) | |
25 | 'Göttinger Blasenwurst'/Krakauer | |
26 | 'Heaven and earth' (apples and pot.) with blood sausage (3) | |
27 | 'Jägersoße' (thickened brown sauce with mushrooms) (5) | |
28 |
(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:
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:
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 SubFile (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
