ansaurus

Question

vba: storing a long string in a variable with special characters

Answer 1

+2 A:

It's a little bit unclear how you intend to retrieve and store that data into a single VBA string, but if you are going to try to copy and paste the contents of the text you posted and have available in-memory as a string in VBA, here's how you might approach it.

The biggest thing to remember is to escape your double quotes and line-breaks. I replaced all " with " & chr(34) & " and added a vbCrLf to the end of the line. The first few lines of your file might look like this when consumed in VBA:

Public Sub TestString()
    Dim str As String

    str = ""

    str = str & "<svg  " & vbCrLf
    str = str & "   xmlns:dc=" & Chr(34) & "http://purl.org/dc/elements/1.1/" & Chr(34) & " " & vbCrLf
    str = str & "   xmlns:cc=" & Chr(34) & "http://creativecommons.org/ns#" & Chr(34) & " " & vbCrLf
    str = str & "   xmlns:rdf=" & Chr(34) & "http://www.w3.org/1999/02/22-rdf-syntax-ns#" & Chr(34) & " " & vbCrLf
    str = str & "   xmlns:svg=" & Chr(34) & "http://www.w3.org/2000/svg" & Chr(34) & " " & vbCrLf
    str = str & "   xmlns=" & Chr(34) & "http://www.w3.org/2000/svg" & Chr(34) & " " & vbCrLf
    str = str & "   xmlns:sodipodi=" & Chr(34) & "http://sodipodi.sourceforge.net/DTD/sodipodi-0.dtd" & Chr(34) & " " & vbCrLf
    str = str & "   xmlns:inkscape=" & Chr(34) & "http://www.inkscape.org/namespaces/inkscape" & Chr(34) & " " & vbCrLf
    str = str & "   version=" & Chr(34) & "1.0" & Chr(34) & " " & vbCrLf
    str = str & "   width=" & Chr(34) & "958.69" & Chr(34) & " " & vbCrLf
    str = str & "   height=" & Chr(34) & "592.78998" & Chr(34) & " " & vbCrLf
    str = str & "   id=" & Chr(34) & "svg2275" & Chr(34) & " " & vbCrLf
    str = str & "   sodipodi:version=" & Chr(34) & "0.32" & Chr(34) & " " & vbCrLf


    Debug.Print str

End Sub

To make it easier to generate those lines of str = str ... code, I pasted your code into Excel and used the following Excel formula which allowed me to simply paste the resulting Excel-calculated text right into the VBA editor:

="str = str & " & CHAR(34) & SUBSTITUTE(A1,CHAR(34),CHAR(34) & " & chr(34) & " & CHAR(34)) & CHAR(34) & " & vbCrLf"

I tried pasting everything you had into VBA, but some of the lines of your original text were way too long (the lines with numbers) and took up multiple rows in the VBA editor. That can be fixed fairly simply by breaking up those lines into multiple str = str & ... lines, except without the vbCrLf at the end.

Ben McCormack 2010-08-27 18:13:30

0xA3 2010-08-27 18:49:01

@0xA3 very good point. I guess I use `chr(34)` since I learned to do that before I learned about escaping the double quotes. I also get confused by looking at lots of pairs of double quotes :-).

Ben McCormack 2010-08-27 18:56:34

Answer 2

A:

Another way of doing it is to replace characters.

Just for simplification, supose you must store one string containing this:

  height="595.63739"

... then, you could substitute the double quotes by an alternate character, ending with someghing like this:

  height=#595.63739#

... and the code for the whole thing:

str = "height=#595.63739#"
str = Replace(str, "#", Chr(34))

There, your string is easier to manage, cause it doesn't need the extra concatenations.

Cons:

You may end messing with a wrong char alternative - in the exemple, supose you have, actually, some "#" in your string.
I'm not sure about performance issues.

OP2506 2010-09-24 18:15:13

ansaurus

tags:

views:

answers:

vba: storing a long string in a variable with special characters

related questions