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:
Dim sCode As String
Dim doClip As DataObject
Set doClip = New DataObject
‘Get the code from the clipboard
doClip.GetFromClipboard
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
doClip.PutInClipboard
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.
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.
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?
I would include Chr(160) as well ? (replace it with Chr(32)
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)
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.
[…] Daily Dose of Excel » Blog Archive » Cleaning Pasted Code […]