预测单元格中的文本换行 [英] Predict text wrapping in cell

问题描述

问题：
我使用VBA来填充带有文本的MS Excel 2000单元格。该列具有固定的宽度（不应由于布局而改变），并且将wordwrap属性设置为true，因此如果文本宽于列，则文本会包围多行。
不幸的是，行高并不总是得到相应的更新。我需要一种方法来预测文本是否包含多行，以便我可以手动调整高度。

我想做这样的事情：

  range（A1）value = longText 
 range（A1）EntireRow.RowHeight = 14 * GetNrOfLines （A1）
  

如何编写函数GetNrOfLines？

我不能依赖字符数，因为字体不是单声道空间。有时我写的单元格与其他单元格合并，所以Autofit不起作用。
请记住我在Excel 2000中工作。
建议？

解决方案

不幸的是，我避风港找不到一个很好的解决方案。
问题源于Excel 2000中的一个错误。我不知道它是否也适用于以后的版本。

这个问题在水平合并单元格时显示出来。
合并单元格时，行高无法自动调整。

以下示例代码显示问题

pre> Dim r As Range
设置r = Sheet1.Range（B2）
范围（r，r（1,2））。合并
r.Value =
r.Value =asdg lakj dsgl dfgjdfgj dgj dfgj dfgjdgjdfgjdfgjd
r.WrapText = True
r.EntireRow.AutoFit


在这种情况下，r.EntireRow.AutoFit不会考虑文本跨越多行，并调整高度，就好像是单行文本。

当手动自动匹配（双击工作表中的行高调整器）到已合并单元格和单词换行的行时，您将遇到同样的问题。

解决方案（由Gary McGill建议）是使用不相关的工作表。将列宽设置为与合并的单元格完全匹配。复制文本，使用相同的格式。让单元格自动调整并使用单元格值。

下面是一个简化示例：

  Public Sub test（）
 
 Dim widthInPoints As Double 
 Dim mergedCells As Range 
设置mergedCells = Sheet1.Range（B2：C2）
 widthInPoints = mergedCells.width 
 
 Dim testCell As Range 
设置testCell = Sheet2.Range（A1）
 testCell.EntireColumn.columnWidth = ConvertPointsToColumnWidth（widthInPoints，Sheet2 .Range（A1））
 testCell.WrapText = True 
 testCell.Value = mergedCells.Value 
'也应用文本格式，如果有任何'
 
 testCell.EntireRow.AutoFit 
 
 mergedCells.EntireRow.rowHeight = testCell.rowHeight 
 End Sub 
 
专用函数ConvertPointsToColumnWidth（widthInPoints As Double，standardCell As Range） As Variant 
 
 ConvertPointsToColumnWidth =（widthInPoints / standardCell.width）* standardCell.columnWidth 
 
结束功能
  

The problem: I use VBA to populate MS Excel 2000 cells with text. The column has a fixed width (should not be changed due to layout) and the wordwrap property is set to true so the text wraps over multiple lines if wider than the column. Unfortunately, the row-height do not always get updated accordingly. I need a way to predict if the text wraps over multiple lines so I can "manually" adjust the height.

I want to do something like this:

range("A1").value = longText  
range("A1").EntireRow.RowHeight = 14 * GetNrOfLines(range("A1"))  

How do I write the function GetNrOfLines?

I can't rely on number of characters since the font is not mono-space. Sometimes the cells I'm writing to are merged with other cells so Autofit doesn't work. Please remember that I'm working in Excel 2000. Suggestions?

解决方案

Unfortunately, I haven't found a good solution. The problem originates in a bug in Excel 2000. I do not know if it also applies to later versions.

The problem manifest it self when merging cells horizontally. Row height fails to auto adjust when you have merged cells.

The following example code shows the problem

Dim r As Range
Set r = Sheet1.Range("B2")
Range(r, r(1, 2)).Merge
r.Value = ""
r.Value = "asdg lakj dsgl dfgjdfgj dgj dfgj dfgjdgjdfgjdfgjd"
r.WrapText = True
r.EntireRow.AutoFit

In this case r.EntireRow.AutoFit will not take into account that the text spans over several rows, and adjust the height as if it was single line of text.
You'll have the same problem when doing manual autofit (double clicking on the row-height-adjuster in the sheet) to a row that has merged cells and word wrap.

A solution (as suggested by Gary McGill ) is to use an unrelated sheet. Set the column width to match the full with of the merged cells. Copy the text, with the same formating. Let the cell auto-adjust and use that cells values.

Here follows a simplified example:

Public Sub test()

    Dim widthInPoints As Double
    Dim mergedCells As Range
    Set mergedCells = Sheet1.Range("B2:C2")
    widthInPoints = mergedCells.width

    Dim testCell As Range
    Set testCell = Sheet2.Range("A1")
    testCell.EntireColumn.columnWidth = ConvertPointsToColumnWidth(widthInPoints, Sheet2.Range("A1"))
    testCell.WrapText = True
    testCell.Value = mergedCells.Value
    'Text formating should be applied as well, if any'

    testCell.EntireRow.AutoFit

    mergedCells.EntireRow.rowHeight = testCell.rowHeight
End Sub

Private Function ConvertPointsToColumnWidth(widthInPoints As Double, standardCell As Range) As Variant

    ConvertPointsToColumnWidth = (widthInPoints / standardCell.width) * standardCell.columnWidth

End Function

这篇关于预测单元格中的文本换行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！