Excel 2010，VBA和ListObjects小计在表更改时未更新 [英] Excel 2010, VBA and ListObjects subtotals not updating on Table changes

问题描述

因此，具有这种结构(从A1开始-show snippet> run):

So, having this structure (starting at A1 - show snippet > run):

table {
  border-color: #BBB;
  border-width: 0px 0px 1px 1px;
  border-style: dotted;
}
body {
  font: 12px Arial, Tahoma, Helvetica, FreeSans, sans-serif;
  color: #333;
}
td {
  border-color: #BBB;
  border-width: 1px 1px 0px 0px;
  border-style: dotted;
  padding: 3px;
}

<table>
  <tbody>
    <tr>
      <th></th>
      <th>A</th>
      <th>B</th>
      <th>C</th>
      <th>D</th>
    </tr>
    <tr>
      <td>1</td>
      <td>Title 1</td>
      <td>Title 2</td>
      <td>Title 3</td>
      <td>Title 4</td>
    </tr>
    <tr>
      <td>2</td>
      <td>GH</td>
      <td>1</td>
      <td>434</td>
      <td>4</td>
    </tr>
    <tr>
      <td>3</td>
      <td>TH</td>
      <td>3</td>
      <td>435</td>
      <td>5</td>
    </tr>
    <tr>
      <td>4</td>
      <td>TH</td>
      <td>4</td>
      <td>4</td>
      <td>6</td>
    </tr>
    <tr>
      <td>5</td>
      <td>LH</td>
      <td>2</td>
      <td>0</td>
      <td>3</td>
    </tr>
    <tr>
      <td>6</td>
      <td>EH</td>
      <td>2</td>
      <td>5</td>
      <td>36</td>
    </tr>
  </tbody>
</table>

我编写了一些代码来转换ListObject中的范围(A1:D6)，添加了4个新列和小计:

I scripted some code to transform that range (A1:D6) in a ListObject, added 4 new columns and subtotals:

Function test()

    Dim objLO As ListObject

    Set objLO = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$6"), , xlYes)
    objLO.Name = "Recap"
    objLO.TableStyle = "TableStyleMedium2"

    objLO.ListColumns.Add (objLO.ListColumns.Count + 1)
    objLO.HeaderRowRange(objLO.ListColumns.Count) = "Tot1"
    objLO.ListColumns.Add (objLO.ListColumns.Count + 1)
    objLO.HeaderRowRange(objLO.ListColumns.Count) = "Tot2"
    objLO.ListColumns.Add (objLO.ListColumns.Count + 1)
    objLO.HeaderRowRange(objLO.ListColumns.Count) = "Tot3"
    objLO.ListColumns.Add (objLO.ListColumns.Count + 1)
    objLO.HeaderRowRange(objLO.ListColumns.Count) = "Tot4"

    objLO.ShowTotals = True

    objLO.ListColumns("Tot1").TotalsCalculation = xlTotalsCalculationSum
    objLO.ListColumns("Tot2").TotalsCalculation = xlTotalsCalculationSum
    objLO.ListColumns("Tot3").TotalsCalculation = xlTotalsCalculationSum
    objLO.ListColumns("Tot4").TotalsCalculation = xlTotalsCalculationSum

End Function

现在，如果您在新列的任何单元格上写一些数字，奇怪的是TOTAL(小计)不会更新；但是，如果您保存文件并重新打开它，它将可以使用，并且总数将会更新. 我想念的是什么?

Now if you go on any cell of the new columns and write some numbers the odd thing is that the TOTAL (subtotal) doesn't updates; but if you save the file and reopen it it would work and the totals will update. What I'm missing?

我已经尝试过在TotalCalculation之后移动ShowTotals，但是行为保持不变.

I've already tried moving the ShowTotals after the TotalCalculation but the behavior remain the same.

如果我们现在从头开始重建工作表，并在上一代码中应用样式之后，将这部分代码添加到b，c和d列的小计中:

If we now rebuild the sheet from scratch and add this piece of code for subtotals for columns b, c and d after applying the style in the previous code:

objLO.ListColumns("b").TotalsCalculation = xlTotalsCalculationSum
objLO.ListColumns("c").TotalsCalculation = xlTotalsCalculationSum
objLO.ListColumns("d").TotalsCalculation = xlTotalsCalculationSum  

