PDA

View Full Version : VBA is Stubbornly Interpreting 10/12 as Date



pkirklewski
02-14-2019, 08:44 AM
VBA is Stubbornly Interpreting 10/12 as Date
https://social.msdn.microsoft.com/Forums/en-US/f3c6afe8-36f1-43e7-b29e-68fd1a0432e4/vba-is-stubbornly-interpreting-1012-as-date?forum=isvvba (https://social.msdn.microsoft.com/Forums/en-US/f3c6afe8-36f1-43e7-b29e-68fd1a0432e4/vba-is-stubbornly-interpreting-1012-as-date?forum=isvvba)



I’ve got a collection and a string variable declared

https://lh4.googleusercontent.com/kTWnfbZ08RFMnMiS7xb8qqoi8UP25Ql9RyZjjjdyocCJick96pafacJ8lChUlGAijZqJh7EPAbw CKx2l2hMuWeAqcOEgEjmyMJ_4zUKs99dTpUODCcrMx4RlCE_Vv_6ENCly5M4_


I select the sizes in my listbox and then populating the myCollection collection from that list

https://lh4.googleusercontent.com/7swOqgI1TdiNmcM0sO2F-3v6e4QA3aBIKOlODrauAZ8v48sCgkgMMWMYahLoQ39PBuForuliJ-wuQGwz5Ui5TO1DDrnNRr1gMdcmBHTaYz2UTEiAQKWd1Mc7704BQGbiKM1NUThC



I am making sure that the currentValue is a string before I paste it in a cell


https://lh3.googleusercontent.com/v171EYEVYCiP47vFobyGoAwuVpDCVWJwVkJr0BqNWU5hGm-K4_CkJKd-lC_lhCYT0pLWGrfuvnocT68k0OkPnKHg-r2TQy2p63AuwCrHogM8pYyk8Zpv8wrOyUHOgAg-KvX5qf3d


Still Excel is interpreting it as date !



https://lh3.googleusercontent.com/izGh5Ihwt3LtINELsYzh8tAqJg9m7e1gCt368hz0bO96ebe6KdT-rT0GXMynK0v1kckNdiqFqlTm64jX-oRNVJCPr58J9qkNfArOcFfo4D9g_fgqNOv_pffxuUmXVvaQapmKSymj

THESE ARE SIZES NOT DATES! I don’t want Excell to format those cells for me and I think I’m making myself clear when I specifically say that this is a STRING and not a date or variant!

Also - each time (despite me formatting the cells manually) Excel annoyingly changes the cell alignment to the right so I need to select the range and change it to xlLeft


https://lh4.googleusercontent.com/huHKcJqjlMzb1kpCZD9HgLz54qwlEAH3ciNyLDT0mX3oG8wO-jKQqiedj6Xis2U5VLS5z-7x441Tk6K8DSYo5jG9HOI4GEuPNWRPcT8-n8l4RkuKKfNiEEO5lDKzuP1mqnmWisju


How do I stop excel formatting the cells for me when I don’t want it to ???


Please help

Paul_Hossler
02-14-2019, 09:04 AM
Excel worksheet tries to be (too) helpful sometimes

Try applying number format first ...





Sheets(1).Cells(j+23,10).Numberformat="@"
Sheets(1).Cells(j+23,10).Value=currentValue




. .. or putting a single quote in front





Sheets(1).Cells(j+23,10).Value="'" & currentValue

Aflatoon
02-14-2019, 09:04 AM
Format the cell as text first using .Numberformat = "@", or prefix the value with an apostrophe.