vba 函数：返回带换行符的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17053579/
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
vba function: return string with newline
提问by posop
first of all, new to VBA.
首先，VBA 新手。
I am implementing this solution how-to-merge-all-column-into-one-cell-in-excel[stackoverflow], but have a follow-on.
我正在实施这个解决方案how-to-merge-all-column-into-one-cell-in-excel[stackoverflow]，但有一个后续。
A1:I
A2:am
A3:a
A4:boy
my output is: Iamaboybut I would like in one cell:
我的输出是：Iamaboy但我想在一个单元格中：
I
am
a
boy
Functions do not seem to return strings with vbNewLine, chr(10)or chr(13)...
函数似乎不返回带有vbNewLine,chr(10)或chr(13)...
采纳答案by Dan Donoghue
One line of code:
一行代码：
Range("B1").Formula = join(application.transpose(Range("A1:A4")),vblf)
回答by AndASM
The answer is, as tlewin said
答案是，正如特尔温所说
=ConcatenateRange(A1:A4,CHAR(10))
Or alternatively, using [alt-enter] you could write it like this
或者，使用 [alt-enter] 你可以这样写
=ConcatenateRange(A1:A4,"
")
"But wait," you say, "I tried that and it does not work!"
Well, you see, when you manually enter something like I[alt-enter]am[alt-enter]a[alt-enter]boyin Excel, or even execute a statement such as [A1] = "I" & vbNewLine & "am"Excel automatically changes the cells formatting and turns on word wrap. Word wrap is requiredfor linebreaks to be shown in a cell. However if you return a string with a linebreak in it from a UDF Excel does not update the format.
“但是等等，”你说，“我试过了，但没有用！”
好吧，您看，当您手动输入类似I[alt-enter]am[alt-enter]a[alt-enter]boyExcel 的内容，甚至执行[A1] = "I" & vbNewLine & "am"Excel等语句时，会自动更改单元格格式并打开自动换行。字包裹物需要用于在细胞中显示换行符。但是，如果您从 UDF 返回带有换行符的字符串，Excel 不会更新格式。
I can think of two possible solutions:
我可以想到两种可能的解决方案：
- Manually enable word wrap on any cell you use this function in
- (Not recommended) Store the reference in
Application.Callerduring your ConcatenateRange UDF call, then set anApplication.OnTime(now, "AddWordWrap")call and write the AddWordWrap subroutine so that it uses the stored reference to add the wordwrap formatting to the cell (this is because you cannot update the cell's format in a UDF). This method is buggy and problematic.
- 在您使用此功能的任何单元格上手动启用自动换行
- （不推荐）
Application.Caller在 ConcatenateRange UDF 调用期间存储引用，然后设置Application.OnTime(now, "AddWordWrap")调用并编写 AddWordWrap 子例程，以便它使用存储的引用将自动换行格式添加到单元格中（这是因为您无法在单元格中更新单元格的格式） UDF）。这种方法有缺陷且有问题。
回答by sagar mhatre
try using this
尝试使用这个
Sheet1.Range("A1").Value = "sgar" & Chr(10) & "saha"
回答by Thiago Lewin
Just use the CHAR(10) separator:
只需使用 CHAR(10) 分隔符：
=ConcatenateRange(A1:A4;CHAR(10))
回答by ILya Kuzmin
Cells(row, column) = "current line" & Chr(10) & "new line"
回答by Praveen
Use:
用：
=A1&"[Alt+Enter]"&A2&"[Alt+Enter]"&A3&"[Alt+Enter]"&A4
回答by dra_red
I stuck this in and it did work once I used 'wrap text'.
我坚持使用它，一旦我使用“换行文字”，它就起作用了。
