DocAElstein
01-20-2021, 10:34 PM
Some time around Middle January 2021 some strange corruptions occurred.
I think they all appeared at the same time, and it could have been on 14th January, 2021. But I am not 100% sure.
This thread caught my attention first,
https://excelfox.com/forum/showthread.php/2302-quot-What%c3%a2%e2%82%ac%e2%84%a2s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string
but I have seen the problem elsewhere also
here is a screen shot from now
As seen in January 2021
https://i.imgur.com/x3EmtPi.jpg
For comparison with what it was like previously, we can find many archived versions, for example http://web.archive.org/web/20200321110623/https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string
_ Archived version from March 2020
https://i.imgur.com/tY5A9qG.jpg
It looks like we have a problem with the non straight quote things
‘
“
”
Its very difficult to analyse these characters because they have habit of changing depending on where you copy and paste them from or to For example, Some of us may have had the experience that when copying a code from somewhere into a code module, quotes get changed to the non straight ones resulting in syntax errors in a macro
Sub HelloGoodQuotes()
MsgBox Prompt:="Hello Good straight Quotes"
End Sub
Sub HellobadQuotes()
MsgBox Prompt:=“Hello Bad non straight Quotes”
End Sub
If I do my WotchaGotMacro, ( coincidentally shared at the example corrupt Thread) , .._
Sub excelfoxCoruptions() ' https://excelfox.com/forum/showthread.php/2704-Test-excelfox-Corruptions-January-2021
' “He” & “l” & “l” & “o” & vbCr & vbLf t’s
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff("“’”“”") ' https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string
End Sub
_.. then I get these results:
ChrW(8220) & ChrW(8217) & ChrW(8221) & ChrW(8220) & ChrW(8221)
Row\Col
E
F
1
20 Jan 2021
Lenf is 5“’”“”
21 “8220
32 ’8217
43 ”8221
54 “8220
65 ”8221
Worksheet: WotchaGotInString
They seem to be tying up with published info, such as here : https://www.vertex42.com/ExcelTips/unicode-symbols.html
_.___________________
I can repair , or rather replace, my corrupt posts from the copy of the posts that I have in Microsoft Word.
To be on the safe side, to help avoid such corruptions in the future, I should probably best change all my quotes in text to straight ones. One small snag in this is that trying to do it manually returns some peculiar results. It seems that Microsoft Word has some rules / processes which convert quotes. I don't understand them yet.
Luckily a macro does not seem to suffer from these problems: If I select the text in which I want to "sanitise" the quotes, the run this macro, then all my quotes get changed to straight ones
Sub QuoteChange() ' Ctrl+Shift+q
With Selection
' .Text = Replace(.Text, ChrW(8220), Chr(34), 1, -1, vbBinaryCompare) ' Chr(34) is "
' .Text = Replace(.Text, ChrW(8221), Chr(34), 1, -1, vbBinaryCompare)
' .Text = Replace(.Text, ChrW(8217), Chr(39), 1, -1, vbBinaryCompare) ' Chr(39) is '
' or
.Text = Replace(Replace(Replace(.Text, ChrW(8217), Chr(39), 1, -1, vbBinaryCompare), ChrW(8221), Chr(34), 1, -1, vbBinaryCompare), ChrW(8220), Chr(34), 1, -1, vbBinaryCompare)
End With
End Sub
I think they all appeared at the same time, and it could have been on 14th January, 2021. But I am not 100% sure.
This thread caught my attention first,
https://excelfox.com/forum/showthread.php/2302-quot-What%c3%a2%e2%82%ac%e2%84%a2s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string
but I have seen the problem elsewhere also
here is a screen shot from now
As seen in January 2021
https://i.imgur.com/x3EmtPi.jpg
For comparison with what it was like previously, we can find many archived versions, for example http://web.archive.org/web/20200321110623/https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string
_ Archived version from March 2020
https://i.imgur.com/tY5A9qG.jpg
It looks like we have a problem with the non straight quote things
‘
“
”
Its very difficult to analyse these characters because they have habit of changing depending on where you copy and paste them from or to For example, Some of us may have had the experience that when copying a code from somewhere into a code module, quotes get changed to the non straight ones resulting in syntax errors in a macro
Sub HelloGoodQuotes()
MsgBox Prompt:="Hello Good straight Quotes"
End Sub
Sub HellobadQuotes()
MsgBox Prompt:=“Hello Bad non straight Quotes”
End Sub
If I do my WotchaGotMacro, ( coincidentally shared at the example corrupt Thread) , .._
Sub excelfoxCoruptions() ' https://excelfox.com/forum/showthread.php/2704-Test-excelfox-Corruptions-January-2021
' “He” & “l” & “l” & “o” & vbCr & vbLf t’s
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff("“’”“”") ' https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string
End Sub
_.. then I get these results:
ChrW(8220) & ChrW(8217) & ChrW(8221) & ChrW(8220) & ChrW(8221)
Row\Col
E
F
1
20 Jan 2021
Lenf is 5“’”“”
21 “8220
32 ’8217
43 ”8221
54 “8220
65 ”8221
Worksheet: WotchaGotInString
They seem to be tying up with published info, such as here : https://www.vertex42.com/ExcelTips/unicode-symbols.html
_.___________________
I can repair , or rather replace, my corrupt posts from the copy of the posts that I have in Microsoft Word.
To be on the safe side, to help avoid such corruptions in the future, I should probably best change all my quotes in text to straight ones. One small snag in this is that trying to do it manually returns some peculiar results. It seems that Microsoft Word has some rules / processes which convert quotes. I don't understand them yet.
Luckily a macro does not seem to suffer from these problems: If I select the text in which I want to "sanitise" the quotes, the run this macro, then all my quotes get changed to straight ones
Sub QuoteChange() ' Ctrl+Shift+q
With Selection
' .Text = Replace(.Text, ChrW(8220), Chr(34), 1, -1, vbBinaryCompare) ' Chr(34) is "
' .Text = Replace(.Text, ChrW(8221), Chr(34), 1, -1, vbBinaryCompare)
' .Text = Replace(.Text, ChrW(8217), Chr(39), 1, -1, vbBinaryCompare) ' Chr(39) is '
' or
.Text = Replace(Replace(Replace(.Text, ChrW(8217), Chr(39), 1, -1, vbBinaryCompare), ChrW(8221), Chr(34), 1, -1, vbBinaryCompare), ChrW(8220), Chr(34), 1, -1, vbBinaryCompare)
End With
End Sub