Hi
Just a follow up, ….
I often find in VBA, that you need to know the answer to be able to know how and what to ask.
So I have here one attempt at an answer I just thought about. I am not sure yet how practical or whether the extras steps needed for this do away with any speed advantages form the original “Magic One Liner” Code Line I was trying to use and improve on…..
So this Post is a partial ( not particularly satisfactory solution to ) as well as an attempt to explain the problem again.
So I To try again to summarize.
For various reasons I wish to use a single code line, ( which I tend to refer to as a “
Magic Code Line”. This is because after many attempts I have found no explanation of how this works ( apart from my own somewhat dodgy explanations ! ) )
So the “
Magic Code Line” works on, for an example, this …….(
rngIn )
Using Excel 2007
Row\Col | K | L | M |
11 | 13.03.2016 | 2 or 12 | 3 or 13 |
12 | P-Tang | Pf-Tang | OLE |
13 | WigWam | DuWolly | Makro |
14 | ModPod | Biscuit | Barrel |
_
~~~~~~~~~…returning me this: (
FieldOut )
13.03.2016 | 3 or 13 |
ModPod | Barrel |
_
~~~~~~~~~~~~~~~…using this, ( the “
Magic Code Line”…..( “pseudo” code )
_
~~~~~~~~~~~~~~~FieldOut = .Index ( rngIn, {1,4}, {1\3} )
~~~~~~~~~~~~~~~~~~~'
~~{1,4} is a 1 “column” 2 Dimension ( 2 Element ) Array of values 1 and 4
~~~~~~~~~~~~~~~~~~~'
~~{1\3} is a 1 Dimension ( 2 Element ) Array of values 1 and 3
~~~~~~~~~~~~~~~~~~~'
~~FieldOut may be an Array or range
~~~~~~~~~~~~~~~~~~~'
~~rngIn I prefer to be a Range
_...........................
_ The above formula generally returns an Array. Mostly that is not a problem. The problem has arisen that a date such as 13.03.2016 comes out as a double like 42442. ( 42442 is of course the Excel "Number" for the date of 13.03.2016 )
_ This problem does not occur if the ( “pseudo” ) code is modifies thus
_
~~~~~~~~~~~~~~~~FieldOut = .Index ( rngIn.Value, {1,4}, {1\3} )
Or
_
~~~~~~~~~~~~~~~~FieldOut= .Index ( arrIn(), {1,4}, {1\3} )
The latter formula was previously serving me fairly well, but with some restrictions. Then I came here,….
http://www.eileenslounge.com/viewtopic.php?f=27&t=22512" onclick="window.open(this.href);return false;
Where I learned that for many reasons it was better to have a Range than an Array in such “Worksheet Functions”
I wish to remain with my Range option within the .Index Function. That is not negotiable here, perticularly
_..............................
I showed in
Rem 4) of my long demo code in the first Post of this Thread that I had a partial solution. Partial being that ( only ) for the case of a single “row”, where a Range object was returned, rather than an Array for FieldOut, ( which could have further advantages ). Applying .Value to the Final Range Object, rather than the Range within the .Index allowed me to keep a Range Object as I wanted in the first argument of the .Index.
_ So
Rem 5 ) is now an adaption of that ”
Rem 4) range object was returned” phenomena to give me an Multi Row Range Output.
So I added a bit to my demo Code:
_ '5a) Just a recap Recap pasting out from "Magic Code line" with Range as First .Index Argument showing the date as coming out like 42442 problem
Row\Col | K | L |
21 | 42442 | 3 or 13 |
22 | ModPod | Barrel |
_...........................................................
_ '5b) Another Recap pasting out from "Magic Code line" with Array as First .Index Argument showing a date as like 13.03.2016.
Row\Col | K | L |
21 | 13.03.2016 | 3 or 13 |
22 | ModPod | Barrel |
So I have in this last Table the date format I want, but have an Array as First argument in the .Index, which I do not want
So that basically summarizes and explains again what this Thread is about
_............................................................
_ 5c) So from I am showing my new “Workaround”. Full details are in the code.
The Summary as brief as I am able I give now, :
The “slicing” technique of
Rem 4) is used in….
'5c)(i) To produce an Array housing the required Rows as 1 Dimensional ( row ) Ranges
So in the example given, I have “Rnts”
Of ( as Range Objects )
~~~13.03.2016
~~~~~~~2 or 12
~~~~~3 or 13
And
~~~~ModPod
~~~~~~~~Biscuit
~~~~Barrel
'5c)(ii) To produce an Array housing the required Columns as 1 Dimensional ( column ) Ranges
So in the example given, I have “Cnts”
Of ( as Range Objects )
~~~~13.03.2016
~~~~~~P-Tang
~~~~~WigWam
~~~~~~ModPod
And
~~~~~3 or 13
~~~~~~~OLE
~~~~~~Makro
~~~~~~Barrel
'5c)(iii) For each "long" row Range Element, I take every "deep" column Range Element. The Application.Intersect-Methode is then used to give the Common Single cell Range Object. ( where the row and the columns intersect ). This is given to an Array of range Objects, rngFieldOut()
Interestingly the .Value Property can be applied to this Range in a “one liner” to return an Array of values. Sadly in this case we get a Date in the undesired Format.
So
'5c)(iv) A final loop is required to give an Array from which the required date Format is obtained. ( Could be incorporated in previous Loop, but shown separately for clarity )
_.....................................................
_ I expect this should be left now as an academic curiosity. And the final conclusion is that for the “Magic Code Line” dates should be avoided in the original Range. Failing that
ether
add the .Value Property bit to the first argument, effectively converting to an Array and living with the disadvantages ( such as Array size limitations, and slower codes )
Or
use the
Rem 5c) workaround.
This final
Rem 5c) workaround may, due to the extra Looping, do away with other advantages of using the “
Magic Code Line”, and a simple looping along the lines of normal “Spreadsheet Interaction Looping through Codes ….
HansV wrote:But you could use your criteria (whatever they may be) to determine which cell(s) you want to copy, then use the Copy method on that cell/those cells...
_
~~~~~~~~~…. Would be just as quick, and a lot less complicated!!!!
_ _
~~~~~~~~~~~~~~……I guess I just wanted to get it out of my system and have it for my or anyone else’s further reference interest..
Thanks for the Platform for allowing me to do that.
Thanks for watching a last time
Alan
P.s. Finally to wrap it up…..
Rem 6) In the final Demo Code:
This section calls up a
Function,
Public Function MagicLineCodeDateWonks
( also included below ) .
This
Function takes in the initial Input Range, RngIn, the required Indices for the Final range output in the Indicia Arrays’, rwsT() and clms() Array, along with a Boolean Which for
True indicates that a Date may be present in the Initial Input range requiring the
Rem 5) workaround discussed in this Post.
So in this
Function the Final required Array of Range Elements is returned either using the Simple “
Magic Code Line”, or the Workaround discussed in this Post
_..............................................
Main Demo code including
Rem 5) and
Rem 6)
'
Code: Select all
'
Sub RangeValueDateAnomolieIndex() '
10 Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("NPueyoGyanArraySlicing") ' CHANGE TO SUIT YOUR WORKSHEET
20 '
30 Dim RngIn As Range 'Variable to hold required Info for a assigning Range Object to this Variable
40 Set RngIn = ws.Range("K11:M12") '
50 '
60 Dim ValueRequired As Variant ' Variant chosen here to be on the safe side, allowing for date format etc.
70 Dim RowRequired() As Variant 'To be used for our Row Output Expected to be a Field of Variant Types as returned by various Methods and Properties
80 Rem 1) 'Array Method ( 1st Argument Limitation , 255 columns )
90 Dim arrIn() As Variant 'Input Array to be obtained by various Methods returning a Field of Variant Elements
100 Let arrIn() = RngIn.Value 'Typically used "one liner" allowed VBA assignment of Range values too an Array
110 Let ValueRequired = Application.Index(arrIn(), 1, 1) 'Single value Variant housing String
120 Let RowRequired() = Application.Index(arrIn(), 1, Array(1, 2, 3)) 'Returns Array(1 to 3) of Variant Types Housing Strings
121 Let ws.Range("K9").Value = RowRequired(1) 'Gives date Format( as String ) . Good !
130 '
140 Rem 2) 'Range Second Argument overcomes 255 columns Limit, But for Entire Row any Date is converted to Double
150 ' 2a) specific range
160 Let ValueRequired = Application.Index(RngIn, 1, 1) 'Single value Variant housing Date
170 Let RowRequired() = Application.Index(RngIn, 1, Array(1, 2, 3)) 'Returns Array(1 to 3) of Variant Types Housing Strings and Double in first Element(1)
171 Let ws.Range("K9").Value = RowRequired(1) 'Gives Double Format. Bad !
180 'or '2b) use Cells as Entire Spreadsheet Range
190 Let ValueRequired = Application.Index(Cells, 11, 11) 'Single value Variant housing Date
200 Let RowRequired() = Application.Index(Cells, 11, Array(11, 12, 13)) 'Returns Array(1 to 3) of Variant Types Housing Strings and Double in first Element(1)
201 Let ws.Range("K9").Value = RowRequired(1) 'Gives Double Format. Bad !
210 '
220 Rem 3) 'Temporary Workaround to maintain date Format
230 'Workarounds ( effectively simply returning to Array as First Argument )
240 Let RowRequired() = Application.Index(RngIn.Value, 1, Array(1, 2, 3)) 'Returns Array(1 to 3) of Variant Types Housing Strings
242 Let RowRequired() = Application.Index((RngIn), 1, Array(1, 2, 3)) 'Returns Array(1 to 3) of Variant Types Housing Strings ( Extra Parens ( ) seems to evoke an Evaluation )
243 Let ws.Range("K9").Value = RowRequired(1) 'Gives date Format ( As String ) . Good !
'250 Let RowRequired() = Application.Index(Cells.Value, 11, Array(11, 12, 13)) ' Error Runtime 7 Not enough memory ( for Array Cells.Value ). ( Would not work anyway as array is over limit 65535 x 255 )
250 '
260 Rem 4) Workaround only applicable to using Slicing Technique for a single Row with Specific Range Object
270 Dim rngRowRequired As Range
280 Set rngRowRequired = Application.Index(RngIn, 1, 0) 'Returns Range Object!!
' 285 Set rngRowRequired = Application.Index(rngIn, 0, 1) 'Returns Range Object!!
290 Let RowRequired() = rngRowRequired.Value 'Returns Array(1 to 1, 1 to 3) of Variant Types Housing Strings and # Date # in first Element(1, 1)
300 Let RowRequired() = Application.Index(RngIn, 1, 0).Value
301 Let ws.Range("K9").Value = RowRequired(1, 1) 'Gives date Format. Good ! NOTE FORMATS CELL AS DATE !
350
400 Rem 5)
410 '5a) Recap pasting out from "Magic Code line" with Range as First .Index Argument showing a date as like 42442 problem
420 Set RngIn = ws.Range("K11:M14") 'Our initial Test Range ( a bit extrended ). Arbritrary 3row x 4column Range
430 Dim FieldOut() As Variant 'Array to house are wanted Output. The reduced size Grid based on Selection will be returned by a Method likely to return a Field of Variant Type Elements
440 Dim rwsT() As Variant, clms() As Variant ' Arrays necerssary for required "row" and "column" indicia in "Magic Code Line". Methods Returning a Fields of Variant Types are used so we Dimension appropriately
450 Let rwsT() = Application.Transpose(Array(1, 4)) ' ' {1,4} is a 1 "column" 2 Dimension ( 2 Element ) Array of values 1 and 4
460 Let clms() = Array(1, 3) ' ' {1\3} is a 1 Dimension ("pseudo horizontal") ( 2 Element ) Array of values 1 and 3
470 Let FieldOut() = Application.Index(RngIn, rwsT(), clms()) 'My Preferred version of magic code line, but.....
480 ws.Range("K21:L22").Clear 'Make sure test Output Range is clear of all contents and Formating that might effect results
490 ws.Range("K21:L22").Value = FieldOut() '.....for the case of a cell with a date in my date comes out as a Double Number, like 42442
500 'Dim arrIn() As ' Already done earlier in Code
510 '5b) Recap pasting out from "Magic Code line" with Array as First .Index Argument showing a date as like 13.03.2016
520 Let arrIn() = RngIn.Value ' .Value Property for more than 1 cell returning a Field of variant Element types
530 ws.Range("K21:L22").Clear
540 Let FieldOut() = Application.Index(arrIn(), rwsT(), clms()) 'This gives me the correct date Format, like
550 ws.Range("K21:L22").Value = FieldOut() '.......like what i want, but I prefer not to have second arguments as an Array()
560 'or
570 ws.Range("K21:L22").Clear
580 Let FieldOut() = Application.Index(RngIn.Value, rwsT(), clms()) ' 'This gives me the correct date Format, like...13.03.2016..
590 ws.Range("K21:L22").Clear
600 ws.Range("K21:L22").Value = FieldOut() '.......like what i want, but I prefer not to have second arguments as an Array()
610 '5c) Workaround allowing "Magic Code Line" to have a Range as second Argument, whilst not converting a date to a doiuble Format.
620 '5c)(i) To produce an Array housing the required Rows as 1 Dimensional ( row ) Ranges
630 Dim arrRnts() As Range 'An Array of Rows required as Ranges
640 ReDim arrRnts(1 To UBound(rwsT(), 1)) '
650 Dim Rnt As Long 'Loop Bound Variable Counts. I intend looping in the Workaround of rem 4 into a "pseudo" multi row range
660 For Rnt = 1 To UBound(rwsT(), 1)
670 Set arrRnts(Rnt) = Application.Index(RngIn, rwsT(Rnt, 1), 0) 'Based on the indicie in rwsT(Rnt, 1), the "slicing" technique returns the full required row ( as a " long" row in this case ) Range Object
680 Next Rnt
690 '5c)(ii) To produce an Array housing the required Columns as 1 Dimensional ( column ) Ranges
700 Dim arrCnts() As Range 'An Array of Columns required as Ranges
710 ReDim arrCnts(1 To (UBound(clms()) + 1)) '+1 as clms() is at default Base 0 ** ( "Internal" Arrays starint at indicia 0 )
720 Dim Cnt As Long 'Loop Bound Variable Counts. I intend looping in the Workaround of rem 4 into a "pseudo" multi row range
730 For Cnt = 1 To (UBound(clms(), 1) + 1)
740 Set arrCnts(Cnt) = Application.Index(RngIn, 0, clms(Cnt - 1)) '-1 because of Base 0 ** ' 'Based on the indicie in clms(Cnt - 1), the "slicing" technique returns the full required row ( as a "deep " column in this case ) Range Object
750 Next Cnt
760 '5c)(iii) For each "long" row Range Element, every "deep" column Range Element, the Application.Intersect-Methode is used to give the Common Single cell Range Object
770 Dim rngFieldOut() As Range 'To become an Array of the cells I want as Range objects in row and column order as given by code lines such as those in Rem 5b)
780 ReDim rngFieldOut(1 To UBound(rwsT(), 1), 1 To UBound(clms()) + 1)
790 For Rnt = 1 To UBound(rwsT(), 1)
800 For Cnt = 1 To (UBound(clms(), 1) + 1)
810 Set rngFieldOut(Rnt, Cnt) = Application.Intersect(arrRnts(Rnt), arrCnts(Cnt))
820 Next Cnt
830 Next Rnt
840 ws.Range("K21:L22").Clear
850 ws.Range("K21:L22") = rngFieldOut() 'This actually is syntaxly OK , but gives me a date like 42442 which I do not want.
860 '5c)(iv) A final loop required to give an Array from which the required date Format is obtained
870 ReDim FieldOut(1 To UBound(rwsT(), 1), 1 To UBound(clms()) + 1)
880 For Rnt = 1 To UBound(rwsT(), 1)
890 For Cnt = 1 To (UBound(clms(), 1) + 1)
900 Let FieldOut(Rnt, Cnt) = Intersect(arrRnts(Rnt), arrCnts(Cnt)).Value
910 Next Cnt
920 Next Rnt
930 ws.Range("K21:L22").Clear
940 ws.Range("K21:L22").Value = FieldOut() 'Finally required Output Foramt including the case of an initial Range including Dates
950 Rem 6) Function Call' Based on Boolean False or True the Simple "Magic Code Line is done or a code along the lines of Rem 5c)
960 Let FieldOut() = MagicLineCodeDateWonks(RngIn, rwsT(), clms(), False)
970 ws.Range("K21:L22").Clear
980 ws.Range("K21:L22") = rngFieldOut() 'Gives me a date like 42442 which I do not want.
990 Let FieldOut() = MagicLineCodeDateWonks(RngIn, rwsT(), clms(), True)
992 ws.Range("K21:L22").Clear
995 ws.Range("K21:L22").Value = FieldOut() 'Finally required Output Format including the case of an initial Range including Dates
End Sub
_...............................................
Pubic Function
Public Function MagicLineCodeDateWonks(
_________,
______)
'
Code: Select all
Public Function MagicLineCodeDateWonks(ByRef RngIn As Range, ByRef rwsT() As Variant, ByRef clms() As Variant, DteBdg As Boolean) As Variant 'We are not modifying any Taken Variables so leaving the Arrays as ByRef avoids the long "Discusion" of whether ByVal is possible http://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4414307 For the Function itself, the only type that can return an Array is the Variant
If DteBdg = False Then 'Case No Dates In Initial Input Range, Simple Magic Code One liner can be used
Let MagicLineCodeDateWonks = Application.Index(RngIn, rwsT(), clms())
Else 'For Input ranges containing dates
'5c) Workaround allowing "Magic Code Line" to have a Range as second Argument, whilst not converting a date to a doiuble Format.
'5c)(i) To produce an Array housing the required Rows as 1 Dimensional ( row ) Ranges
Dim arrRnts() As Range 'An Array of Rows required as Ranges
ReDim arrRnts(1 To UBound(rwsT(), 1)) '
Dim Rnt As Long 'Loop Bound Variable Counts. I intend looping in the Workaround of rem 4 into a "pseudo" multi row range
For Rnt = 1 To UBound(rwsT(), 1)
Set arrRnts(Rnt) = Application.Index(RngIn, rwsT(Rnt, 1), 0) 'Based on the indicie in rwsT(Rnt, 1), the "slicing" technique returns the full required row ( as a " long" row in this case ) Range Object
Next Rnt
'5c)(ii) To produce an Array housing the required Columns as 1 Dimensional ( column ) Ranges
Dim arrCnts() As Range 'An Array of Columns required as Ranges
ReDim arrCnts(1 To (UBound(clms()) + 1)) '+1 as clms() is at default Base 0 ** ( "Internal" Arrays starint at indicia 0 )
Dim Cnt As Long 'Loop Bound Variable Counts. I intend looping in the Workaround of rem 4 into a "pseudo" multi row range
For Cnt = 1 To (UBound(clms(), 1) + 1)
Set arrCnts(Cnt) = Application.Index(RngIn, 0, clms(Cnt - 1)) '-1 because of Base 0 ** ' 'Based on the indicie in clms(Cnt - 1), the "slicing" technique returns the full required row ( as a "deep " column in this case ) Range Object
Next Cnt
'5c)(iii) For each "long" row Range Element, every "deep" column Range Element, the Application.Intersect-Methode is used to give the Common Single cell Range Object
Dim rngFieldOut() As Range
ReDim rngFieldOut(1 To UBound(rwsT(), 1), 1 To UBound(clms()) + 1)
'5c)(iv) Also in loop required Array to give an Array from which the required date Format is obtained
Dim FieldOut() As Variant
ReDim FieldOut(1 To UBound(rwsT(), 1), 1 To UBound(clms()) + 1)
For Rnt = 1 To UBound(rwsT(), 1)
For Cnt = 1 To (UBound(clms(), 1) + 1)
Set rngFieldOut(Rnt, Cnt) = Application.Intersect(arrRnts(Rnt), arrCnts(Cnt)) '5c)(iii)
Let FieldOut(Rnt, Cnt) = Intersect(arrRnts(Rnt), arrCnts(Cnt)).Value '5c)(iv)
Next Cnt
Next Rnt
Let MagicLineCodeDateWonks = FieldOut() 'Final required assignment of our FieldOut() Array to the Array Returned by this Function For the case of the workaround being needed
End If 'End of Boolean check for if the workaround is required
End Function
_.......................
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also