我注意到b，c和d的小计正在工作，但Tot1，Tot2等却没有.​​

I noticed that the subtotals for b, c and d are working but not for Tot1, Tot2 etc.

似乎唯一的解决方法是在添加带有创建表的引用的ListObject之前构造原始表. 有谁知道更好的解决方案?

It seems that the only workaround is to construct the raw table before adding a ListObject with the references for creating it. Anyone know a better solution?

先谢谢您了:)

推荐答案

Excel表中存在一个突出的错误，为了获得所需的结果，需要解决一些细微问题.

There is an outstanding bug in Excel tables, and there are some subtleties that need to be addressed in order to get the outcome you require.

使用显式计算技巧的粗略修正确实有效，但是尽管该方法将根据数据行中的当前值更新总计，但每次更改值时都需要应用它们.数据表.

A crude fix using explicit calculation tricks does work, but while this approach will update the totals based on the current values in the data rows, they need to be applied every time there are changed values in the data table.

有两种方法可以强制Excel计算总数:

There are 2 ways to force Excel to calculate the totals:

1. 您可以切换父级工作表的计算"状态:

1. You can toggle the Calculation state of the parent Worksheet:

objLO.Parent.EnableCalculation = False
objLO.Parent.EnableCalculation = True

或者，您可以在总计公式中替换=:

objLO.TotalsRowRange.Replace "=", "="

但是上述两种方法都不能为您提供持久的解决方案，该解决方案可以自动自动使总数保持最新状态.

But neither of the above approaches give you a lasting solution that keeps the totals up to date automatically.

更好的解决方案...

该解决方案的线索在于以下事实:在将ListObject从范围转换为ListObject时，对于存在 的列动态计算小计 .

The clue to the solution lies in the fact that subtotals are dynamically calculated for columns that existed when the ListObject was converted from a range to a ListObject.

您可以利用此知识，并确保将列插入到现有列之前，而不是将列附加到ListObject的末尾.但是，由于您最终希望新列位于最右边，因此该方法将需要在原始范围内使用一个虚拟列，然后将所有新列插入 before 中，最后， 虚拟"列可以删除.

You can exploit this knowledge, and ensure that instead of appending columns to the end/right of the ListObject, you insert them before an existing column. But as you ultimately want the new columns to be right-most, this approach will require the use of a dummy column in the original range, then all new columns are inserted before the Dummy column, and finally, the Dummy column can be deleted.

请参阅此修改后的代码，并附带注释:

See this modified code, with comments:

Function test()

    Dim objLO As ListObject

    'Expand the selection to grab an additional Dummy column
    Set objLO = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$E$6"), , xlYes)
    objLO.Name = "Recap"
    objLO.TableStyle = "TableStyleMedium2"

    'Insert all of the new columns BEFORE the Dummy column
    objLO.ListColumns.Add (objLO.ListColumns.Count)
    objLO.HeaderRowRange(objLO.ListColumns.Count - 1) = "Tot1"
    objLO.ListColumns.Add (objLO.ListColumns.Count)
    objLO.HeaderRowRange(objLO.ListColumns.Count - 1) = "Tot2"
    objLO.ListColumns.Add (objLO.ListColumns.Count)
    objLO.HeaderRowRange(objLO.ListColumns.Count - 1) = "Tot3"
    objLO.ListColumns.Add (objLO.ListColumns.Count)
    objLO.HeaderRowRange(objLO.ListColumns.Count - 1) = "Tot4"

    'Must show totals BEFORE applying totals, otherwise the last column defaults to Count (even if we override it)
    objLO.ShowTotals = True

    objLO.ListColumns("Tot1").TotalsCalculation = xlTotalsCalculationSum
    objLO.ListColumns("Tot2").TotalsCalculation = xlTotalsCalculationSum
    objLO.ListColumns("Tot3").TotalsCalculation = xlTotalsCalculationSum
    objLO.ListColumns("Tot4").TotalsCalculation = xlTotalsCalculationSum

    'Remove the extra dummy column
    objLO.ListColumns(objLO.ListColumns.Count).Delete

    'Now toggle the ShowTotals to force the ListObject to recognise the new column totals
    objLO.ShowTotals = False
    objLO.ShowTotals = True

End Function

这篇关于Excel 2010，VBA和ListObjects小计在表更改时未更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！