定义一个大的 vba 字符串的最佳方法 - 即 heredoc 等价物？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3671308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to define a large vba string - i.e. heredoc equivalent?
提问by Oesor
How should I define large strings in VBA? Is there a better way than coding something like the below?
我应该如何在 VBA 中定义大字符串？有没有比如下编码更好的方法？
Dim largeString as String
largeString = "This is a long block of text that I want to fill " & _
"into a form field. I need to make sure I pay attention " & _
"to spacing and carriage return issues while doing so. " & _
"I also have to use quotes liberally, the concatenation " & _
"operator, and the continuance underscore to make sure " & _
"VBA can parse my code." & vbCr & vbCr & _
"It's kind of a pain in the ass and I wish I could use " & _
"a heredoc instead, letting me copy and paste the block" & _
"of text I need from another source and shove it into " & _
"a string."
Edit:Ugh, and there's a 25 line continuation limit too? So much for nice indenting and 80 characters of width, that only gives me enough room for a couple decent paragraphs.
编辑：呃，还有 25 行的延续限制？这么多漂亮的缩进和 80 个字符的宽度，这只能给我足够的空间来容纳几个像样的段落。
采纳答案by Dirk Vollmar
No, this is as good as it gets.
不，这已经足够了。
For really long strings it might be an option to keep the string in a separate file, or use some application feature. For example, in Word, you might want to store the string in a document variable, as hidden text or AutoText. In Excel, you might consider a hidden sheetfor storing long string constants.
对于非常长的字符串，可以选择将字符串保存在单独的文件中，或者使用某些应用程序功能。例如，在Word 中，您可能希望将字符串作为隐藏文本或AutoText存储在文档变量中。在Excel 中，您可能会考虑使用隐藏表来存储长字符串常量。
回答by BrianZ
I prefer doing it in this way:
我更喜欢这样做：
Dim lStr As String
lStr = ""
lStr = lStr & "This is a long block of text that I want to fill "
lStr = lStr & "into a form field. I need to make sure I pay attention "
lStr = lStr & "to spacing and carriage return issues while doing so. "
lStr = lStr & "I also have to use quotes liberally, the concatenation "
lStr = lStr & "operator, and the continuance underscore to make sure "
lStr = lStr & "VBA can parse my code." & vbCr & vbCr
lStr = lStr & "It's kind of a pain in the ass and I wish I could use "
lStr = lStr & "a heredoc instead, letting me copy and paste the block"
lStr = lStr & "of text I need from another source and shove it into "
lStr = lStr & "a string."
I think this method is easier to work with than the line continuation method and there is no line number limit to get in with this way. You can comment out individual lines, which is useful for debugging SQL strings.
我认为这种方法比行延续方法更容易使用，并且没有行数限制。您可以注释掉单独的行，这对于调试 SQL 字符串很有用。
When handling long strings, I find it easier to use short variable names because VBA does not have the equivalent of +=operator. largeString = largeString & ""takes up too much space and gets repetitive, so shortening the string name makes the format somewhat bearable.
在处理长字符串时，我发现使用短变量名更容易，因为 VBA 没有等效的+=运算符。 largeString = largeString & ""占用太多空间并变得重复，因此缩短字符串名称使格式有点可以忍受。
For very large blocks of text, write it in a text editor then copy and paste it into your procedure. Then copy
对于非常大的文本块，请在文本编辑器中编写，然后将其复制并粘贴到您的程序中。然后复制
lStr = lStr & "
and paste it at the beginning of each line. The VBA editor will automatically add the quotes at the end of the line making the process simple to do.
并将其粘贴在每行的开头。VBA 编辑器将自动在行尾添加引号，使该过程变得简单。
回答by C. Rocha
Another way is to store the text in comments, then parse it in a function. No external files required, good readability.
另一种方法是将文本存储在注释中，然后在函数中解析它。无需外部文件，可读性好。
' TEXT to retrieve:
' SELECT
' field1, field2
' FROM table1
Function SQL_in_comments()
SQL_in_comments = Replace(Replace(Application.VBE.ActiveCodePane.CodeModule.Lines(2, 3), "' ", ""), "'", "")
End Function
