如何使用 VBA 检测不适合 Excel 单元格的值

Question

我们使用各种工具生成长 Excel 工作表，这些工作表必须经过审查并用作工作流程中进一步的输入。某些单元格对于它们包含的文本来说太小的问题。因此，正在读取工作表的人和程序将不会看到相同的数据。

当 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|     |          |
|-------------------------------------------------------|

在这里，"定义"和"注释"中的某些单元格并不完全可见。有没有办法以编程方式找到这样的细胞？

Answer 1

要检测这些单元格（我不是在谈论解决问题），您可以使用 Range 对象的 Text 方法。

例如，Range("A1").Value可能是123456789，但如果它的格式为 Number 并且列不够宽，则Range("A1").Text将为"###"（或者单元格中可容纳多少个 # 符号）。

Answer 2

这是我

以前用过的技巧：

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

当然，这将一次适用于整个列。您仍然可以通过将单元格逐个复制到虚拟列并在那里进行AutoFit测试来使用它。

但可能对您更有用的是我对这个问题的早期回答：根据列宽将文本拆分为多行。它描述了一种确定任何给定单元格中文本宽度的方法（并将其与单元格的实际宽度进行比较以确定文本是否合适）。

编辑 回复您的评论：如果您的某些单元格足够高以显示 2 行或更多行文本，那么您可以使用我之前答案中所述的类似方法，首先使用 .EntireRow.AutoFit 来确定字体的高度，.RowHeight确定单元格中可容纳多少行， 然后使用上一个问题的方法确定文本是否可以容纳该宽度的单元格中的该行数。

Answer 3

取消合并工作簿中的所有单元格并使用Thisworkbook.sheets（"Name"）.rows（index）.entirerow.autofit列也是如此。除了美观的原因之外，无论如何保留合并的单元格有什么用？仅考虑"基本单元格"的值（左上角）。

Answer 4

我今天遇到了同样的问题。我正在尝试这个技巧来躲避它。也许，它可能对你有用。它被编码为处理一个列宽的合并区域：

'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

不幸的是，如果有几列像这样合并的单元格，也许它们相互需要的高度会相互碰撞，并且需要额外的代码来恢复和谐。看起来像一段有趣的代码。希望对您有所帮助。