VBA .Index Returned Date Format Different for Range or Array

User avatar
Doc.AElstein
BronzeLounger
Posts: 1403
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

VBA .Index Returned Date Format Different for Range or Array

Post by Doc.AElstein »

VBA .Index Returned Date Format Different for Range or Array First Argument

Hi,
~~~This is not urgent as I have a Workaround. I mention it both for others to see, and also to ask if anyone can understand what is going in here, as in other Codes the Workaround I have may not be appropriate.

~~~I will try and demo with much simplified Code and scenario

~~~Consider this simple Spreadsheet Range as my Input Range of Raw Data. (Range K11:M12 )
Using Excel 2007
Row\ColKLM
1113.03.20162 or 123 or 13
12OLEWigwamBiscuit-Barallel
~~~~
Lets say my aim is to give this in Cell K9 based on some criteria for picking a particular Cell from the Raw data.
Row\ColK
913.03.2016
( Effectively, (based on criteria), Cell K11 from original data is selected and put in cell K9 )
_...............................

For some time I had been using codes of the type given in Rem 1) of the demo Code below. This had some Array size Limitations in the First Argument of the .Index Method

Following participation in this Thread , and in particular this post…..
http://www.eileenslounge.com/viewtopic. ... 12#p175343" onclick="window.open(this.href);return false;
~~~~~~~~~~~~~ ….. I was able to overcome the size limits as well as obtain some speed advantages by replacing the First Argument Array with a Range Object ( Rem 2a ) ) . ( Some further speed advantages as well as further flexibility was found by using Cells as the Range object ( Rem 2b) )

~~~~~So far so good ! :smile:

_........................

~~~~~A problem has now arisen in the event that any Cell in the required Range had a Date in. For some strange reason the First argument as Range Object way is returning me a double number rather than a Date Format. ( Code Lines 170 and 200 ).
Row\ColK
942442
~~~~~In lines 240 and 242 in Rem 3) I overcome the problem with a workaround. But this simply reverts back to having the First Argument as An Array :scratch:

(……~~~~~In addition I also have an interesting workaround for the case of requiring a single Row in Rem 4 ). This is only applicable to a single Row and restricted to a specific Range and not Cells as the First Argument. This is using the interesting result That a Range Object is returned in using this “Slicing” Technique*** Applying the .Value property is then possible to the Final Range Object and appears to maintain the correct date Format. ( But Note !!! - in my Excel it Re Formats the Cell to date format also !!! ) ….)

~~~~~So this is not a big problem for me just now, but I am just asking if anyone can understand why this is happening, and if I can somehow get the Date format from my Row getting lines in code section Rem 2 ) ( Lines 170 ( 171 ) and 200 (201) ) without resorting back to the First argument being an Array


~~~~~So to summarize. It is not a big problem, but an awkward problem that can catch you out…. I would prefer to use a Range ( or better still Cells ) as 1st .Index argument. But if I may expect a date it does a strange conversion to the VBA “Number “ for a Date.

Thanks for Watching
Alan

_....................

Demo Code: ( Note !!! the code will appear to give the correct Date Format in all cases on a second Run as the Cell K9 will be left in Date Format from the previous Run. ( caused by line 301 ) . - This initial situation may not be possible in a typical Application). ( It would therefore also be a workaround to reformat the cells after the main code ran, but in an application this could also not be appropriate, as the selection based on various criteria may or may not be of Date Format )

'
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!!
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 !
End Sub
Last edited by Doc.AElstein on 17 Oct 2016, 20:30, edited 1 time in total.
\ -_- / :heavy: :jollyroger:

User avatar
HansV
Administrator
Posts: 72002
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA .Index Returned Date Format Different for Range or A

Post by HansV »

Why don't you simply copy K11 to K9:

Range("K11").Copy Destination:=Range("K9")

This will copy the value AND the formatting (including the date format).
Regards,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1403
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Index Returned Date Format Different for Range or A

Post by Doc.AElstein »

Hi Hans,

Thanks for the quick reply

