Hi,
Can someone help???
For years (2002) I've been using the same file, full of macros, conditional formatting, formulas.
The document is composed of calendars sheets regenerated regularly with VBA, creating conditional formatting depending on dates and weekdays of the dates, for example: weekend have special colors, when date are reached they are changing of color (all this is set during VBA generation of the sheet). Lots of computation depending on weekdays, holidays, leaves are based on these weekdays...
I've done slowly all the conversions from Excel 2003 to Excel 2016 (Family ed) that I'm using since november 2015 with few problems, and my tool was working perfectly until last week.
Recently there was a Microsoft update of Excel 2016, and my formulas, VBA code and conditional formatting are not working anymore, because now when using format "ddd" for the date (giving previously "Mon", "Tue"...) , there's now a dot "." and the result of VBA for "ddd" is "Mon.", "Tue.", "Wed."(...) instead of "Mon", "Tue", "Wed" as expected and which cannot be recognized by formulas and conditional formatting when hardcoded....
FYI: I'm most often using the French language, but also working on EN computer and my macros are testing the language. Code used is different if FR or EN computer thanks to a Public Function System_Language() checking the language. It's working perfectly
...
CodeLangueSysteme = Application.LanguageSettings.LanguageID(msoLanguageIDUI)
Select Case CodeLangueSysteme
Case 1036, 2060, 11276, 3084, 9228, 12300, 15372, 5132, 13324, 6156, 14348, 58380, 8204, 10252, 4108, 7180: LangueSysteme = "FR"
Case 1033, 2057, 3081, 10249, 4105, 9225, 15369, 16393, 14345, 6153, 8201, 17417, 5129, 13321, 18441, 7177, 11273, 12297: LangueSysteme = "UK"
Case Else: LangueSysteme = "UK"
End Select
...
For example: the below FR formula is now generating week days with a dot like "Sam." instead of "Sam" (for saturday) as expected:
Cnd_Color_Wk_End = "=SI(OU(" & Adr_Col1 & "=""Sam"";" & Adr_Col1 & "=""Sat"";" & Adr_Col1 & "=""Dim"";" & Adr_Col1 & "=""Sun"");VRAI;FAUX)"
the EN one :
Cnd_Color_Wk_End = "=IF(OR(" & Adr_Col1 & "=""Sam""," & Adr_Col1 & "=""Sat""," & Adr_Col1 & "=""Dim""," & Adr_CoI1 & "=""Sun""),TRUE,FALSE)"
And the worse is in the Excel Sheet itself because the VBA generated formula:
=TEXTE("04/07/2016";"jjj")
is producing a weekday with a dot like "Sam."
But when entered manually there's no dot with "Sam", so if I press "Enter" on a formula, it will automatically turn from "Sam." to "Sam" and all the other formulas from the sheet counting the number of "Sam." generated by VBA will not work anymore like below one:
=NB.SI($CK$3:$CK$33;"Lun")+NB.SI($CK$3:$CK$33;"Mar")+NB.SI($CK$3:$CK$33;"Mer")+NB.SI($CK$3:$CK$33;"Jeu")+NB.SI($CK$3:$CK$33;"Ven") + NB.SI($CK$3:$CK$33;"Mon")+NB.SI($CK$3:$CK$33;"Tue")+NB.SI($CK$3:$CK$33;"Wed")+NB.SI($CK$3:$CK$33;"Thu")+NB.SI($CK$3:$CK$33;"Fri")
Of course I can review all my code and add dots everywhere it's needed (15 years of datas!!!), but there will be several mismatch if I click on any cell of the document + if the crazy developper from Micosoft decide to change something else, I'll also become mad.
Question 1: Is there a (new) setting somewhere allowing to set or unset the dot "." at the end of the weekdays "Mon." => "Mon" ??
Question 2: If no new setting, do I have to update all my code (VBA, Sheets, Cond Fmt ...) until the crazy guy who made this stupid change in Excel decide to change something else in the days formatting.
Question 4: If no setting, can we expect Microsoft will quickly remove this stupid update from Excel 2016 or propose a patch to correct this urgently ?
Question 3: Did someone else noticed this ?
Thanks to all and regards
Gerard
