Quotes

[LisaGreen]

Hello everyone,

First of all and before I forget, I hope everyone has a great Christmas and a terrific and healthy new year!

My problem this time is with quotes and presenting code in a word document.

In a new document type 'Comment.
I'm pretty sure the quote will alter to something like a tiny forward slash. That is, the top is to the right of the bottom.

If I select that statement and run...

Code: Select all

Sub subString()

Dim slStr As String
Dim lnglN As Long

slStr = Selection.Text
Debug.Print slStr
For lnglN = 1 To Len(slStr)
  Debug.Print Asc(Mid$(slStr, lnglN, 1)), Chr$(Asc(Mid$(slStr, lnglN, 1)))
Next lnglN

' *********************************************************************
End Sub

here is the result from the immediate window.'
‘Comment

145 ‘
67 C
111 o
109 m
109 m
101 e
110 n
116 t
13

It doesn't show "correctly" here because of the conversion occurring, but the chr(145) is like a tiny *backward* slash in the immediate window!

It's so strange because If I copy and paste the line 'Comment from word to my editor of choice, notepad++, the comment character comes out as the little backward slahs.
If I copy and paste into Notepad it comes out the other way around!
This means that It's not possible to copy/paste into an editor to set the quote to an ordinary one.

I thought I had all this licked years ago!
I feel I'm totally out of my depth now.

I've googled change smart quotes for a couple of hours now and none of the code I've tried from the net hasn't worked to alter smart quotes to ordinary ones.

We need to present code in word that is copy and pasteable straight into the VBE.

Can anyone help please?

TIA
Lisa

[HansV]

Since I use Word documents more often to contain code than to look 'pretty', I have turned off the option to replace "Straight quotes" with "smart quotes" in File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type.

If you don't like that, you could add the lines

slStr = Replace(slStr, Chr(145), Chr(39))
slStr = Replace(slStr, Chr(146), Chr(39))

For double quotes, use

slStr = Replace(slStr, Chr(147), Chr(34))
slStr = Replace(slStr, Chr(148), Chr(34))

[LisaGreen]

Thank you Hans!

At the mo I have...

Code: Select all

  Selection.Find.ClearFormatting
  Selection.Find.Replacement.ClearFormatting
  With Selection.Find
    .Text = Chr$(145)
    .Replacement.Text = Chr$(39)
    .Forward = True
    .Wrap = wdFindAsk
    .Format = False
  End With
  Selection.Find.Execute Replace:=wdReplaceAll
  With Selection.Find
    .Text = Chr$(146)
    .Replacement.Text = Chr$(39)
    .Forward = True
    .Wrap = wdFindAsk
    .Format = False
  End With
  Selection.Find.Execute Replace:=wdReplaceAll
  With Selection.Find
    .Text = Chr$(147)
    .Replacement.Text = Chr$(39)
    .Forward = True
    .Wrap = wdFindAsk
    .Format = False
  End With
  Selection.Find.Execute Replace:=wdReplaceAll
  With Selection.Find
    .Text = Chr$(148)
    .Replacement.Text = Chr$(39)
    .Forward = True
    .Wrap = wdFindAsk
    .Format = False
  End With
  Selection.Find.Execute Replace:=wdReplaceAll

When I select a line in the full document though it comes back with over 500 items changed but the line I've selected is exactly the same!!!!

Very!.. frustrated at the moment.

i'm going to change thqat procedures code the way you suggest and try to run it after selecting the line in the full document.

Thank you again.

Lisa

[HansV]

Replacing 'curly quotes' with 'straight' quotes in the document itself won't help if you have the option to replace "Straight quotes" with "smart quotes" turned on - AutoFormat will change them back immediately.
So you have to manipulate the strings in VBA. Or turn off that option - much easier!

[LisaGreen]

I think I see what you mean but Mike/We would like curls in other parts of the document to look nice.

So... just change the curls in the code snippets.

Do you think creating a style would help?

All of the code is in tables delibarately so, so it's easier to copy and paste without any walkthrough text. The tables have two cols and the code is in col2. Comments on the code but not in the code are in col1 merged with col2. Copying col2 does just that and drops the merged cols.

Lisa

[LisaGreen]

Oh bugger... So much for that idea!!! Should have tested it of course. Merged cols are copied too.

I have to say thank you because you prompted us to actually try it instead of making an assumption.

Should really **really** have known better.

Thanks Hans

Lisa

[LisaGreen]

Houten, we have a solution.

Well actually a workaround.
As long as any of the code or otherwise, including and disregarding code errors in red, is copied from the VBE code window, everything seems to paste into word with the correct quotes.

So... write code or whatever anywhere, put it into the VBE, copy/paste to word.

Hope this helps someone.

Lisa

[Doc.AElstein]

I have also experienced copying back and forth to / from the VB editor can straighten things out sometimes.
Also sometimes the \ “dies” in word, that is to say, no key combination will produce it. The work around is to type it in the VB Editor , ( where up until now I have always been able to get it ) , and then copy into Word.