I probably over simplified in trying to bring across the basic Problem. In the practice I would have a large range and based on criteria (….as in my Thread http://www.eileenslounge.com/viewtopic.php?f=27&t=22512" onclick="window.open(this.href);return false; …)

A single row, for example, would be extracted using .Index

Sorry I did not make it too clear. The code I gave was just intended to demonstrate that when I use the .Index and have first arguments as a Range, rather than an Array then I can get the awkward problem whereby a Date format comes out in its Number. In the applications I am using .Index is for many reasons being used. That is fixed. An improvement I learned here was to use a range rather than an Array as First Argument.

So The issue is not just copying and pasting

I apologize; it is difficult not to give too much or too little info. I think due to my bad description you have completely missed the point. Sorry.

Alan
\ -_- / :heavy: :jollyroger:

User avatar
HansV
Administrator
Posts: 72002
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA .Index Returned Date Format Different for Range or A

Post by HansV »

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...
Regards,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1403
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Index Returned Date Format Different for Range or A

Post by Doc.AElstein »

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...
Correct. That would be another work around. Or rather that would be a completely different method to using the .Index .

In the practice I use a single line code line of the form ( Pseudo code )

OutputField = Application.Index(RangeIn, rws(), clms() )
or
OutputField = Application.Index(Cells, rws(), clms() )
Where rws() and clms() are Arrays of required “row” and “column” indices within RangeIn ( or Worksheet Cells )
http://www.excelforum.com/excel-new-use ... rrays.html" onclick="window.open(this.href);return false;
http://www.excelforum.com/excel-program ... ost4238685" onclick="window.open(this.href);return false;
https://usefulgyaan.wordpress.com/2013/ ... ion-index/" onclick="window.open(this.href);return false;


Again I wish to remain with the basic code line form. I appreciate it is a big demo code. I was not expecting such a quick reply. Again I think the main point is not coming across. Sorry

Alan
\ -_- / :heavy: :jollyroger:

User avatar
Doc.AElstein
BronzeLounger
Posts: 1403
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Index Returned Date Format Different for Range or A

Post by Doc.AElstein »

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\ColKLM
1113.03.20162 or 123 or 13
12P-TangPf-TangOLE
13WigWamDuWollyMakro
14ModPodBiscuitBarrel


_~~~~~~~~~…returning me this: ( FieldOut )
13.03.20163 or 13
ModPodBarrel
_~~~~~~~~~~~~~~~…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\ColKL
21424423 or 13
22ModPodBarrel
_...........................................................

_ '5b) Another Recap pasting out from "Magic Code line" with Array as First .Index Argument showing a date as like 13.03.2016.
Row\ColKL
2113.03.20163 or 13
22ModPodBarrel
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
_.......................
:toilet:
:heavy:
\ -_- / :heavy: :jollyroger:

User avatar
Doc.AElstein
BronzeLounger
Posts: 1403
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Excel 2007 Issue

Post by Doc.AElstein »

EDIT 24.03.2016: : The following 3 Posts ( 3 in total including this one ) were moved form Here:-*******
http://eileenslounge.com/viewtopic.php?p=177683#p177683" onclick="window.open(this.href);return false; ********
So there is some references to that Thread and the some of the Data Files. But the relevant example data is also included in the File uploaded in this Post. Alan.
_.................................................................................................





Hi
The Thread Title and the contents, including the good explanation of how Excel handles dates**********, caught my eye, and I thought I might just tack this follow up on… as I have may be an Anomaly on how Excel is handling dates, may be…..
I have workaround solutions, …….
It is a bit related then to this Thread.
http://www.eileenslounge.com/viewtopic.php?f=30&t=22787" onclick="window.open(this.href);return false;
So more I am posting out of interest. to advise possibly of a Bug...
_......................

As briefly as I can explain: I may wish to use what I call a “Magic Code Line” to give me a small Part of a larger Range. This range may have some dates in it. As an example, Using, for example, the Test Data Range used in the File from this Thread, ( here just a small part thereof: )

Using Excel 2007
Row\ColABCD
1FileTimestampFirstRecordTimestamp
21602290001052016.02.29 00:01:051602282355212016.02.28 23:55:21
31602290006472016.02.29 00:06:471602290001212016.02.29 00:01:21
41602290011322016.02.29 00:11:321602290007212016.02.29 00:07:21
51602290016562016.02.29 00:16:561602290012222016.02.29 00:12:22
61602290022012016.02.29 00:22:011602290017212016.02.29 00:17:21
_.......................

Now, say I wish to use my so called “Magic Code Line” ( reasons for using this “Magic Code Line” were discussed previously and a bit too long to discuss again here ).
Say I wish to use the “Magic Code Line” to give me just the data from rows 2 and 4 and columns 1 and 2
So I want this:
1.60229E+1129.02.2016 00:01:05
1.60229E+1129.02.2016 00:11:32
( The date Month , Day , Year may or may not turn around a bit due to my settings, but that is not an issue here )

The demo Code given below shows various realizations of my “Magic Code Line” used to achieve the above output. Here just one of them:

MyDatesArray() = Application.Index(AllDatesArray(), [ { 2 ; 4 } ], [ { 1 , 2 } ])

I will not labor the point again as I went into detail in the other Thread. And I upload a File here with the below Demo Code in which by stepping through shows clearly I think what I am getting at. ( The issue here is not how else to do this by a method other them the “Magic Code line” ).
So, Assuming I do want to use this method then something strange happens if I use a Range rather than an Array() as the first Argument in the .Index.

MyDatesArray() = Application.Index(rngAllDates, [ { 2 ; 4 } ], [ { 1 , 2 } ])

In such a case I get this: ( Either pasted out to a Spreadsheet or by viewing the contents of MyDatesArray() in the watch Window )
1.60229E+1142429.00075
1.60229E+1142429.00801
So you see I appear to have some “Anomaly” in how Excel is handling dates in some cases. Somehow in the latter case it gives me the Double Representation of the date as a Number, ( The Format as explained by Hans. ) .
( In a particular real life case I may not know where dates are so formatting the cell to get back to the date format is not an option
( I think – Maybe a code could guess if it looks like the VBA Date Number and then change caell format to Date, but that might be a bit complicated, maybe ? )
_...........................................

But At the end of the day this “Magic Code line” , in the form of the first argument using Cells, or a smaller Spreadsheet Range, ( or in the form of the first argument using an Array() for that matter ), is not used too much , and apart from my somewhat dodgy explanations, I have never seen it well explained, so I guess that alone is a good reason to be wary of it, and may be to think again about using it.!!

I will leave this one then finally for now, but I thought just for future reference I might add it here as a “Date Anomaly ( possible Bug ) to be aware of….

Alan
_.......................................

Demo Code

Code: Select all

 
Sub IndexDateAnomalie() ' Date to Double Anomoly
10  Rem 1 ) 'Worksheets Info
20  Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Hansdates")
30  Dim rngAllDates As Range: Set rngAllDates = ws.Range("A1:G17") 'A "Used Range" including some dates for Demo
40  Rem 2 ) ' "Magic Code line" with Cells or (any Range Object) as First Argument
50  Dim MyDatesArray() As Variant 'Array dimensioned to take the Variant Element Type Field  fro the VBA .Index method
60  Let MyDatesArray() = Application.Index(Cells, Application.Transpose(Array(2, 4)), Array(1, 2))
70  Let MyDatesArray() = Application.Index(Cells, Application.Transpose(Array(2, 4)), [ { 1 , 2 } ])
80  Let MyDatesArray() = Application.Index(Cells, Application.Transpose([ { 2 , 4 } ]), [ { 1 , 2 } ])
90  Let MyDatesArray() = Application.Index(Cells, [ { 2 ; 4 } ], [ { 1 , 2 } ])
100 Let MyDatesArray() = Application.Index(rngAllDates, [ { 2 ; 4 } ], [ { 1 , 2 } ])
110 ws.Range("A20").Resize(UBound(MyDatesArray(), 1), UBound(MyDatesArray(), 2)).Clear 'Clear All data And Format From Test output Range
120 Let ws.Range("A20").Resize(UBound(MyDatesArray(), 1), UBound(MyDatesArray(), 2)).Value = MyDatesArray()
130 Rem 3 ) ' "Magic Code line" with an Array as First Argument
140 Dim AllDatesArray() As Variant: Let AllDatesArray() = rngAllDates.Value ' "Capture" Test R to an Array
150 Let MyDatesArray() = Application.Index(AllDatesArray(), [ { 2 ; 4 } ], [ { 1 , 2 } ])
151 Let MyDatesArray() = Application.Index(rngAllDates.Value, [ { 2 ; 4 } ], [ { 1 , 2 } ])
160 ws.Range("A20").Resize(UBound(MyDatesArray(), 1), UBound(MyDatesArray(), 2)).Clear 'Clear All data And Format From Test output Range
170 Let ws.Range("A20").Resize(UBound(MyDatesArray(), 1), UBound(MyDatesArray(), 2)).Value = MyDatesArray()
180 Rem 4 ) ' Simple Index "behaves"!
190 Dim MyDate As Variant
200 Let MyDate = Application.Index(Cells, 2, 2)
210 ws.Range("B22").Clear
220 Let ws.Range("B22").Value = MyDate
End Sub
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 17 Oct 2016, 20:29, edited 2 times in total.
\ -_- / :heavy: :jollyroger:

