如何使用VBA检测不适合Excel单元格的值？ [英] How to detect values that do not fit in Excel cells, using VBA?

问题描述

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

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.

对于包含自动包装文本的合并单元格，通常为true不适当地调整行高。但也有其他情况：例如，当某些列已经明确设置宽度时，这对于长的值是不够的。

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.

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

推荐答案

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

To detect these cells (I'm not talking about fixing the problem), you could use the Text method of a Range object.

例如， Range（A1）。值可能是123456789，但如果格式为Number，该列不够宽， Range（A1）。文本将为###（或许多＃符号适合单元格）。

For example, Range("A1").Value might be 123456789, but if it's formatted as Number and the column is not wide enough, Range("A1").Text will be "###" (or however many # signs fit in the cell).

这篇关于如何使用VBA检测不适合Excel单元格的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！