Excel VBA : how to check for calculated column?

Question

如果 Excel 中的表 (ListObject) 的特定列是计算列(如 http://office.microsoft.com/en-us/excel-help/use-calculated-columns-in-an-excel-table-HA010342380.aspx )，是否可以在 VBA 中进行检查？

请注意，计算列不仅每行具有相同的 R1C1 公式集，而且还会在添加新行时自动扩展(如果删除整个数据主体范围，然后重新创建一些新行，它甚至会重新填充) )。因此，检查具有一致公式的列与检查计算公式不同。

甚至可以计算一列，但用某些其他公式或值覆盖其中一行，并保留自动扩展功能。

所以我非常确信这一定是列的某些属性，我只是不确定在哪里可以通过 VBA 访问它。如果它没有通过 VBA 对象模型公开，是否有一些解决方法来获取此信息？

提前致谢，卡洛斯

编辑:我对 Excel Office Open XML 文件进行了一些挖掘，结果发现我正在寻找的是 <calculatedColumnFormula> <tableColumn> 上的元素xl\tables\table*.xml 文件的定义。有什么办法可以通过VBA实现这一点吗？

编辑2:这是一个example file以及我能想到的测试用例。 VBA 应指示第 1、2 和 3 列是计算列，而第 4 和 5 列不是。

Answer 1

以前没有看过这个，但它似乎是 ListObject 列范围的属性，如下所示:

Dim wks As Worksheet
Set wks = ActiveSheet
Dim li As ListObject
Set li = wks.ListObjects(1)
Dim col As ListColumn
Set col = li.ListColumns(2)    ' assuming column 2 of the table has a calculated formula
Dim r As Range
Set r = col.DataBodyRange
Let b = Not IsNull(r.FormulaArray)
if b then
    Let b = Len(r.FormulaArray) > 0 ' case where r.FormulaArray = "", suspect it's not a calculated column
End If
MsgBox b

如果 IsNull(r.FormulaArray) 则它没有计算列，否则它有。

第

<小时/>

好的，稍微玩了一下，我看到使用上面获得的范围对象与任何给定单元格的范围对象不同，所以如果你有一个给定的单元格，我认为你将需要获取通过.DataBodyRange 对应ListColumn 的范围。

(例如，如果您在上面插入 Set r = r.Cells(1,1) ，则 IsNull(r.FormulaArray) 测试 no更长的工作用于测试计算列，但只是说明范围是否有公式，但可以计算或不计算。)

此外，虽然在计算列时 r.FormulaArray 看起来是一个字符串，但如果它不是(计算列)，则 .FormulaArray 会产生 null，这不是一个有效的字符串值(这使得计算变得困难)要在其中捕获值，您必须使用变体而不是 bool 值)；我发现 IsNull(r.FormulaArray) 似乎工作正常。

<小时/>

如果我在已计算列的右侧添加一列，则新添加的列的 r.FormulaArray = ""。如果您将一个值放入其中一个单元格中，公式数组会立即恢复为更预期的 NULL。因此，我添加了一个测试来检测我认为的误报。