如何使用 VBA 检测不适合 Excel 单元格的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10930743/
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
How to detect values that do not fit in Excel cells, using VBA?
提问by jmster
We are generating long Excel sheets using various tools, which have to be reviewed and used as input further down in the workflow. The problem that some cells are too small for texts they contain. So humans and programs that are reading the worksheets will not see the same data.
我们正在使用各种工具生成长 Excel 表格，这些表格必须在工作流程中进行并用作输入。某些单元格对于它们包含的文本来说太小的问题。因此，正在阅读工作表的人和程序将不会看到相同的数据。
This is usually true for merged cells containing auto-wrapped texts, when Excel does not adjust the row height properly. But there are also other cases: for instance, when some columns have width explicitly set, which is not enough for long values.
对于包含自动换行文本的合并单元格，当 Excel 未正确调整行高时，这通常是正确的。但也有其他情况：例如，当某些列明确设置了宽度时，这对于长值是不够的。
|Group|Def1 |Subgroup|Definition| Id |Data |Comment |
|-------------------------------------------------------|
| G1 | | G1-1 |Important |G1-1-1|... | |
| |Long | |about G1-1|G1-1-2|.....|........ |
| |text |-------------------------------------------|
| |about| G1-2 |Another |G1-2-1|... | |
| |group| |important |G1-2-2|... |long comme|
| |G1. | |text about|G1-2-3| | |
|-------------------------------------------------------|
Here, some cells in "Definition" and "Comment" are not fully visible. Is there any method to find such cells programmatically?
此处，“定义”和“注释”中的某些单元格不完全可见。有没有办法以编程方式找到这样的单元格？
回答by djjw
To detect these cells (I'm not talking about fixing the problem), you could use the Text method of a Range object.
要检测这些单元格（我不是在谈论解决问题），您可以使用 Range 对象的 Text 方法。
For example, Range("A1").Valuemight be 123456789, but if it's formatted as Number and the column is not wide enough, Range("A1").Textwill be "###" (or however many # signs fit in the cell).
例如，Range("A1").Value可能是 123456789，但如果它的格式设置为数字并且列不够宽，Range("A1").Text则将是“###”（或者单元格中可以容纳多少# 符号）。
回答by Jean-Fran?ois Corbett
Here's a trick I've used before:
这是我以前使用过的一个技巧：
With Columns("B:B")
oldWidth = .ColumnWidth ' Save original width
.EntireColumn.AutoFit
fitWidth = .ColumnWidth ' Get width required to fit entire text
.ColumnWidth = oldWidth ' Restore original width
If oldWidth < fitWidth Then
' Text is too wide for column.
' Do stuff.
End If
End With
Of course this will apply to an entire column at a time. You can still use this by copying cells over one by one to a dummy column and do the AutoFittest there.
当然，这将一次应用于整个列。您仍然可以通过将单元格一个一个地复制到一个虚拟列并在AutoFit那里进行测试来使用它。
But probably more useful to you is an earlier answer of mine to this question: Split text across multiple rows according to column width. It describes a method to determine the width of the text in any given cell (and compare it to the cell's actual width to determine whether the text fits or not).
但可能对您更有用的是我之前对这个问题的回答：根据列宽将文本拆分为多行。它描述了一种确定任何给定单元格中文本宽度的方法（并将其与单元格的实际宽度进行比较以确定文本是否适合）。
EDITResponding to your comment: If some of your cells are tall enough to show 2 or more lines of text, then you can use a similar approach as described in my previous answer, first using .EntireRow.AutoFitto determine the height of the font and .RowHeightto determine how many lines fit in the cell, then figuring out whether the text can fit in that number of lines in a cell of that width, using the method of the previous question.
编辑回应您的评论：如果您的某些单元格足够高以显示 2 行或更多行文本，那么您可以使用我之前的回答中描述的类似方法，首先使用.EntireRow.AutoFit来确定字体的高度并.RowHeight确定如何许多行适合单元格，然后使用上一个问题的方法确定文本是否可以适合该宽度的单元格中的行数。
回答by user6261023
I bumped into the same problem today. I am trying this trick to dodge it. Perhaps, it might be useful for you. It is coded to deal with one column width merging areas:
我今天遇到了同样的问题。我正在尝试这个技巧来躲避它。也许，它可能对你有用。它被编码为处理一列宽度合并区域：
'Sheet2 is just merely support tool no data sheet in ThisWorkbook
With Sheet2.Range(target.Address)
target.Copy
.PasteSpecial xlPasteAll
.UnMerge
If .MergeArea.Count > 1 Then .UnMerge
.ColumnWidth = target.ColumnWidth
.Value = target.Value
.EntireRow.AutoFit
target.MergeArea.EntireRow.RowHeight = _
1.05 * .RowHeight / target.MergeArea.Rows.Count
.ClearContents
.ClearFormats
End With
Unfortunately, if there are several columns with merged cells like this, perhaps their mutual needed height will collide with each other and extra code will be needed for restoring harmony. Looks like an interesting piece of code. Wish you find this helpful.
不幸的是，如果有几列合并了这样的单元格，也许它们相互需要的高度会相互碰撞，并且需要额外的代码来恢复和谐。看起来是一段有趣的代码。希望你觉得这有帮助。
回答by Trace
Unmerge all cells in the workbook and use Thisworkbook.sheets("Name").rows(index).entirerow.autofit And the same for columns. What's the use of keeping the merged cells anyway except for esthetic reasons? Only the value of the "base cell" is taken into account (upper left).
取消合并工作簿中的所有单元格并使用 Thisworkbook.sheets("Name").rows(index).entirerow.autofit 和列相同。除了美学原因，保留合并的单元格有什么用？仅考虑“基本单元格”的值（左上角）。