User avatar
rory
5StarLounger
Posts: 742
Joined: 24 Jan 2010, 15:56

Re: Excel 2007 Issue

Post by rory »

It's not a bug, simply the difference between the Value and Value2 properties of a range.
Regards,
Rory

User avatar
Doc.AElstein
BronzeLounger
Posts: 1403
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Excel 2007 Issue

Post by Doc.AElstein »

Hi Rory,
Thanks for the quick reply.
I used the word "Bug" a bit loosely ( or wrongly ) , Maybe "not too clearly documented "thing" " would have been better!

Somewhere along the line I did think something along those lines, and experimented with the .Value2
I think I understand. - I can get my Array() version of the “Magic Code Line”, “not to work” as it were, if I use
.Value2
Instead of
.Value
That is to say I get back to the Double again.
_............................................

It is not a big problem as I just then add the .Value to a Range if I think I may have dates in the main Range.

Just a bit of a shame.

Cells seemed to work a bit quicker and of course then I am not limited in the . Index ( the first argument at any rate ) to the Array size limitations. And of course if I do Cells.Value my computer bombs out ( at least in XL 2007 + ) . So I have an additional step to set a Smaller Range to do the .Value to.

I guess this is just a case of the old “Implicit defaults” working against me here.

I doubt I can somehow coerce to get it to give the .Value by default instead. ?
May be just one of those subtle things where you generally think that the “range default Implicit thing” is Value. But then occasionally it catches you out that it is .Value2
But anyway when dates are involved things often get messed up anyway.