But I don’t think you will ever solve completely the “ ‘ bleeding red “ ‘’’ quotes that you get from time to time. I think it is like the comer – point . , . , . , . , . problem that, just as you think you have it solved, it rears its ugly head again.
I post a lot in Blog Site Comment sections: Often, ( to the annoyance of their owners ) , those Comment sections turn into unofficial question/ answer forums and, amongst other things, codes are swapped between question answerers and question askers. About a third of the sites have problems of the ,( seemingly randomly ) changing quote format. In some cases the owners are very experienced computer people, but never the less have not managed to solve this problem consistently at their site. ( Or , it may be in some cases that the end user has settings that make it impossible to guarantee that things are copied as intended )
Ctrl+z
I prepare all my internet postings in Word. I have got in the habit of Hitting the key combination of Ctrl+z after typing in any form of quote. This appears to "straighten things up" – If I understand correctly, Ctrl+z “undoes” the last thing that was “done’d”… So I think possibly some things are straight initially , and then something converts them – so that conversion is what Ctrl+z undoes in this case.
I suppose this thing that is undone’d is what is talked about here as the “Auto smart thingy”

I have not tried yet changing the internal settings , as Hans suggested. I did not know about those sort of "smart" things. I may try that now and see how I get on. But I am sometimes reluctant to change the default settings. One disadvantage of messing with default settings is that it can cause you later problems when you move around on different computers and then get inconsistencies due to the different default settings…..

But looking on the bright side, The variation in these things is what makes life stay interesting and keeps us Human , as we struggle in the chaos we cause ourselves in the reluctance to all conform to a same boring standard.

One extra tool, that I think would be useful , is a good simple to use way of analysing exactly and accurately what you have in your text string at any time in the clipboard. Often in answering Forum questions I knock up a quick code, as Lisa did in the first post here.

Having a very good knowledge and understanding of the different things in a text string , including the different Tab , CarriageReturn, LineFeeds, Quote variations, .. etc.. is probably very useful. I have just been finding myself, that being able to manipulate confidently the string to put in the clipboard can be a useful aid in coding as an alternative to building up and using arrays in coding…( I am grateful to a chap called snb for getting me to think about these things )

Alan

[LisaGreen]

Hello Alan,

I dug this code out from some quite old stuff and thought maybe it'll be useful.

Code: Select all

Sub subRunDumpStringToWord()
subDumpString "Copy String Here"
' ***********************************************************************
End Sub
Sub subDumpStringToWord( _
              strpLine As String, _
              Optional vpLineNum As Variant)
' Dump a string.

Dim intlI As Integer
Dim strlLine As String
Dim intlICols As Integer
Dim intlRows As Integer
Dim objlRange As Object
Dim intlCol As Integer
Dim intlTableNum As Integer
Dim intlRemainder As Integer
Dim intlColsDone As Integer
Dim strlLineNum As String
Dim slChr As String * 1

If IsMissing(vpLineNum) Then
  strlLineNum = "No Line number given"
Else
  strlLineNum = "Line " & STR$(vpLineNum)
End If

intlTableNum = 0
strlLine = strpLine
Documents.Add

Set objlRange = ActiveDocument.Content
With objlRange
  
  Selection.InsertAfter "Line >" & strlLine & "<" & vbCrLf & vbCrLf
  Selection.InsertAfter "Length =" & Len(strlLine) & vbCrLf & vbCrLf
  Selection.InsertAfter strlLineNum & vbCrLf & vbCrLf
  intlI = 1
  intlRows = 1
  intlCol = 12
  Do
    If intlCol > 11 Then
      Selection.EndKey Unit:=wdStory
      Selection.InsertAfter intlI & " To " & intlI + 9 & vbCrLf
      Selection.EndKey Unit:=wdStory
      ActiveDocument.Tables.Add _
          Range:=Selection.Range, _
          NumRows:=3, _
          NumColumns:=11, _
          DefaultTableBehavior:=wdWord9TableBehavior, _
          AutoFitBehavior:=wdAutoFitFixed
          
      Selection.EndKey Unit:=wdStory
      Selection.TypeParagraph
      Selection.TypeParagraph
      intlCol = 2
      intlTableNum = intlTableNum + 1
      ActiveDocument.Tables(intlTableNum) _
          .Cell(1, 1).Range.Text _
          = "Chr"
      ActiveDocument.Tables(intlTableNum) _
          .Cell(2, 1).Range.Text _
          = "Asc"
      ActiveDocument.Tables(intlTableNum) _
          .Cell(3, 1).Range.Text _
          = "Chr #"
      
    End If
    
    slChr = Mid$(strlLine, intlI, 1)
    ActiveDocument.Tables(intlTableNum) _
            .Cell(1, intlCol).Range.Text _
            = slChr
    ActiveDocument.Tables(intlTableNum) _
            .Cell(2, intlCol).Range.Text _
            = Asc(slChr)
    ActiveDocument.Tables(intlTableNum) _
            .Cell(3, intlCol).Range.Text _
            = intlI
    
    Selection.Collapse wdCollapseEnd
    intlCol = intlCol + 1
    intlI = intlI + 1
    
  Loop Until intlI > Len(strlLine)

End With

Set objlRange = Nothing
' ***********************************************************************
End Sub


Sub subDumpStringToExcelSheet( _
    strpLine As String, _
    Optional vpLineNum As Variant _
    )
' Dump a string.

Dim intlI As Integer
Dim strlLine As String
Dim intlICols As Integer
Dim intlRows As Integer
Dim objlRange As Object
Dim intlCol As Integer
Dim intlTableNum As Integer
Dim intlRemainder As Integer
Dim intlColsDone As Integer
Dim strlLineNum As String
Dim slChr As String * 1

If IsMissing(vpLineNum) Then
  strlLineNum = "No Line number given"
Else
  strlLineNum = "Line " & CStr(vpLineNum)
End If

intlTableNum = 0
strlLine = strpLine
'Documents.Add

ActiveCell.Value = "Line >" & strlLine & "<"  '& vbCrLf & vbCrLf
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Length =" & Len(strlLine)  '& vbCrLf & vbCrLf
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = strlLineNum  '& vbCrLf & vbCrLf
ActiveCell.Offset(2, 1).Select

intlI = 1
intlRows = 1
intlCol = 12
Do
  If intlCol > 11 Then
    ActiveCell.Offset(4, -1).Select

    ActiveCell.Value = intlI & " To " & intlI + 9  '& vbCrLf
    ActiveCell.Offset(1, 0).Select

    intlCol = 2
    intlTableNum = intlTableNum + 1

    ActiveCell.Value = "Chr"
    ActiveCell.Offset(1, 0).Select

    ActiveCell.Value = "Asc"
    ActiveCell.Offset(1, 0).Select

    ActiveCell.Value = "Chr #"
    ActiveCell.Offset(-2, 1).Select

  End If

  slChr = Mid$(strlLine, intlI, 1)

  ActiveCell.Offset(0, intlCol) = slChr

  ActiveCell.Offset(1, intlCol) = Asc(slChr)

  ActiveCell.Offset(2, intlCol) = intlI

  intlCol = intlCol + 1
  intlI = intlI + 1

Loop Until intlI > Len(strlLine)

subMsgBox "@Done."
' ***********************************************************************
End Sub

[Doc.AElstein]

Hi Lisa
I just gave the Excel routine a quick run. It does something similar to what I was wanting as a “string analysis routine”.
That will be helpful when I write my routine.
Thanks very much

Alan

P.s. 1. there is a small typo at the bottom of the code ( subMsgBox "@Done." )
P.s. 2. I have your PDF file on my list of things to read. I’ll drop off some comments in that other Thread ( http://www.eileenslounge.com/viewtopic.php?f=30&t=31429" onclick="window.open(this.href);return false; ) when I get around to it.

[LisaGreen]

Hello Alan,

I wrote subMsgBox because I was fed up of MsgBox appearing in the application rather than the VBE when running code for the VBE. Some other niggles were that MsgBox always presents in the centre of the window and most of all, you can't copy text from it!!

Soooo, here are two userforms. One for msgbox and one for inputbox. The code is supposed to look through the VBA project for the form and if it doesn't find it use the ordinary msgbox instead.

There's a brief description in the PDF.

I'm sending this as a PM because I think it's a little too much off topic.

Well... I would if I could find where to add a PM attachement anyway!

Apologies to Hans and I hope he doesn't mind me posting it here!

Regards
Lisa

[Doc.AElstein]

Hi Lisa
Not being able to copy from the Message box has often annoyed me as well . Just at the moment I am working on a code of mine that won’t work in F8 step mode. So I have things like this littered around the code
MsgBox strOut: Debug.Print strOut
That serves as a crude way to step through the code and to be able to get a copy of what is in simple variables from the immediate window if I want it.
So it will be interesting to look at your userforms later. I’ll wait until I have time to have a good look at it. Thanks very much for sharing it.

I have also been considering automating getting the immediate window up. There appears to be some bugs with that. I was recently involved with a few Blog site discussions on it. I keep forgetting where.. maybe here a few
http://dailydoseofexcel.com/archives/20 ... nt-1045809" onclick="window.open(this.href);return false;
https://www.excelcampus.com/vba/vba-imm ... dow-excel/" onclick="window.open(this.href);return false; ----- AlanElstonNov26 2018 .JPG ----- https://imgur.com/2eohP7i" onclick="window.open(this.href);return false; -


I have not done much with userforms, - I am just not that far yet as I am only a part time Excel hobbiest.

The Application.Input box really aggravated me because the positioning is broke since xl2007.
I wrote my own pseudo Application.Inputbox , using “API call stuff”. It works well in my own files. I use it a lot.. But that wore me out a bit as I am only a novice and that was all a bit out of my league.
http://www.excelfox.com/forum/showthrea ... #post10465" onclick="window.open(this.href);return false;
https://stackoverflow.com/questions/491 ... ion-z-opti" onclick="window.open(this.href);return false;

Thanks again for the info, I will look at all in detail when I have the time to do it justice

Alan