[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] G1_SimpleLoop() [color=lightgreen]'Simplist Loop[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch") [color=lightgreen]'Give abbreviations the Methods, properties, sub-Objects through dot of Worksheets Object[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color], rws [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'last row and rows in sheet( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) -Most smaller numbers, Byte, Integer, Single are converted in computer to long so no advantage of Dim to smaller Type here) >>> Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. http://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-3.html[/color]
[color=blue]Let[/color] lr = wksLG.Cells.Find(What:="*", after:=wksLG.Cells(1, 1), lookat:=xlPart, LookIn:=xlFormulas, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row [color=lightgreen]'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), sercching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method here for fun- finds last row in sheet rather than row for last entry in particular cell[/color]
[color=blue]Let[/color] lr = wksLG.Cells(Rows.Count, 1).End(xlUp).Row [color=lightgreen]'Better alternative for single column work. - Starting at last Spreadsheet Row in column1, quasi "go back up" / return the last cell with entry in it as a Range object using .End Property and obtain rows from the .Rows Property of that returned Range Object[/color]
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21 [color=lightgreen]'Start row of data[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
[color=blue]Let[/color] wksLG.Range("D" & rws & "").Value = Left((wksLG.Range("A" & rws & "").Value), 4)
[color=lightgreen]'Let wksLG.Range("D" & rws & "").Value = GetLeftstr((wksLG.Range("A" & rws & "").Value), 4)[/color]
[color=lightgreen]'wksLG.Range("D" & rws & "").Value = Left$((wksLG.Range("A" & rws & "").Value), 4)[/color]
[color=blue]Next[/color] rws
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'SimpleLoop[/color]
'
'
[color=blue]Sub[/color] G2_TypicalEvaluateAlternativeToLoop()
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch") [color=lightgreen]'Give abbreviations the Methods, properties, sub-Objects through dot of Worksheets Object[/color]
[color=blue]Dim[/color] RngName [color=blue]As[/color] Range, RngD [color=blue]As[/color] Range [color=lightgreen]'Data Column range and an offset Column for results.. Give abbreviations >>>[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'last row ( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) -Most smaller numbers, Byte, Integer, Single are converted in computer to long so no advantage of Dim to smaller Type here)[/color]
[color=blue]Let[/color] lr = wksLG.Cells(Rows.Count, 1).End(xlUp).Row [color=lightgreen]'Better alternative for single column work. - Starting at last Spreadsheet Row in column1, quasi "go back up" / return the last cell with entry in it as a Range object using .End Property and obtain rows from the .Rows Property of that returned Range Object[/color]
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21 [color=lightgreen]'Start row of data[/color]
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "") [color=lightgreen]'...>>>>..the Methods, properties, sub-Objects through dot of Worksheets Object[/color]
[color=lightgreen]'Let RngD.Value = Evaluate("if(Row(21:1674),LEFT(" & RngName.Address & ",4))")[/color]
[color=blue]Let[/color] RngD.Value = Evaluate("if(Row(" & sr & ":" & lr & "),LEFT(" & RngName.Address & ",4))")
[color=lightgreen]'Let RngD.Value = Evaluate("if(Row(" & sr & ":" & lr & "),GetLeftstr(" & RngName.Address & ",4))")[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'TypicalEvaluateAlternativeToLoop[/color]
[color=lightgreen]'[/color]
'
[color=blue]Sub[/color] G3_4_VBAWithFormula()
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[color=blue]Dim[/color] RngD [color=blue]As[/color] Range, RngName [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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngD = Range("D" & sr & ":D" & lr & ""): [color=blue]Set[/color] RngName = Range("A" & sr & ":A" & lr & "")
[color=blue]With[/color] RngD
[color=lightgreen]'.Formula = "=Left(" & RngName.Address & ",4)"[/color]
[color=lightgreen]'.FormulaR1C1 = "=left(R[0]C[-3],4)"[/color]
[color=lightgreen]'.Formula = "=GetLeftstr(" & RngName.Address & ",4)"[/color]
.FormulaR1C1 = "=GetLeftstr(R[0]C[-3],4)"
[color=blue]End[/color] [color=blue]With[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'VBAWithFormula()[/color]
[color=lightgreen]'[/color]
[color=blue]Sub[/color] G5_6_VBAFormulaLoop() 'Looping the [color=blue]For[/color]mulas in
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch") [color=lightgreen]'Give abbreviations the Methods, properties, sub-Objects through dot of Worksheets Object[/color]
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row [color=lightgreen]'Better alternative for single column work. - Starting at last Spreadsheet Row in column1, quasi "go back up" / return the last cell with entry in it as a Range object using .End Property and obtain rows from the .Rows Property of that returned Range Object[/color]
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21 [color=lightgreen]'Start row of data[/color]
[color=blue]Dim[/color] RngD [color=blue]As[/color] Range, RngName [color=blue]As[/color] Range
[color=blue]Set[/color] RngD = Range("D" & sr & ":D" & lr & ""): [color=blue]Set[/color] RngName = Range("A" & sr & ":A" & lr & "")
For rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
[color=lightgreen]'wksLG.Range("D" & rws & "").Formula = "=Left(" & RngName.Address & ",4)"[/color]
[color=lightgreen]'wksLG.Range("D" & rws & "").FormulaR1C1 = "=left(R[0]C[-3],4)"[/color]
[color=lightgreen]'wksLG.Range("D" & rws & "").Formula = "=GetLeftstr(" & RngName.Address & ",4)"[/color]
wksLG.Range("D" & rws & "").FormulaR1C1 = "=GetLeftstr(R[0]C[-3],4)"
[color=blue]Next[/color] rws
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'VBAFormulaLoop[/color]
[color=lightgreen]'[/color]
'
[color=blue]Sub[/color] G7_EvaluateRoaryALeftFunction()
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=blue]Let[/color] RngD.Value = Evaluate("RoaryLeftPubic(" & RngName.Address & ",4)")
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'EvaluateRoaryALeftFunction()[/color]
[color=lightgreen]'[/color]
[color=lightgreen]'[/color]
'
[color=blue]Sub[/color] VBALeft() 'DON'T WORK
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[color=blue]Dim[/color] RngD [color=blue]As[/color] Range, RngName [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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngD = Range("D" & sr & ":D" & lr & ""): [color=blue]Set[/color] RngName = Range("A" & sr & ":A" & lr & "")
[color=lightgreen]'Let RngD.Value = Left(RngName, 4) Wont work..RngName is nat a string as required in first argument![/color]
[color=blue]End[/color] [color=blue]Sub[/color]
[color=lightgreen]'[/color]
[color=blue]Sub[/color] G8_ApplicationWorksheetRoaryFunctionLeft()
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=blue]Let[/color] RngD.Value = RoaryLeftPubic(RngName, 4)
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'ApplicationWorksheetRoaryFunctionLeft()[/color]
[color=lightgreen]'[/color]
'
'
''
[color=blue]Sub[/color] G9_VBAArrayOfRanges1a()
[color=lightgreen]'Loop Range of Ranges. VBA Make Array for Output. Output in a "Output Value One Liner"[/color]
[color=lightgreen]'Typical application requiring seperate loops for the (often differnt sized) input and output arrays[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=blue]Dim[/color] arrIn() [color=blue]As[/color] Range [color=lightgreen]'Variant 'In the looping, the Set would appear to be responsible for bringing **Ranges into the Array The variable type remains as defined here: Either a range for As Range or a Variant Object for [color=blue]As[/color] Variant[/color]
[color=blue]ReDim[/color] arrIn(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1) [color=lightgreen]'We must give array a size as we give specific value in a loop. ReDim Must be used rather than Dim as [color=blue]Dim[/color] only takes Numbers not variables[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=blue]Set[/color] arrIn(rws, 1) = Range("A" & rws & "")
[color=lightgreen]'Set arrIn(rws, 1) = wksLG.Range("A" & rws & "") 'This would be tolerate**[/color]
[color=blue]Next[/color] rws
Dim arrOut() As [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 rturn us an array of values.[/color]
[color=blue]ReDim[/color] arrOut(sr [color=blue]To[/color] lr, 1 To 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 [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=lightgreen]'Let arrOut(rws, 1) = arrIn(rws, 1).Value 'Works 'VBA would appear to recognise this array element as A range as it gives me the Method, Object and property selkection to choose from via intellisense brought in by typing . Dot after the Array elemant[/color]
[color=lightgreen]'Let arrOut(rws, 1) = arrIn()(rws, 1).Value 'Works'.. 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.[/color]
[color=lightgreen]'Let arrOut()(rws, 1) = arrIn()(rws, 1).Value 'Doesn't - Rory said it aint ever needed on the LHS, so it aint[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(arrIn(rws, 1).Value, 4) 'Even A function on the RHS of the = is accepted Here. So again for a non dynamic Array it is not relying on what the function to "define" the type[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(arrIn()(rws, 1).Value, 4) [color=lightgreen]'Works[/color]
'Let arrOut()(rws, 1) = Left(arrIn()(rws, 1).Value, 4) 'Works
[color=blue]Next[/color] rws
[color=lightgreen]' Possibility to Note here: Dim IntermediateArray() [color=blue]As[/color] Range 'Variant 'Note RoryA Post #2You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic AND THE TWO ARRAYS ARE THE SAME TYPE.[/color]
[color=lightgreen]' ' Re[color=blue]Dim[/color] IntermediateArray(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1)'THIS Line Will NOT Work: RoryA Post #2 http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html - You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic[/color]
[color=lightgreen]' Let IntermediateArray() = arrIn()[/color]
[color=blue]Let[/color] RngD.Value = arrOut() [color=lightgreen]'This is the typical allowed one liner to give values of a Range the values in an Array (VBA does not allow this one liner in any way for assigning the ranges. There is no way of getting over having to paste each Range Object in a loop.[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'VBAArrayOfRanges1a[/color]
[color=blue]Sub[/color] G10_VBAArrayOfRanges1aa()
[color=lightgreen]'Loop Range of Ranges. VBA Make Array for Output. Output in a "Output Value One Liner"[/color]
[color=lightgreen]'Typical application requiring seperate loops for the (often differnt sized) input and output arrays[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
Dim arrIn() As Range [color=lightgreen]'Variant 'In the looping, the Set would appear to be responsible for bringing **Ranges into the Array The variable type remains as defined here: Either a range for [color=blue]As[/color] Range or a Variant Object for [color=blue]As[/color] Variant[/color]
[color=blue]ReDim[/color] arrIn(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1) [color=lightgreen]'We must give array a size as we give specific value in a loop. ReDim Must be used rather than [color=blue]Dim[/color] as [color=blue]Dim[/color] only takes Numbers not variables[/color]
Dim EmtpyInArrIndiciesCount As Long: [color=blue]Let[/color] EmtpyInArrIndiciesCount = 0 [color=lightgreen]'Here possibility to Redim ArrIn if any extra conitions, such as MSRD etc unique entries being ommited, leading to unused indicies.. then###[/color]
[color=blue]For[/color] rws = sr To lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=blue]If[/color] Range("A" & rws & "").Value <> "" [color=blue]Then[/color]
[color=blue]Set[/color] arrIn(rws - EmtpyInArrIndiciesCount, 1) = Range("A" & rws & "") [color=lightgreen]'The extra " - Emtp...." will take inidie niumber back accordingly to fill just after next free indicie[/color]
[color=lightgreen]'Set arrIn((rws - EmtpyInArrIndiciesCount, 1) = wksLG.Range("A" & rws & "") 'This would be tolerate**[/color]
[color=blue]Else[/color]
[color=blue]Let[/color] EmtpyInArrIndiciesCount = EmtpyInArrIndiciesCount + 1
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Next[/color] rws
Dim arrOut() As [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 rturn us an array of values.[/color]
[color=blue]ReDim[/color] arrOut(sr To lr - EmtpyInArrIndiciesCount, 1 To 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 - EmtpyInArrIndiciesCount [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges'The extra - Emtp.... prevents looping to far[/color]
[color=lightgreen]'Let arrOut(rws, 1) = arrIn(rws, 1).Value 'Works 'VBA would appear to recognise this array element as A range as it gives me the Method, Object and property selkection to choose from via intellisense brought in by typing . Dot after the Array elemant[/color]
[color=lightgreen]'Let arrOut(rws, 1) = arrIn()(rws, 1).Value 'Works'.. 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.[/color]
[color=lightgreen]'Let arrOut()(rws, 1) = arrIn()(rws, 1).Value 'Dont Work gives empty but Rory said it aint ever needed on the LHS, so it aint[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(arrIn(rws, 1).Value, 4) [color=lightgreen]'Even A function on the RHS of the = is accepted Here. So again for a non dynamic Array it is not relying on what the function to "define" the type[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(arrIn()(rws, 1).Value, 4) 'Works[/color]
[color=lightgreen]'Let arrOut()(rws, 1) = Left(arrIn()(rws, 1).Value, 4) 'Works[/color]
[color=blue]Next[/color] rws
[color=lightgreen]' Possibility to Note here: Dim IntermediateArray() As Range 'Variant 'Note RoryA Post #2You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic AND THE TWO ARRAYS ARE THE SAME TYPE.[/color]
[color=lightgreen]' ' ReDim IntermediateArray(sr To lr, 1 To 1)'THIS Line Will NOT Work: RoryA Post #2 http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html - You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic[/color]
[color=lightgreen]' Let IntermediateArray() = arrIn()[/color]
[color=blue]Let[/color] RngD.Resize(lr - sr - EmtpyInArrIndiciesCount + 1, 1).Value = arrOut() [color=lightgreen]'This is the typical allowed one liner to give values of a Range the values in an Array (VBA does not allow this one liner in any way for assigning the ranges. There is no way of getting over having to paste each Range Object in a loop. 'The extra size corrections prevents a NoValue error placed in last cell if trying to assign to indicies that are not there[/color]
[color=lightgreen]'' 'Long winded alternative to give an empty indicie rather than no indicie to prevent6 output error[/color]
[color=lightgreen]'' ' 1)Because we assign the Array using the Value property of a range, the returned array has lower bounds[/color]
[color=lightgreen]'' ' of 1 for each dimension. When we redim it without providing lower bounds explicitly,[/color]
[color=lightgreen]'' ' the redim tries to assign each dimension the default lower bound, which is 0 giving an error.[/color]
[color=lightgreen]'' ' So we need to explicitly provide the lower bounds[/color]
[color=lightgreen]'' ' 2) Only the size of the last dimension can be changed for a non dynamic arrange. We want to change the first.[/color]
[color=lightgreen]'' ' So we do a transpose trick for that problem[/color]
[color=lightgreen]'' ' 3) Preseve ensures we do not loose the info already there. (Post #11 http://www.mrexcel.com/forum/excel-questions/830139-proper-redim-preserve-syntax-best-practice-2.html#post4049584)[/color]
[color=lightgreen]'' ' 4) Would only be needed if arrOut has only 1 column as the Transpose does some thing wiered .. does not give 1 to 1 back.. gives normal array...just 1 indicie in ()[/color]
[color=lightgreen]' Dim TempTranspose() As Variant 'Must be variant as seeing a Function below in a "one Liner" which returns a collection which VBA will always guess as an Array because Rory-a-Romping Archibald said so to me... a few times now!!![/color]
[color=lightgreen]' Let TempTranspose = Application.WorksheetFunction.Transpose(arrOut) 'This woulöd annoyingly take for example 21 to 23 , 1 to 1 and give back ... 1 to 3 ONLY!![/color]
[color=lightgreen]' ReDim Preserve arrOut(sr To lr - EmtpyInArrIndiciesCount, 1 To 2) 'This extra bodge 4 '- Seems to be necerssary to make at least bigger than 1 column - goes from for example 21-23, 1 to 1 > Redim Preserve > 21-23, 1 to 2[/color]
[color=lightgreen]' Let TempTranspose = Application.WorksheetFunction.Transpose(arrOut) 'Important.. transposes, but > is 1 to 2, ... 1 to 3 ... so would appoear always to start at 1[/color]
[color=lightgreen]' ReDim Preserve TempTranspose(1 To 2, 1 To lr - sr + EmtpyInArrIndiciesCount) 'This increase from 3 to 4 columns[/color]
[color=lightgreen]' 'ReDim Preserve TempTranspose(1 To 2, sr To lr)' This will NOT work!![/color]
[color=lightgreen]' Let TempTranspose = Application.WorksheetFunction.Transpose(TempTranspose) 'We have the increased row size bot are stuck with the convention of rows starting at 1[/color]
[color=lightgreen]' ReDim Preserve TempTranspose(1 To lr - sr + EmtpyInArrIndiciesCount, 1 To 1) 'This is extra bodge 4 Part 2[/color]
[color=lightgreen]'[/color]
' Let RngD.Value = TempTranspose
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'VBAArrayOfRanges1aa[/color]
[color=lightgreen]'[/color]
'
''
[color=blue]Sub[/color] G11_VBAArrayOfRanges1b()
[color=lightgreen]'Loop Range of Ranges. VBA Make Array for Output. Output in a "Output Value One Liner"[/color]
[color=lightgreen]'Typical application requiring seperate loops for the (often differnt sized) input and output arrays[/color]
[color=lightgreen]'Just an extra direct assigniong bit to demonstrate possibillity of assigning a non dynamic array to a dynamic array in one go, noting Dim characteristics[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
Dim arrIn() [color=blue]As[/color] Range [color=lightgreen]'Variant 'In the looping, the Set would appear to be responsible for bringing **Ranges into the Array The variable type remains as defined here: Either a range for As Range or a Variant Object for [color=blue]As[/color] Variant[/color]
[color=blue]ReDim[/color] arrIn(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1) [color=lightgreen]'We must give array a size as we give specific value in a loop. ReDim Must be used rather than [color=blue]Dim[/color] as Dim only takes Numbers not variables[/color]
[color=blue]For[/color] rws = sr To lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=lightgreen]'Set arrIn()(rws, 1) = Range("A" & rws & "") 'Does not work, that is to say produces error later####!!!![/color]
[color=blue]Set[/color] arrIn(rws, 1) = Range("A" & rws & "") [color=lightgreen]'no error later - we are setting, that is to say "making an object, or rather putting? one in an[/color]
[color=lightgreen]'Set arrIn(rws, 1) = wksLG.Range("A" & rws & "") 'This would be tolerate**[/color]
[color=blue]Next[/color] rws
Dim IntermediateArray() [color=blue]As[/color] Range [color=lightgreen]'Variant 'Note RoryA Post #2You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic AND THE TWO ARRAYS ARE THE SAME TYPE.[/color]
[color=lightgreen]' ' Re[color=blue]Dim[/color] IntermediateArray(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1)'THIS Line Will NOT Work: RoryA Post #2 http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html - You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic[/color]
[color=blue]Let[/color] IntermediateArray() = arrIn() [color=lightgreen]' ......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 utilising 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 IntermediateArray() 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 [color=blue]Variant[/color] to Return an Array of element Type Of Objects of the Range Sort : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html[/color]
[color=blue]Dim[/color] arrOut() As [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 rturn us an array of values.[/color]
[color=blue]Re[color=blue]Dim[/color][/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() [color=blue]As[/color] String initially[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=lightgreen]'Let arrOut(rws, 1) = IntermediateArray(rws, 1).Value 'VBA would appear to recognise this array element as A range as it gives me the Method, Object and property selkection to choose from via intellisense brought in by typing . Dot after the Array elemant[/color]
[color=lightgreen]' Let arrOut(rws, 1) = IntermediateArray(rws, 1).Value'####here comes the error by replaceing Set arrIn(rws, 1) with [color=blue]Set[/color] arrIn()(rws, 1) or in the following similar lines[/color]
[color=lightgreen]' Let arrOut(rws, 1) = IntermediateArray()(rws, 1).Value.. 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.[/color]
[color=lightgreen]' Let arrOut()(rws, 1) = IntermediateArray(rws, 1).Value'Dos not work anyway it aint ever needed on the LHS said Rory, so it aint[/color]
[color=lightgreen]'Let arrOut(rws, 1) = IntermediateArray()(rws, 1).Value[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(IntermediateArray(rws, 1).Value, 4) 'Even A function on the RHS of the = is accepted Here. So again for a non dynamic Array it is not relying on what the function to "define" the type[/color]
[color=lightgreen]'Let arrOut()(rws, 1) = Left(IntermediateArray(rws, 1).Value, 4) 'Givers empty[/color]
[color=lightgreen]'Let arrOut()(rws, 1) = Left(IntermediateArray()(rws, 1).Value, 4)'Gives empty[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(IntermediateArray()(rws, 1).Value, 4)
[color=blue]Next[/color] rws
[color=blue]Let[/color] RngD.Value = arrOut() [color=lightgreen]'This is the typical allowed one liner to give values of a Range the valiues in an Array (VBA does not allow this one liner in any way for assigning the ranges. There is no wy of getting over having to paste each Range Object in in a loop.[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'VBAArrayOfRanges1b[/color]
[color=lightgreen]'[/color]
'
'
'
[color=blue]Sub[/color] G12_VBAArrayOfRanges2()
[color=lightgreen]'Loop Range of Ranges. VBA Make Array for Output. Output in a "Output Value One Liner"[/color]
[color=lightgreen]'less typical application where any workings can be done within one loop and Input and Output arrays may be similarly sized[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
Dim sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
Dim arrIn() [color=blue]As[/color] Range, arrOut() [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'[/color]
[color=blue]Re[color=blue]Dim[/color][/color] arrIn(sr [color=blue]To[/color] lr, 1 [color=blue]To[/color] 1): [color=blue]Re[color=blue]Dim[/color][/color] arrOut(sr [color=blue]To[/color] lr, 1 To 1)
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
Set arrIn(rws, 1) = Range("A" & rws & "")
[color=lightgreen]' ' Let arrOut()(rws, 1) = Left(arrIn(rws, 1).Value, 4) 'Takes 30.5s and returns empties[/color]
[color=lightgreen]' ' Let arrOut()(rws, 1) = Left(arrIn()(rws, 1).Value, 4)'Takes 146s and returns empties[/color]
[color=lightgreen]'Let arrOut(rws, 1) = Left(arrIn(rws, 1).Value, 4)'Takes 1.5s. "Works"[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(arrIn()(rws, 1).Value, 4) [color=lightgreen]'Takes 115s. ."Works". 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. It "tolerates" for Let on LHS also (returning empties) (but not for [color=blue]Set[/color] in the case of similar line involving Ranges..)..But Rory thinks it would never be needed there anyway, which means it most likely doesn't.[/color]
[color=blue]Next[/color] rws
[color=blue]Let[/color] RngD.Value = arrOut()
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'VBAArrayOfRanges2[/color]
[color=blue]Sub[/color] G13_RangeObjectCapture()
[color=lightgreen]'The entire range is captured 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] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
Dim sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
Dim CapturedRangeObject [color=blue]As[/color] Range [color=lightgreen]'One Range Object for entire range[/color]
Set CapturedRangeObject = RngName [color=lightgreen]'Direct assignmet to existing [color=blue]Dim[/color]ensioned Array[/color]
[color=blue]Dim[/color] arrOut() As [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]Re[color=blue]Dim[/color][/color] arrOut(sr [color=blue]To[/color] lr, 1 To 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 [color=blue]Set[/color] an object (Would expect an Range Object if indicies of arrOut were so defined by arrOut() [color=blue]As[/color] String initially[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=lightgreen]'Let arrOut(rws, 1) = CapturedRangeObject.Value2(rws - sr + 1, 1) 'Conventionally the Value2 values are in an Array starting at ( 1, 1) and extending over the two (in this case just a 1 dimensional array). So a bit of adjusting with the indicies is necerssary[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(CapturedRangeObject.Value2(rws - sr + 1, 1), 4)
[color=lightgreen]'Let arrOut(rws, 1) = Left(CapturedRangeObject.Value2()(rws - sr + 1, 1), 4) ' 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, (In the "bodge subs it is assigned each time to the full object[/color]
[color=blue]Next[/color] rws
[color=blue]Let[/color] RngD.Value = arrOut()
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'RangeObjectCapture()[/color]
[color=blue]Sub[/color] G13b_RangeObjectCapture()
[color=lightgreen]'The entire range is captured 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] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[color=blue]Dim[/color] RngName [color=blue]As[/color] Range, RngD [color=blue]As[/color] Range
Dim lr [color=blue]As[/color] [color=blue]Long[/color], rws [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Let[/color] lr = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
Dim sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=blue]Dim[/color] CapturedRangeObject [color=blue]As[/color] Range [color=lightgreen]'One Range Object for entire range[/color]
Set CapturedRangeObject = RngName [color=lightgreen]'Direct assignmet to existing [color=blue]Dim[/color]ensioned Array[/color]
[color=blue]Dim[/color] arrOut() As [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]Re[color=blue]Dim[/color][/color] arrOut(sr To 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 [color=blue]Set[/color] an object (Would expect an Range Object if indicies of arrOut were so defined by arrOut() [color=blue]As[/color] String initially[/color]
[color=blue]Dim[/color] vTemp() [color=blue]As[/color] Variant [color=lightgreen]'Post from #20 - #24 .. return the array to a variable and then reference the elements through that variable, in which case you don't need to worry about the extra () . The usual "To see an object which returns a field" requirement to be dimensioned as [color=blue]Variant[/color][/color]
[color=lightgreen]' http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-3.html[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=blue]Let[/color] vTemp = CapturedRangeObject.Value2 [color=lightgreen]'Works vTemp is Array of variants values, a Data field[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(vTemp(rws - sr + 1, 1), 4)
[color=lightgreen]'Let arrOut(rws, 1) = Left(vTemp()(rws - sr + 1, 1), 4)'This will not work..[/color]
[color=blue]Next[/color] rws
[color=blue]Let[/color] RngD.Value = arrOut()
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'RangeObjectCapture()[/color]
[color=blue]Sub[/color] G13c_RangeObjectCapture()
[color=lightgreen]'The entire range is captured 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] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
Dim RngName [color=blue]As[/color] Range, RngD [color=blue]As[/color] Range
Dim lr [color=blue]As[/color] [color=blue]Long[/color], rws [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Let[/color] lr = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=blue]Dim[/color] CapturedRangeObject [color=blue]As[/color] Range [color=lightgreen]'One Range Object for entire range[/color]
Set CapturedRangeObject = RngName [color=lightgreen]'Direct assignmet to existing [color=blue]Dim[/color]ensioned Array[/color]
[color=blue]Dim[/color] arrOut() As [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]Re[color=blue]Dim[/color][/color] arrOut(sr To lr, 1 To 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 [color=blue]Set[/color] an object (Would expect an Range Object if indicies of arrOut were so defined by arrOut() [color=blue]As[/color] String initially[/color]
[color=blue]Dim[/color] vTemp [color=blue]As[/color] Variant [color=lightgreen]'Post from #20 - #24 .. return the array to a variable and then reference the elements through that variable, in which case you don't need to worry about the extra () . (The usual "To see an (Range) object which returns a field" requirement to be dimensioned as [color=blue]Variant[/color] - Post #13 Post #14 http://www.excelforum.com/excel-programming-vba-macros/1058171-return-row-index-and-column-index-of-a-cell-in-a-range.html[/color]
[color=lightgreen]' http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-3.html[/color]
[color=blue]Let[/color] vTemp = CapturedRangeObject.Value2 [color=lightgreen]'Works vTemp is Array of variants values, a Data field[/color]
[color=blue]For[/color] rws = sr To lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=lightgreen]'Let arrOut()(rws, 1) = Left(vTemp(rws - sr + 1, 1), 4) 'Tolerates the Extra () on the LHS but rerurns empties and takes usually lots longer, here (for 33928 Rows) 30.5s .. compared to _-[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(vTemp(rws - sr + 1, 1), 4) [color=lightgreen]' ... takes 0.325s for 33928 Rows.[/color]
[color=lightgreen]'Let arrOut(rws, 1) = Left(vTemp()(rws - sr + 1, 1), 4) 'wont work ????? ####1 Error 9: Index out of valid Range[/color]
[color=blue]Next[/color] rws
[color=blue]Let[/color] RngD.Value = arrOut()
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'RangeObjectCaptureC()[/color]
[color=lightgreen]'[/color]
'
[color=blue]Sub[/color] G13_RangeObjectCaptureExcelForumDemo()
[color=lightgreen]'The entire range is captured as One Range Object and its Value2 values accesed and used[/color]
[color=lightgreen]'then in VBA Make an Array for Output. Output in a "Output Value One Liner"[/color]
[color=lightgreen]'(Demonstrating also advantage and characteristics of temporary intermediateArray)[/color]
Dim wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
Dim 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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
Dim sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
Dim CapturedRangeObject [color=blue]As[/color] Range [color=lightgreen]' One Range Object for entire range[/color]
Set CapturedRangeObject = RngName [color=lightgreen]' Direct assignmet to existing Dimensioned Array[/color]
Dim arrOut() As [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 To lr, 1 To 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() [color=blue]As[/color] String initially[/color]
Dim vTemp As Variant [color=lightgreen]' Post from #20 - #24 .. return the array to a variable and then reference the elements through that variable, in which case you don't need to worry about the extra () . (The usual "To see an (Range) object which returns a field" requirement to be dimensioned as Variant - Post #13 Post #14 http://www.excelforum.com/excel-programming-vba-macros/1058171-return-row-index-and-column-index-of-a-cell-in-a-range.html[/color]
[color=lightgreen]' http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-3.html[/color]
[color=blue]Let[/color] vTemp = CapturedRangeObject.Value2 [color=lightgreen]'Works vTemp is Array of variants values, a Data field[/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) = Left(CapturedRangeObject.Value2(rws - sr + 1, 1), 4) [color=lightgreen]'Works 1651.5s 33928 Rows. 3.85 1654 Rows Conventionally the Value2 values are in an Array starting at ( 1, 1) and extending over the two (in this case just a 1 dimensional array). So a bit of adjusting with the indicies is necerssary[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(CapturedRangeObject.Value2()(rws - sr + 1, 1), 4) [color=lightgreen]'Works 1667s 33928 Rows. 3.85 1654 Rows 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, (In the "bodge subs it is assigned each time to the full object[/color]
[color=blue]Let[/color] arrOut()(rws, 1) = Left(CapturedRangeObject.Value2(rws - sr + 1, 1), 4) [color=lightgreen]'Returns Empties ????? #2) 991.5s 33928 Rows. 3.85 1654 Rows[/color]
[color=blue]Let[/color] arrOut()(rws, 1) = Left(CapturedRangeObject.Value2()(rws - sr + 1, 1), 4) [color=lightgreen]'Returns Empties ????? #2) 1696s 33928 Rows. 3.85 1654 Rows[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(vTemp(rws - sr + 1, 1), 4) [color=lightgreen]'Works 0.325s for 33928 Rows. 0.018 1654 Rows. This amazing speed is easilly explained as in this SIMPLIFIED examplse file the code is working similar to the "VBA Array" version of my typical answers to sorting Threads which demonstrates the advantage of working with arrays over my alternative "Spreadsheet" type Solution which I usually also give in the Sorting Threads that I answer..[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(vTemp()(rws - sr + 1, 1), 4) [color=lightgreen]'wont work ????? #1 Error 9: Index out of valid Range[/color]
[color=blue]Let[/color] arrOut()(rws, 1) = Left(vTemp(rws - sr + 1, 1), 4) [color=lightgreen]'Returns Empties. ????? #2) 30.5s 33928 Rows.. 0.088 1654 Rows Tolerates the Extra () on the LHS but rerurns empties and takes usually lots longer 30.5s 33928 Rows..[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(vTemp()(rws - sr + 1, 1), 4) [color=lightgreen]'wont work ????? #1 Error 9: Index out of valid Range[/color]
[color=blue]Next[/color] rws
[color=blue]Let[/color] RngD.Value = arrOut() [color=lightgreen]' the Array of Values is outputted in the typical one liner exclusively allowed to values only[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'G13_RangeObjectCaptureExcelForumDemo()[/color]
[color=lightgreen]'[/color]
'
[color=blue]Sub[/color] G14_MicrosoftScriptingRuntimeDictionaryRangeOfRangesKeysItems1()
[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, including the use of a temporary cell for use of unique key characteristic.[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=lightgreen]' 1) 'Part 1: Setting up Scriptimg Runtime Stuff-----------------------------[/color]
[color=lightgreen]' Attempting Using the Microsooft Scripting Runtime Dictionary to store Range Objects[/color]
[color=lightgreen]'We put the unique values now into a Dictionary for later look up purposes:[/color]
[color=lightgreen]'--requires library reference to MS Scripting Runtime (Early Binding)-[/color]
[color=lightgreen]' Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime[/color]
[color=lightgreen]' ..Or crashes at next line.....[/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
[color=lightgreen]' 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=lightgreen]' Dim dicLookupTable As Object[/color]
[color=lightgreen]' Set dicLookupTable = CreateObject("Scripting.Dictionary")[/color]
[color=lightgreen]' Late Binding is better when sharing files as I am here. Early Binding has the advantage that Excel intellisense[/color]
[color=lightgreen]' 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=lightgreen]'Not quite sure wot this does yet[/color]
[color=lightgreen]'. A Dictionary in VBA is a collection of objects :you can store all kinds of things in it.[/color]
[color=lightgreen]'. 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=lightgreen]'. (Although the Dictionary has not been designed for that purpose it's a nice side effect.)[/color]
[color=lightgreen]'End of Part 1 initial set up Of Scripting Runtime------------------------[/color]
[color=lightgreen]'Dim j As Long ', i [color=blue]As[/color] Long 'LoopBoundVariableCounts used in looping here and at end-----[/color]
[color=blue]Dim[/color] TempColumn [color=blue]As[/color] Long: [color=blue]Let[/color] TempColumn = Columns.Count: [color=blue]Let[/color] TempColumn = 6 [color=lightgreen]'Usually when not debugging comment out last let so Temp Column is last in sheet given by Columns.count....[/color]
[color=blue]Dim[/color] TempCell [color=blue]As[/color] Range: [color=blue]Set[/color] TempCell = wksLG.Cells(1, TempColumn): [color=blue]Dim[/color] TempCellOffset [color=blue]As[/color] [color=blue]Long[/color]: [color=blue]Let[/color] TempCellOffset = 0 [color=lightgreen]'....We choose a cell (or through the later use of the offset step down a column) to use for Duplicate or Empty cells. We often use the last column in the sheet. (This is genarally a good practice as it will not effect finding last column with .End(XltoLeft). Note there were sometimes strange resource problems with deleting columns on large files using the last column rather than one "just off screen" instead )[/color]
[color=lightgreen]'2a) Part2a) Looping to put Range Objects in MRSD[/color]
[color=lightgreen]'For i = StartColumnTableOutput To LastColumnTableOutput Step 1[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
[color=blue]If[/color] wksLG.Cells(rws, 1).Value <> "" [color=blue]Then[/color] [color=lightgreen]'If cell is not empty then...[/color]
[color=blue]If[/color] [color=blue]Not[/color] dicLookupTable.Exists(wksLG.Cells(rws, 1).Value) [color=blue]Then[/color] [color=lightgreen]'check that the unique value does not already exist. ##NOTE[/color]
dicLookupTable.Add wksLG.Cells(rws, 1).Value, wksLG.Cells(rws, 1) [color=lightgreen]'it is easier to understand as well as kind of explicit the first argument does a CStr and the Second Takes anything[/color]
[color=blue]Else[/color] [color=lightgreen]'If the key exists, that is to say we have a Range with a Duplicate value, we give the key a slightly modified (unique value) , still give the Range Object as an item, but make an indication, here by highlighting the cell in Pink[/color]
[color=blue]Let[/color] TempCellOffset = TempCellOffset + 1
[color=blue]Let[/color] TempCell.Offset(TempCellOffset, 0).Value = "Duplicate at " & rws & " | " & 1 & ""
wksLG.Cells(rws, 1).Interior.Color = 10987519
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, wksLG.Cells(rws, 1) [color=lightgreen]'In case of duplicate we need a unique key, but we stillinclude the Duplicate Range[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Else[/color] [color=lightgreen]'Case r an empty cell - inform of empty cell by writing message in that cell via the Tempory cell[/color]
[color=blue]Let[/color] TempCellOffset = TempCellOffset + 1 [color=lightgreen]'Go to next free tempory cell in tempory column[/color]
[color=blue]Let[/color] TempCell.Offset(TempCellOffset, 0).Value = "Empty Cell at " & rws & " | " & 1 & ""
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, TempCell.Offset(TempCellOffset, 0)
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Next[/color] rws
[color=lightgreen]'Next i[/color]
'End Part 2-----------------------------------------------------------
[color=lightgreen]'3) Part 3)--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] IntermediateArray() As [color=blue]Variant[/color] [color=lightgreen]'... [color=blue]As[/color] a Variant and then the "Let IntermediateArray = " 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] IntermediateArray() = dicLookupTable.Items() [color=lightgreen]'... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses IntermediateArray()[/color]
[color=lightgreen]'End part 3)--- NOTE: this gives automatically the 0 to _ convention in IntermediateArray 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 utilising 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 IntermediateArray() 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 : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html[/color]
[color=blue]Dim[/color] arrOut() As [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 rturn 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() [color=blue]As[/color] String initially[/color]
[color=blue]For[/color] rws = sr To lr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=lightgreen]'Let arrOut(rws, 1) = IntermediateArray(rws - sr).Value 'VBA would appear to recognise this array element as A range as it gives me the Method, Object and property selkection to choose from via intellisense brought in by typing . Dot after the Array elemant[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(dicLookupTable.Items(rws - sr).Value, 4)[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(dicLookupTable.Items()(rws - sr).Value, 4)[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(IntermediateArray(rws - sr).Value, 4) 'Even A function on the RHS of the = is accepted Here. So again for a non dynamic Array it is not relying on what the function to "define" the type[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(IntermediateArray()(rws - sr).Value, 4)[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(dicLookupTable.Keys(rws - sr), 4)[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(dicLookupTable.Keys()(rws - sr), 4)
[color=blue]Next[/color] rws
[color=blue]Let[/color] RngD.Value = arrOut()
[color=lightgreen]' Let RngD.Value = IntermediateArray() 'This should not do anything.. interestingly gives value from first Cell in RngName,[/color]
[color=blue]Set[/color] dicLookupTable = [color=blue]Nothing[/color] [color=lightgreen]'Genarally good practice to turn these thimgs off. 'May not be needed but to keep program flexible should alterations need it.[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'MicrosoftScriptimeRuntimeDictionaryRangeOfRangesKeysItems1()[/color]
[color=lightgreen]'[/color]
'
[color=blue]Sub[/color] G15_MicrosoftScriptimeRuntimeDictionaryRangeOfRangesKeys()
[color=lightgreen]'Similar to the Array of Range ranges, except that the Ranges are held in the MSRD Key. Strange but it can!![/color]
[color=lightgreen]'A bit less Lots of interaction with the spreadsheet, as the temp for the unique is not there - we are using the key,[/color]
[color=lightgreen]'so all ranges are unique keys we simply include that duplicateentry, that is to say the output array may be a bit shorter.[/color]
[color=lightgreen]'Also the cell my be empty but the RANGE cannot be="" so for it's "value" VBA writes "Empty" !![/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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] Long
[color=blue]Let[/color] lr = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=lightgreen]' 1) 'Part 1b: Setting up Scriptimg Runtime Stuff-----------------------------[/color]
[color=lightgreen]'Using the Microsoft Scripting Runtime Dictionary KEYS ONLY to store Range Objects... A bit like saying "....(It is certainly a crazy concept if you think about it….Like saying in a Filing Cabinet I have a piece of paper for every House in a town with all the plans and details of the house in it. The parallel idea to the Keys being able to be almost anything would be that instead of the piece of paper I could a Duplicate of every House in the filing cabinet!!! ). ...." Alan :- Post #10 http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects.html[/color]
[color=lightgreen]'We put the unique values now into a Dictionary for later look up purposes:[/color]
[color=lightgreen]'--requires library reference to MS Scripting Runtime (Early Binding)-[/color]
[color=lightgreen]' Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime[/color]
[color=lightgreen]' ..Or crashes at next line.....[/color]
[color=blue]Dim[/color] dicLookupTable As 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
[color=lightgreen]' 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=lightgreen]' Dim dicLookupTable As Object[/color]
[color=lightgreen]' Set dicLookupTable = CreateObject("Scripting.Dictionary")[/color]
[color=lightgreen]' Late Binding is better when sharing files as I am here. Early Binding has the advantage that Excel intellisense[/color]
[color=lightgreen]' 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=lightgreen]'Not quite sure wot this does yet[/color]
[color=lightgreen]'. A Dictionary in VBA is a collection of objects :you can store all kinds of things in it.[/color]
[color=lightgreen]'. 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=lightgreen]'. (Although the Dictionary has not been designed for that purpose it's a nice side effect.)[/color]
[color=lightgreen]'End of Part 1b initial set up Of Scripting Runtime------------------------[/color]
[color=lightgreen]'Dim j As Long ', i As Long 'LoopBoundVariableCounts used in looping here and at end-----[/color]
[color=lightgreen]'Dim TempCell [color=blue]As[/color] Range: Set TempCell = wksLG.Cells(1, Columns.Count): Dim TempCellOffset As Long: Let TempCellOffset = 0 'We choose a cell (or through the later use of the offset a column) to use for Duplicate or Empty cells. We use the last column in the sheet. (This is genarally a good practice as it will not effect attempts with .End(XltoLeft) to find last column[/color]
[color=lightgreen]'2a) Part2a) Looping to put Range Objects in MRSD[/color]
[color=lightgreen]'For i = StartColumnTableOutput [color=blue]To[/color] LastColumnTableOutput Step 1[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
[color=blue]If[/color] wksLG.Cells(rws, 1).Value <> "" [color=blue]Then[/color] [color=lightgreen]'If RANGE.value is not empty...[/color]
[color=lightgreen]' If Not dicLookupTable.Exists(wksLG.Cells(rws, 1).Value) Then 'check that the unique value does not already exist. ##NOTE[/color]
dicLookupTable.Add wksLG.Cells(rws, 1), [color=blue]Nothing[/color] [color=lightgreen]'it is easier to understand as well as kind of explicit the first argument does a CStr and the Second Takes anything[/color]
[color=lightgreen]' Else 'The key is the Range which is always unique[/color]
[color=lightgreen]' End If[/color]
[color=blue]Else[/color] [color=lightgreen]'Case r an empty cell. We habe a choice by virtue of us using ###Newlr below to do nothing.. or..[/color]
[color=blue]Let[/color] wksLG.Cells(rws, 1).Value = "Anything" [color=lightgreen]'.. we could have done nothing - for the case of the Let function it does not erro just gives nothing[/color]
dicLookupTable.Add wksLG.Cells(rws, 1), [color=blue]Nothing[/color] [color=lightgreen]'.. chose here it to give key anyway and..[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Next[/color] rws
[color=lightgreen]'Next i[/color]
'End Part 2-----------------------------------------------------------
[color=lightgreen]'3) Part 3)--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] IntermediateArray() As [color=blue]Variant[/color] [color=lightgreen]'... [color=blue]As[/color] a Variant and then the "Let IntermediateArray = " 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] IntermediateArray() = dicLookupTable.Keys() [color=lightgreen]'... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses IntermediateArray()[/color]
[color=lightgreen]'End part 3)--- NOTE: this gives automatically the 0 to _ convention in IntermediateArray 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 utilising 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 IntermediateArray() 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 : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html[/color]
[color=lightgreen]' 'Dim Newlr As Long: Let Newlr = UBound(IntermediateArray(), 1) + sr '###This is importent when looping through dicLookupTable.Keys so that for one or more empty cell not given a key we do not try to loop too far[/color]
[color=blue]Dim[/color] arrOut() As [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 rturn us an array of values.[/color]
[color=blue]ReDim[/color] arrOut(sr [color=blue]To[/color] Newlr, 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. We could leave Nerlr as lr - doesn't matter if output array is bit too big[/color]
[color=blue]For[/color] rws = sr To Newlr [color=blue]Step[/color] 1 [color=lightgreen]'Input Array of Ranges[/color]
[color=lightgreen]'Let arrOut(rws, 1) = IntermediateArray(rws - sr).Value 'VBA would appear to recognise this array element as A range as it gives me the Method, Object and property selkection to choose from via intellisense brought in by typing . Dot after the Array elemant[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(dicLookupTable.Keys(rws - sr).Value, 4) [color=lightgreen]'Remember.. write in .Value - Do not rely on the implicit!![/color]
[color=lightgreen]'Let arrOut(rws, 1) = Left(dicLookupTable.Keys()(rws - sr).Value, 4)[/color]
[color=lightgreen]'Let arrOut(rws, 1) = Left(IntermediateArray(rws - sr).Value, 4) 'Even A function on the RHS of the = is accepted Here. So again for a non dynamic Array it is not relying on what the function to "define" the type[/color]
[color=lightgreen]'Let arrOut(rws, 1) = Left(IntermediateArray()(rws - sr).Value, 4)[/color]
[color=blue]Next[/color] rws
[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. 'May not be needed but to keep program flexible should alterations need it.[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'MicrosoftScriptimeRuntimeDictionaryRangeOfRangesKeys()[/color]
[color=green]'[/color]
'
'
[color=lightgreen]'[/color]
[color=blue]Sub[/color] G18_MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItemsBodge1()
[color=lightgreen]'Similar to the Array of Range ranges, except that the Ranges are held in a full MSRD[/color]
[color=lightgreen]'Less interaction with the spreadsheet, just the use of a temporary cell for use of unique key characteristic.[/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 Temporárilly put entire CapturedRangeObject in each item (except for duplicates and empty cells, which for now still access the Spreadsheet[/color]
[color=lightgreen]'This requires extra Bodges in Emptys and Duplicates and explanations of new Explicits here Post #14 - post #19 http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-2.html[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=lightgreen]' 1) 'Part 1b: Setting up Scriptimg Runtime Stuff-----------------------------[/color]
[color=lightgreen]' Attempting Using the Microsooft Scripting Runtime Dictionary to store Range Objects[/color]
[color=lightgreen]'We put the unique values now into a Dictionary for later look up purposes:[/color]
[color=lightgreen]'--requires library reference to MS Scripting Runtime (Early Binding)-[/color]
[color=lightgreen]' Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime[/color]
[color=lightgreen]' ..Or crashes at next line.....[/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
[color=lightgreen]' 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=lightgreen]' Dim dicLookupTable As Object[/color]
[color=lightgreen]' Set dicLookupTable = CreateObject("Scripting.Dictionary")[/color]
[color=lightgreen]' Late Binding is better when sharing files as I am here. Early Binding has the advantage that Excel intellisense[/color]
[color=lightgreen]' 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=lightgreen]'Not quite sure wot this does yet[/color]
[color=lightgreen]'. A Dictionary in VBA is a collection of objects :you can store all kinds of things in it.[/color]
[color=lightgreen]'. 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=lightgreen]'. (Although the Dictionary has not been designed for that purpose it's a nice side effect.)[/color]
[color=lightgreen]'End of Part 1b initial set up Of Scripting Runtime------------------------[/color]
[color=lightgreen]'Dim j As Long ', i [color=blue]As[/color] Long 'LoopBoundVariableCounts used in looping here and at end-----[/color]
[color=blue]Dim[/color] TempCell [color=blue]As[/color] Range: [color=blue]Set[/color] TempCell = wksLG.Cells(1, Columns.Count): [color=blue]Dim[/color] TempCellOffset [color=blue]As[/color] Long: [color=blue]Let[/color] TempCellOffset = 0 [color=lightgreen]'We choose a cell (or through the later use of the offset a column) to use for Duplicate or Empty cells. We use the last column in the sheet. (This is genarally a good practice as it will not effect attempts with .End(XltoLeft) to find last column[/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=lightgreen]'For i = StartColumnTableOutput To LastColumnTableOutput Step 1[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
[color=blue]If[/color] CapturedRangeObject.Value2(rws - sr + 1, 1) <> "" [color=blue]Then[/color] [color=lightgreen]'If cell is not empty then...[/color]
[color=blue]If[/color] [color=blue]Not[/color] dicLookupTable.Exists(wksLG.Cells(rws, 1).Value) [color=blue]Then[/color] [color=lightgreen]'check that the unique value does not already exist. ##NOTE[/color]
dicLookupTable.Add CapturedRangeObject.Value2(rws - sr + 1, 1), CapturedRangeObject [color=lightgreen]'it is easier to understand as well as kind of explicit the first argument does a CStr and the Second Takes anything[/color]
[color=blue]Else[/color] [color=lightgreen]'If the key exists, that is to say we have a Range with a Duplicate value, we give the key a slightly modified (unique value) , still give the Range Object as an item, but make an indication, here by highlighting the cell in Pink[/color]
[color=blue]Let[/color] TempCellOffset = TempCellOffset + 1
[color=blue]Let[/color] TempCell.Offset(TempCellOffset, 0).Value = "Duplicate at " & rws & " | " & 1 & ""
wksLG.Cells(rws, 1).Interior.Color = 10987519
[color=lightgreen]'dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, wksLG.Cells(rws, 1) 'In case of duplicate we need a unique key, but we stillinclude the Duplicate Range[/color]
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, CapturedRangeObject [color=lightgreen]'Must bodge also this line or problem that for my Temp Range has a Value2 of NOT an array()[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Else[/color] [color=lightgreen]'Case r an empty cell - inform of empty cell by writing message in that cell via the Tempory cell[/color]
[color=blue]Let[/color] TempCellOffset = TempCellOffset + 1 [color=lightgreen]'Go to next free tempory cell in tempory column[/color]
[color=blue]Let[/color] TempCell.Offset(TempCellOffset, 0).Value = "Empty Cell at " & rws & " | " & 1 & ""
[color=lightgreen]'dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, TempCell.Offset(TempCellOffset, 0)[/color]
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, CapturedRangeObject [color=lightgreen]'Must bodge also this line or problem that for my Temp Range has a Value2 of NOT an array()[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Next[/color] rws
[color=lightgreen]'Next i[/color]
[color=lightgreen]'End Part 2-----------------------------------------------------------[/color]
[color=lightgreen]'3) Part 3)--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] IntermediateArray() As [color=blue]Variant[/color] [color=lightgreen]'... [color=blue]As[/color] 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] IntermediateArray() = dicLookupTable.Items() [color=lightgreen]'... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses IntermediateArray()[/color]
[color=lightgreen]'End part 3)--- NOTE: this gives automatically the 0 to _ convention in IntermediateArray 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 utilising 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 IntermediateArray() 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 : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html[/color]
[color=lightgreen]'4) Part 4)---Produce output array by looping[/color]
[color=blue]Dim[/color] arrOut() As [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 rturn 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 Note ## >> ’.. 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[/color]
[color=lightgreen]'Let arrOut(rws, 1) = CapturedRangeObject.Value2(rws - sr + 1, 1) 'Works.. but ...[/color]
[color=lightgreen]'Let arrOut(rws, 1) = CapturedRangeObject.Value2()(rws - sr + 1, 1) '...better ##[/color]
[color=lightgreen]'Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2(rws - sr + 1, 1) 'Wont work[/color]
[color=lightgreen]'Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2()(rws - sr + 1, 1) 'Works ##[/color]
[color=lightgreen]'Let arrOut(rws, 1) = IntermediateArray(rws - sr).Value2(rws - sr + 1, 1) 'Won't work[/color]
[color=lightgreen]'Let arrOut(rws, 1) = IntermediateArray(rws - sr).Value2()(rws - sr + 1, 1) 'Works ##[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(dicLookupTable.Items(rws - sr).Value2()(rws - sr + 1, 1), 4) [color=lightgreen]'Works ##[/color]
'Let arrOut(rws, 1) = Left(IntermediateArray(rws - sr).Value2()(rws - sr + 1, 1), 4) 'Works ##
[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. 'May not be needed but to keep program flexible should alterations need it.[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItemsBodge1()[/color]
[color=lightgreen]'[/color]
'
'
'
'
'
[color=lightgreen]'[/color]
[color=blue]Sub[/color] G19_MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItemsBodge2()
[color=lightgreen]'Similar to the Array of Range ranges, except that the Ranges are held in a full MSRD[/color]
[color=lightgreen]'Less interaction with the spreadsheet, just the use of a temporary cell for use of unique key characteristic.[/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 Temporárilly put entire CapturedRangeObject in each item (except for duplicates and empty cells, which for now still access the Spreadsheet[/color]
[color=lightgreen]'This requires extra Bodges in Emptys and Duplicates and explanations of an alternative way to avoid the extra () from last code (MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItemsBodge2()) Post #14 - post #19 http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-2.html[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=lightgreen]' 1) 'Part 1b: Setting up Scriptimg Runtime Stuff-----------------------------[/color]
[color=lightgreen]' Attempting Using the Microsooft Scripting Runtime Dictionary to store Range Objects[/color]
[color=lightgreen]'We put the unique values now into a Dictionary for later look up purposes:[/color]
[color=lightgreen]'--requires library reference to MS Scripting Runtime (Early Binding)-[/color]
[color=lightgreen]' Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime[/color]
[color=lightgreen]' ..Or crashes at next line.....[/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
[color=lightgreen]' 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=lightgreen]' Dim dicLookupTable As Object[/color]
[color=lightgreen]' Set dicLookupTable = CreateObject("Scripting.Dictionary")[/color]
[color=lightgreen]' Late Binding is better when sharing files as I am here. Early Binding has the advantage that Excel intellisense[/color]
[color=lightgreen]' 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=lightgreen]'Not quite sure wot this does yet[/color]
[color=lightgreen]'. A Dictionary in VBA is a collection of objects :you can store all kinds of things in it.[/color]
[color=lightgreen]'. 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=lightgreen]'. (Although the Dictionary has not been designed for that purpose it's a nice side effect.)[/color]
[color=lightgreen]'End of Part 1b initial set up Of Scripting Runtime------------------------[/color]
[color=lightgreen]'Dim j As Long ', i [color=blue]As[/color] Long 'LoopBoundVariableCounts used in looping here and at end-----[/color]
[color=blue]Dim[/color] TempCell [color=blue]As[/color] Range: [color=blue]Set[/color] TempCell = wksLG.Cells(1, Columns.Count): [color=blue]Dim[/color] TempCellOffset [color=blue]As[/color] Long: [color=blue]Let[/color] TempCellOffset = 0 [color=lightgreen]'We choose a cell (or through the later use of the offset a column) to use for Duplicate or Empty cells. We use the last column in the sheet. (This is genarally a good practice as it will not effect attempts with .End(XltoLeft) to find last column[/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=lightgreen]'For i = StartColumnTableOutput To LastColumnTableOutput Step 1[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
[color=blue]If[/color] CapturedRangeObject.Value2(rws - sr + 1, 1) <> "" [color=blue]Then[/color] [color=lightgreen]'If cell is not empty then...[/color]
[color=blue]If[/color] [color=blue]Not[/color] dicLookupTable.Exists(wksLG.Cells(rws, 1).Value) [color=blue]Then[/color] [color=lightgreen]'check that the unique value does not already exist. ##NOTE[/color]
dicLookupTable.Add CapturedRangeObject.Value2(rws - sr + 1, 1), CapturedRangeObject [color=lightgreen]'it is easier to understand as well as kind of explicit the first argument does a CStr and the Second Takes anything[/color]
[color=blue]Else[/color] [color=lightgreen]'If the key exists, that is to say we have a Range with a Duplicate value, we give the key a slightly modified (unique value) , still give the Range Object as an item, but make an indication, here by highlighting the cell in Pink[/color]
[color=blue]Let[/color] TempCellOffset = TempCellOffset + 1
[color=blue]Let[/color] TempCell.Offset(TempCellOffset, 0).Value = "Duplicate at " & rws & " | " & 1 & ""
wksLG.Cells(rws, 1).Interior.Color = 10987519
[color=lightgreen]'dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, wksLG.Cells(rws, 1) 'In case of duplicate we need a unique key, but we stillinclude the Duplicate Range[/color]
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, CapturedRangeObject [color=lightgreen]'Must bodge also this line or problem that for my Temp Range has a Value2 of NOT an array()[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Else[/color] [color=lightgreen]'Case r an empty cell - inform of empty cell by writing message in that cell via the Tempory cell[/color]
[color=blue]Let[/color] TempCellOffset = TempCellOffset + 1 [color=lightgreen]'Go to next free tempory cell in tempory column[/color]
[color=blue]Let[/color] TempCell.Offset(TempCellOffset, 0).Value = "Empty Cell at " & rws & " | " & 1 & ""
[color=lightgreen]'dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, TempCell.Offset(TempCellOffset, 0)[/color]
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, CapturedRangeObject [color=lightgreen]'Must bodge also this line or problem that for my Temp Range has a Value2 of NOT an array()[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Next[/color] rws
[color=lightgreen]'Next i[/color]
[color=lightgreen]'End Part 2-----------------------------------------------------------[/color]
[color=lightgreen]'3) Part 3)--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] IntermediateArray() As [color=blue]Variant[/color] [color=lightgreen]'... [color=blue]As[/color] 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] IntermediateArray() = dicLookupTable.Items() [color=lightgreen]'... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses IntermediateArray()[/color]
[color=lightgreen]'End part 3)--- 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 utilising 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 IntermediateArray() 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 : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html[/color]
[color=lightgreen]'4) Part 4)---Produce output array by looping[/color]
[color=blue]Dim[/color] vTemp [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Post from #20 - #24 .. return the array to a variable and then reference the elements through that variable, in which case you don't need to worry about the extra () . The usual "To see an object which returns a field" requirement to be dimensioned as Variant[/color]
[color=lightgreen]' http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-3.html[/color]
[color=blue]Dim[/color] arrOut() As [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 rturn 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 Note ## >> ’.. 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, (In the "bodge it is assigned each time to the full object[/color]
[color=lightgreen]' ' Let arrOut(rws, 1) = CapturedRangeObject.Value2(rws - sr + 1, 1) 'Works.. but ...[/color]
[color=lightgreen]' ' Let arrOut(rws, 1) = CapturedRangeObject.Value2()(rws - sr + 1, 1) '...better ##[/color]
[color=lightgreen]' ' 'Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2(rws - sr + 1, 1) 'Wont work[/color]
[color=lightgreen]' ' Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2()(rws - sr + 1, 1) 'Works ##[/color]
[color=blue]Let[/color] vTemp = dicLookupTable.Items(rws - sr).Value2 [color=lightgreen]'Works vTemp is Array of variants values, a Data field[/color]
[color=lightgreen]'Let vTemp = dicLookupTable.Items(rws - sr).Value2() 'Works also!!![/color]
[color=lightgreen]'Let vTemp = IntermediateArray(rws - sr).Value2 'Works vTemp is Array of variants values, a Data fild[/color]
[color=lightgreen]'Let vTemp = IntermediateArray(rws - sr).Value2()'Also works!!![/color]
[color=lightgreen]' 'Let arrOut(rws, 1) = vTemp(rws - sr + 1, 1) 'Works[/color]
[color=lightgreen]' ' 'Let arrOut(rws, 1) = IntermediateArray(rws - sr).Value2(rws - sr + 1, 1) 'Won't work[/color]
[color=lightgreen]' ' Let arrOut(rws, 1) = IntermediateArray(rws - sr).Value2()(rws - sr + 1, 1) 'Works ##[/color]
[color=lightgreen]' ' Let arrOut(rws, 1) = Left(dicLookupTable.Items(rws - sr).Value2()(rws - sr + 1, 1), 4) 'Works ##[/color]
[color=lightgreen]' ' Let arrOut(rws, 1) = Left(IntermediateArray(rws - sr).Value2()(rws - sr + 1, 1), 4) 'Works ##[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(vTemp(rws - sr + 1, 1), 4) [color=lightgreen]'Works[/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. 'May not be needed but to keep program flexible should alterations need it.[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItemsBodge2()[/color]
[color=lightgreen]'[/color]
[color=lightgreen]'[/color]
[color=blue]Sub[/color] G20_MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItemsBodge3()
[color=lightgreen]'Similar to the Array of Range ranges, except that the Ranges are held in a full MSRD[/color]
[color=lightgreen]'Less interaction with the spreadsheet, just the use of a temporary cell for use of unique key characteristic.[/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 Temporárilly put entire CapturedRangeObject in each item (except for duplicates and empty cells, which for now still access the Spreadsheet[/color]
[color=lightgreen]'This requires extra Bodges in Emptys and Duplicates and explanations of an alternative way to avoid the extra () from last code (MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItemsBodge2()) Post #14 - post #19 http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-2.html[/color]
[color=blue]Dim[/color] wksLG [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksLG = ThisWorkbook.Worksheets("LeftSpeedsDeutsch")
[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 = wksLG.Cells(Rows.Count, 1).End(xlUp).Row
[color=blue]Dim[/color] sr [color=blue]As[/color] Long: [color=blue]Let[/color] sr = 21
[color=blue]Set[/color] RngName = wksLG.Range("A" & sr & ":A" & lr & ""): [color=blue]Set[/color] RngD = wksLG.Range("D" & sr & ":D" & lr & "")
[color=lightgreen]' 1) 'Part 1b: Setting up Scriptimg Runtime Stuff-----------------------------[/color]
[color=lightgreen]' Attempting Using the Microsooft Scripting Runtime Dictionary to store Range Objects[/color]
[color=lightgreen]'We put the unique values now into a Dictionary for later look up purposes:[/color]
[color=lightgreen]'--requires library reference to MS Scripting Runtime (Early Binding)-[/color]
[color=lightgreen]' Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime[/color]
[color=lightgreen]' ..Or crashes at next line.....[/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
[color=lightgreen]' 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=lightgreen]' Dim dicLookupTable As Object[/color]
[color=lightgreen]' Set dicLookupTable = CreateObject("Scripting.Dictionary")[/color]
[color=lightgreen]' Late Binding is better when sharing files as I am here. Early Binding has the advantage that Excel intellisense[/color]
[color=lightgreen]' 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=lightgreen]'Not quite sure wot this does yet[/color]
[color=lightgreen]'. A Dictionary in VBA is a collection of objects :you can store all kinds of things in it.[/color]
[color=lightgreen]'. 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=lightgreen]'. (Although the Dictionary has not been designed for that purpose it's a nice side effect.)[/color]
[color=lightgreen]'End of Part 1b initial set up Of Scripting Runtime------------------------[/color]
[color=lightgreen]'Dim j As Long ', i [color=blue]As[/color] Long 'LoopBoundVariableCounts used in looping here and at end-----[/color]
[color=blue]Dim[/color] TempCell [color=blue]As[/color] Range: [color=blue]Set[/color] TempCell = wksLG.Cells(1, Columns.Count): [color=blue]Dim[/color] TempCellOffset [color=blue]As[/color] Long: [color=blue]Let[/color] TempCellOffset = 0 [color=lightgreen]'We choose a cell (or through the later use of the offset a column) to use for Duplicate or Empty cells. We use the last column in the sheet. (This is genarally a good practice as it will not effect attempts with .End(XltoLeft) to find last column[/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=lightgreen]'For i = StartColumnTableOutput To LastColumnTableOutput Step 1[/color]
[color=blue]For[/color] rws = sr [color=blue]To[/color] lr [color=blue]Step[/color] 1
[color=blue]If[/color] CapturedRangeObject.Value2(rws - sr + 1, 1) <> "" [color=blue]Then[/color] [color=lightgreen]'If cell is not empty then...[/color]
[color=blue]If[/color] [color=blue]Not[/color] dicLookupTable.Exists(wksLG.Cells(rws, 1).Value) [color=blue]Then[/color] [color=lightgreen]'check that the unique value does not already exist. ##NOTE[/color]
dicLookupTable.Add CapturedRangeObject.Value2(rws - sr + 1, 1), CapturedRangeObject [color=lightgreen]'it is easier to understand as well as kind of explicit the first argument does a CStr and the Second Takes anything[/color]
[color=blue]Else[/color] [color=lightgreen]'If the key exists, that is to say we have a Range with a Duplicate value, we give the key a slightly modified (unique value) , still give the Range Object as an item, but make an indication, here by highlighting the cell in Pink[/color]
[color=blue]Let[/color] TempCellOffset = TempCellOffset + 1
[color=blue]Let[/color] TempCell.Offset(TempCellOffset, 0).Value = "Duplicate at " & rws & " | " & 1 & ""
wksLG.Cells(rws, 1).Interior.Color = 10987519
[color=lightgreen]'dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, wksLG.Cells(rws, 1) 'In case of duplicate we need a unique key, but we stillinclude the Duplicate Range[/color]
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, CapturedRangeObject [color=lightgreen]'Must bodge also this line or problem that for my Temp Range has a Value2 of NOT an array()[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Else[/color] [color=lightgreen]'Case r an empty cell - inform of empty cell by writing message in that cell via the Tempory cell[/color]
[color=blue]Let[/color] TempCellOffset = TempCellOffset + 1 [color=lightgreen]'Go to next free tempory cell in tempory column[/color]
[color=blue]Let[/color] TempCell.Offset(TempCellOffset, 0).Value = "Empty Cell at " & rws & " | " & 1 & ""
[color=lightgreen]'dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, TempCell.Offset(TempCellOffset, 0)[/color]
dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, CapturedRangeObject [color=lightgreen]'Must bodge also this line or problem that for my Temp Range has a Value2 of NOT an array()[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Next[/color] rws
[color=lightgreen]'Next i[/color]
[color=lightgreen]'End Part 2-----------------------------------------------------------[/color]
[color=lightgreen]'3) Part 3)--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] IntermediateArray() As [color=blue]Variant[/color] [color=lightgreen]'... [color=blue]As[/color] 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] IntermediateArray() = dicLookupTable.Items() [color=lightgreen]'... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses IntermediateArray()[/color]
[color=lightgreen]'End part 3)--- 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 utilising 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 IntermediateArray() 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 : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html[/color]
[color=lightgreen]'4) Part 4)---Produce output array by looping[/color]
[color=blue]Dim[/color] vTemp [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Post from #20 - #24 .. return the array to a variable and then reference the elements through that variable, in which case you don't need to worry about the extra () . The usual "To see an object which returns a field" requirement to be dimensioned as Variant[/color]
[color=lightgreen]' http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-3.html[/color]
[color=blue]Let[/color] vTemp = dicLookupTable.Items(3).Value2 [color=lightgreen]'Works vTemp is Array of variants values, a Data field. In bodge 3 set to an arbritrary item number as in this stupid bodge they are all the same[/color]
[color=blue]Dim[/color] arrOut() As [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 rturn 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 Note ## >> ’.. 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, (In the "bodge it is assigned each time to the full object[/color]
[color=lightgreen]' Let arrOut(rws, 1) = CapturedRangeObject.Value2(rws - sr + 1, 1) 'Works.. but ...[/color]
[color=lightgreen]' Let arrOut(rws, 1) = CapturedRangeObject.Value2()(rws - sr + 1, 1) '...better ##[/color]
[color=lightgreen]' 'Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2(rws - sr + 1, 1) 'Wont work[/color]
[color=lightgreen]' Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2()(rws - sr + 1, 1) 'Works ##[/color]
[color=lightgreen]'Let vTemp = dicLookupTable.Items(rws - sr).Value2() 'Works also!!![/color]
[color=lightgreen]'Let vTemp = IntermediateArray(rws - sr).Value2 'Works vTemp is Array of variants values, a Data fild[/color]
[color=lightgreen]'Let vTemp = IntermediateArray(rws - sr).Value2()'Also works!!![/color]
[color=lightgreen]'Let arrOut(rws, 1) = vTemp(rws - sr + 1, 1) 'Works[/color]
[color=lightgreen]' 'Let arrOut(rws, 1) = IntermediateArray(rws - sr).Value2(rws - sr + 1, 1) 'Won't work[/color]
[color=lightgreen]' Let arrOut(rws, 1) = IntermediateArray(rws - sr).Value2()(rws - sr + 1, 1) 'Works ##[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(dicLookupTable.Items(rws - sr).Value2()(rws - sr + 1, 1), 4) 'Works ##[/color]
[color=lightgreen]' Let arrOut(rws, 1) = Left(IntermediateArray(rws - sr).Value2()(rws - sr + 1, 1), 4) 'Works ##[/color]
[color=blue]Let[/color] arrOut(rws, 1) = Left(vTemp(rws - sr + 1, 1), 4) [color=lightgreen]'Works[/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. 'May not be needed but to keep program flexible should alterations need it.[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'MicrosoftScriptimeRuntimeDictionaryRangeObjectKeysItemsBodge3()[/color]