Alan

P.s. I am not quite sure how the following ties up with what we are saying:

My line 200 in my demo code
MyDate = Application.Index(Cells, 2, 2) ' "Works" Returns Date Format
Seems to “work” as I want.
So does this
MyDate = Application.Index(rngAllDates, 2, 2) ' "Works" Returns Date Format

(_............. As expected also this “works”
Application.Index(rngAllDates.Value, 2, 2) ' "Works" Returns Date as a String
And similarly as expected this does not”work” ( that is to say returns a Double) )
MyDate = Application.Index(rngAllDates.Value2, 2, 2) ' "Does not Work" Returns Double
_...........)

So the Implicit “works” for me in this case…. I am bit surprised the default is not .Value2 in this case?????
Last edited by Doc.AElstein on 24 Mar 2016, 20:05, edited 1 time in total.
\ -_- / :heavy: :jollyroger:

User avatar
Rudi
gamma jay
Posts: 25315
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: VBA .Index Returned Date Format Different for Range or A

Post by Rudi »

Hi Alan,

I have moved some of your posts from this source thread into this current thread as the topic of the posts relate better to this discussion.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1403
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Index Returned Date Format Different for Range or A

Post by Doc.AElstein »

OK. :smile:
Thanks for letting me know,
( Sorry for any trouble. )
Alan
\ -_- / :heavy: :jollyroger:

User avatar
Doc.AElstein
BronzeLounger
Posts: 1403
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Index Returned Date Format Different for Range or A

Post by Doc.AElstein »

Hi
Just adding a solution or rather the conclusion to this Thread for any future references. ..
In the meantime I have seen no real solution possible, ( but infinite work-a-rounds )

From here: http://www.eileenslounge.com/viewtopic. ... 45#p177349" onclick="window.open(this.href);return false;
we had Input Range object of single Area of contiguous Cells, rngIn.
3x2RangeKLM1112.JPG http://imgur.com/nPq9wSs" onclick="window.open(this.href);return false;
3x4RangeKLM1112.JPG
The aim was using .Index with a first argument of a Range Object, to return a multi dimensional Array of outputs rather than a single Output.

I went through lots of ways to get such an Array in this Thread and in the meantime have repeated a lot of the experiments, so…

Final Conclusion.
There are infinite workarounds, but the specific problem here I think is not possible to solve. That is to say when using the .Index with a first argument of a Range Object, to return a multi dimensional Array, then you will have to live with getting .Value2 Double type values returned in an Array
Why that happens… is probably anyone’s guess..


_.....

Some-work-a-rounds:
_ The simplest solution is to add a .Value, thus replace rngIn with rngIn.Value.
I note even with a Variant type Array, the Date format will be “lost”. – You will get the “Date” you want, but as a String.
With the .Value work-a-round you are then left with various Array caused limitations, overcoming of these limitations was the original point of this and some associated Threads
_ A partial solution not subject to Array limitations can be used for an output of a single row or column using the Slicing technique which will return A Range Object, provided the receiving variable is Declared, ( Dim ed as such )

_.....
The “Answer”
as best we will get ( assuming no one from Microsoft would tell better ! ) is probably of the Form:
When using Index with second and third arguments, as Arrays of Rows and / or columns, rather than single values, then an Array of values will be returned rather than an Array of Range Objects. The values returned will be the .Value2 values. The fact that for the .Value work-a-round the date format converted to a string is strange. It somehow stills sees the difference between a String and a Number and an Empty but not Date. So we get “more detail” out from an Array as first argument than a Range Object. Strange.
The thing is somehow “wired” to take the .Value2 from a Range Object.
In the case of a Variant Type member Element Array it almost takes its .Values

Would lead to madness perusing further why I guess.

Problems are often encountered when comparing dates even when they appear to be of identical format. Often every occurrence seems to have a unique workaround. But doing all comparisons in .Value2 appears the most robust.., Using the .match appears to be an example of this. But as with my usage in the it means you are likely to have to extra steps to get the final output as you wish..
Converting to String in VBA or Text in a Spreadsheet comes second best I find sometimes.

Alan

:heavy:
You do not have the required permissions to view the files attached to this post.
\ -_- / :heavy: :jollyroger: