Cleaning Pasted Code

I’m copying code from a Word document and pasting into the VBE. Some of the code has funky quote and double-quote characters in it (like you get from this blog sometimes) and it’s a real pain. When I paste it into the VBE, all the lines are red and I have to go through and fix all of the quotes. After 50 times of doing that I wrote a procedure to do it. Then I wondered why I waited so long. Here it is:

Sub FixCode()
    Dim sCode As String
    Dim doClip As DataObject
    Set doClip = New DataObject
    ‘Get the code from the clipboard
    sCode = doClip.GetText
    ‘replace the bad stuff with good stuff
   ’34 is a double quote and 39 a single
   ‘145, 147, 148 I found using the ASC() function in
   ‘  the immediate window
   sCode = Replace$(sCode, Chr$(145), Chr$(39))
    sCode = Replace$(sCode, Chr$(147), Chr$(34))
    sCode = Replace$(sCode, Chr$(148), Chr$(34))
    ‘Put the good code back in the clipboard
   doClip.SetText sCode
    Set doClip = Nothing
End Sub

To use the DataObject object, I had to set a reference to the Microsoft Forms 2.0 object library. I hate looking for stuff in the references box, so I just added and removed a userform. That sets that reference for me.

I like this method because I don’t have to paste the text into a textbox, then copy it from another textbox and paste it into a code module. I copy the code from Word, switch to the VBE, F5, run FixCode, paste to code module. That makes it nice an easy, but I haven’t tested what would happen if there was something other than text in the clipboard. Errors would happen, I guess.

Posted in Uncategorized

6 thoughts on “Cleaning Pasted Code

  1. Don’t forget 145:

    sCode = Replace$(sCode, Chr$(145), Chr$(39))

    145 and 146 are the curly open and close single quotes. 147 and 148 are the corresponding double quotes.

  2. That’s strange, I don’t have a problem with pasting from Word. Even if I turn on the smart quotes option in Word, (using Tools – AutoCorrect – AutoFormat As You Type), those funky quotes are converted to normal quotes when I paste into a VBA module.

    I wonder what causes the difference?

  3. I assume all that is related to writing books ;)

    my personnal way to store procedures etc.. is to use scite, it works just fine and doesnt add any fancy things (but still recognize syntax etc…), and save as txt file

    (I say that because I assume that your code is coming from a vb editor first then goes into word and back into a vb editor, I would not imagine writing code straight into word)

  4. Good guess Alderaic. I don’t know how the code gets into Word. Not all of the code has screwed up qutoes, but it’s pretty easy to tell which does and which doesn’t. I assumed it was a curly quote autocorrect thing, but John’s experiments seem to dispell that theory.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.