VBA Excel - 自动换行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5868437/
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 Excel - Word Wrap
提问by Kuroh
I'm creating a small piece of VBA code with a specific formula, however it has a couple of ifstatements, one of which originates a double-line string (with vbNewLine)
The issue is that I can't see the text.
我正在创建一小段带有特定公式的 VBA 代码，但是它有几个if语句，其中一个源自双行字符串（带vbNewLine） 问题是我看不到文本。
So I wanted to word wrap it, but each time I set the ActiveCell.WrapText = True, nothing happens.
所以我想对它进行自动换行，但是每次我设置 时ActiveCell.WrapText = True，什么也没有发生。
I checked with a message box. I set the WrapTextto True, I return the property value with the MessageBox to confirm, and it's still False.
我检查了一个消息框。我把the设置WrapText为True，我用MessageBox返回属性值进行确认，结果还是False。
I've been told to use ActiveCell.Rows.AutoFitas well, but AutoFitdoes nothing if the text isn't wrapped.
我也被告知要使用ActiveCell.Rows.AutoFit，但AutoFit如果文本没有换行，则什么也不做。
Any idea what I might be doing wrong here?
知道我在这里可能做错了什么吗？
回答by GrimR7529
try:
尝试：
Sub WrapandFit()
ActiveCell.WrapText = True
ActiveCell.EntireRow.AutoFit
End Sub
It worked for me. Make sure that your screenupdating is also set to true.
它对我有用。确保您的 screenupdating 也设置为 true。
回答by user7775323
For me, the code below worked. (only set to change header row, (change range))
对我来说，下面的代码有效。（仅设置为更改标题行，（更改范围））
ActiveSheet.Range("A1:R1").Select
With Selection
.WrapText = True
End With
回答by Dick Kusleika
UDFs (procedures that use the keyword Function) only return values. They cannot change other parts of the Excel object model, like cell formatting. Only Subroutines (procedures that use the keyword Sub) can do that.
UDF（使用关键字 Function 的过程）仅返回值。它们不能更改 Excel 对象模型的其他部分，例如单元格格式。只有子程序（使用关键字 Sub 的过程）可以做到这一点。
You need to have your cells formatted properly before you enter your UDF. Or you could use a worksheet change event sub to format them after the fact.
在输入 UDF 之前，您需要正确格式化单元格。或者您可以在事后使用工作表更改事件子来格式化它们。
回答by rchacko
Turn off/On word wrap for whole sheet row can be done by VB code shown below: If the first row is set true, excel inherits that property for whole sheet, unless you specifically turned it off using another code.
可以通过如下所示的 VB 代码关闭/打开整个工作表行的自动换行：如果第一行设置为 true，则 excel 将继承整个工作表的该属性，除非您专门使用其他代码将其关闭。
MyWorkSheet.Rows.WrapText = True
To turn off wrapping property of a specific row:
要关闭特定行的环绕属性：
MyWorkSheet.Rows(8).WrapText = False
回答by Hu Qiang
I suspect that you are trying to wrap text in merged cells. If yes, you cannot simply call:
我怀疑您正试图在合并的单元格中换行。如果是，您不能简单地调用：
MyWorkSheet.Rows.WrapText = True
Instead, you have to simulate the wrapping operations. I found the code from http://blog.contextures.com/archives/2012/06/07/autofit-merged-cell-row-height/helped me last year.
相反，您必须模拟包装操作。去年我发现来自http://blog.contextures.com/archives/2012/06/07/autofit-merged-cell-row-height/的代码帮助了我。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MergeWidth As Single
Dim cM As Range
Dim AutoFitRng As Range
Dim CWidth As Double
Dim NewRowHt As Double
Dim str01 As String
str01 = "OrderNote"
If Not Intersect(Target, Range(str01)) Is Nothing Then
Application.ScreenUpdating = False
On Error Resume Next
Set AutoFitRng = Range(Range(str01).MergeArea.Address)
With AutoFitRng
.MergeCells = False
CWidth = .Cells(1).ColumnWidth
MergeWidth = 0
For Each cM In AutoFitRng
cM.WrapText = True
MergeWidth = cM.ColumnWidth + MergeWidth
Next
'small adjustment to temporary width
MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66
.Cells(1).ColumnWidth = MergeWidth
.EntireRow.AutoFit
NewRowHt = .RowHeight
.Cells(1).ColumnWidth = CWidth
.MergeCells = True
.RowHeight = NewRowHt
End With
Application.ScreenUpdating = True
End If
End Sub
回答by ashleedawg
This may not be exactly what the OP had in mind but I figured I'd share my VBA Word Wrapfunction since I couldn't find anything on the web to do what I wanted.
这可能并不完全是 OP 的想法，但我想我会分享我的 VBA Word Wrap功能，因为我在网上找不到任何东西来做我想做的事。
This function insert CR+LF's into the string in order to wrap it, so the word wrap is maintained if the text is copied to another application, text-based or otherwise.
此函数将+插入字符串以对其进行换行，因此如果将文本复制到另一个基于文本的应用程序或其他应用程序，则自动换行将保持不变。CRLF
Function wrapText(strIn As String, Optional maxLen As Long = 110) As String
Dim p As Long: wrapText = strIn
Do
p = InStrRev(wrapText, " ", p + maxLen) - 1
wrapText = Left(wrapText,p) & vbCrLf & Right(wrapText, Len(wrapText)-p-1)
Debug.Print Mid(Replace(wrapText, vbCrLf, "||"), p - 20)
'Stop
Loop While p + maxLen < Len(wrapText)
End Function
It defaults to maximum width of 115 characters but can optionally be changed to anything. It only breaks on spaces (the last one that appears on/before position #115), and it only inserts CR+ LF's (with the constant vbCrLf), but they can be adapted as required.
它默认为 115 个字符的最大宽度，但可以选择更改为任何内容。它只在空格处中断（最后一个出现在位置 #115 上/之前），并且它只插入CR+ LF（带有常量vbCrLf），但它们可以根据需要进行调整。
As an example of application, I was building complex SQL queries in Excel and wanted to copy the SQL over to the server app neat & tidy, instead of one giant line.
作为应用程序的一个示例，我正在 Excel 中构建复杂的 SQL 查询，并希望将 SQL 整齐地复制到服务器应用程序，而不是一条巨线。
