Hi, I have spent literally hours debugging one of my VBA routines. Everything was looping perfectly and all the results were as expected accept one. Eventually I worked out that it was the dates. In three of the tests I was using these dates, 01/01/2017, 01/01/2021 & 01/01/2012, for the fourth test I was using today(), currently the 11/04/2021. However, for some inexplicable reason I have yet to work out VBA is treating 11/04/2021 as 04/11/2021 so not giving me the expected results. As soon as I changed the macro input to be 04/11/2021 it all worked as expected. Of course, I never saw the problem on my three earlier tests because 01/01/2012 is exactly the same as 01/01/2012 :-)
I've checked all the settings on my iMac running 10.15.7 using Excel 16.49 I can find and it all seems to be set to United Kingdom or English. Excel is in UK English for all other date activities.
I'd be grateful if someone can help me understand why VBA is reading dd/mm/yyyy dates from a cell into an array as though they were in a mm/dd/yyyy format however when I use debug.print to show the date in intermediate widow it prints dd/mm/yyyy, exactly as it should. This is why it took so long to debug!!
I'm very confused, Thank you for your help.
Regards
Bookmarks