Excel 2010、VBA 和 ListObjects 小计未随表更改而更新

2024-01-01

因此,具有以下结构(从 A1 开始 - 显示片段 > 运行):

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 个新列和小计:

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(小计)不会更新;但如果您保存文件并重新打开它,它就会起作用并且总数将会更新。 我缺少什么?

我已经尝试将 ShowTotals 移到 TotalCalculation 之后,但行为保持不变。

如果我们现在从头开始重建工作表,并在应用前面代码中的样式后添加这段代码用于 b、c 和 d 列的小计:

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

我注意到 b、c 和 d 的小计有效,但 Tot1、Tot2 等无效。

似乎唯一的解决方法是在添加 ListObject 以及创建它的引用之前构造原始表。 有人知道更好的解决方案吗?

提前致谢 :)


Excel 表格中存在一个突出的错误,需要解决一些微妙之处才能获得所需的结果。

使用显式计算技巧的粗略修复确实有效,但是虽然此方法将根据数据行中的当前值更新总计,但每次数据表中的值发生更改时都需要应用它们。

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

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

    objLO.Parent.EnableCalculation = False
    objLO.Parent.EnableCalculation = True
    
  2. 或者,您可以更换=在总计公式中:

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

但上述方法都无法为您提供持久的解决方案来使总数保持最新自动地.

更好的解决方案...

解决方案的线索在于小计are动态计算的列existed当 ListObject 从范围转换为 ListObject 时。

您可以利用这些知识,并确保不是将列附加到 ListObject 的末尾/右侧,而是将它们插入到现有列之前。但由于您最终希望新列位于最右侧,因此这种方法将需要在原始范围中使用虚拟列,然后插入所有新列before虚拟列,最后可以删除虚拟列。

请参阅此修改后的代码,并附有注释:

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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)

Excel 2010、VBA 和 ListObjects 小计未随表更改而更新 的相关文章

  • 当单元格值和复选框更改时更改工作表

    我有一本包含多个工作表的工作簿 我有一个菜单页面 工作表 其中包含多个用户选择 输入新订单 更新订单等 每个选项旁边都有一个复选框 根据选中的复选框 单元格F4 F21改变自0 to 1并且 细胞B1更改我想要去的工作表的名称 我的主菜单工
  • 使用工作表作为数据源的 VSTO Excel 的简单示例

    我想我遇到了 最简单的答案是最难找到的答案 的情况 而且我还没有遇到过任何搜索能够以直接的方式给我这个答案 这是为了Excel 2010 and VS 2010在现有 VSTO C 项目中 我有一个 Excel 工作表 其中包含 4 列数据
  • 将 Outlook 中选定电子邮件的电子邮件正文显示为 Excel 中的消息框?

    我正在使用 excel 2010 并创建了以下 vba 代码 该代码查找主题标题中包含单词 test 的电子邮件 然后在 excel 中显示包含该电子邮件正文的消息框 Sub GetFromInbox Dim olApp As Outloo
  • 索引多列并匹配不同的值,返回跨列的唯一值列表

    我已经在漫长的几周内广泛寻找解决我的问题的方法了 我提出了一个部分有效的解决方案 我将其包含在底部 供那些可能知道如何修改 扩展它们以解决问题的人使用 这就是我想要完成的任务 以下描述参考此屏幕截图https i stack imgur c
  • 空格键在 Excel VBA 编辑器中的行尾不起作用

    我将 Personal xlsb 移至新笔记本电脑 但遇到了一个奇怪的问题 空格键在行尾不起作用 除非它是完全空的行 示例 1 不起作用的示例 不工作 示例 2 工作示例 会工作 当我按空格键时 文本选择指针没有移动 但位置指示器在原始位置
  • 从html中获取属性字符串值

    我正在构建一个宏来使用从网站提取数据vba questions tagged vba 目前 我可以使用元素语法轻松地从表内容中获取值 例如obj getElementsByTagName td innerText 但是 当某些单元格中有一些
  • 如何通过VBA刷新所有单元格

    有没有办法触发 从VBA Excel要求它重新评估所有Excel单元格 谢谢 The 计算 http msdn microsoft com en us library aa223802 28office 11 29 aspx方法可以重新计算
  • 如何在 C# 中将 excel ListObject 添加到给定工作表?

    我目前正在 C 中开发一个 Excel 插件 其中包含多种方法 表值函数 可供 Excel 用户和程序员 VBA 使用 如何编写一个方法 将新的 ListObject Excel 表 添加到给定的 Excel 工作表 并将给定的 DataT
  • VBA中工作表变化的递归调用

    我已经创建了包含多个工作表的工作簿 我正在尝试使用 WorkSheet ChangeSheet1 即工作表 1 上的某些更改正在复制到工作表 2 中 同样 如果有任何改变Sheet2我想做出类似的改变Sheet1以及 这样做时 两张表上都会
  • 带有 For 循环的多维数组 VBA

    尝试检查第一列中的值 即多维数组中的列 如果它匹配 则对另一列中与该行匹配的值进行排序 我认为我做错了 但这是我第一次搞乱多维数组 我是否需要在每个 for 循环中使用 UBound 和 LBound 来告诉它要查看哪一列 除了当前问题的答
  • 元素不存在,尽管它具有 ID 属性

    在 selenium excel vba 中 我试图了解有关如何处理 CSS 选择器的更多信息 我很想知道 因为在检查带有 ID 的元素并运行代码时 我收到一条消息 指出未找到该元素 这是到目前为止的代码 Private bot As Ne
  • 有没有办法设置一个变量一次并在多个地方使用它而不给它模块级别的范围?

    我有一个循环将用户窗体控件添加到集合中 由于多个地方都需要该集合 因此我将其放入模块中并在需要时调用它 这意味着该集合仅在需要时才位于内存中 但这也意味着我每次想要使用它时都会运行一个循环 I could已给出集合模块级别范围并在第一次需要
  • 在 VBA 循环中导出查询以根据字符串值选择数据

    我有一个名为 TEST 的表 下面的代码根据 Territory 列中的唯一值循环导出查询 该代码应该根据 Territory 列中的唯一值将数据导出到 Excel 文件 因此每个 Territory 值都有它自己的文件 我在设置 sql
  • 双击事件 - 多个范围

    我正在寻找为双击事件在多个范围内进行编码的最佳方法 Private Sub Worksheet BeforeDoubleClick ByVal Target As Range Cancel As Boolean If Not Interse
  • Excel VBA - 循环文件夹中的文件、复制范围、粘贴到此工作簿中

    我有 500 个包含数据的 Excel 文件 我会将所有这些数据合并到一个文件中 实现此目标的任务列表 我想循环遍历文件夹中的所有文件 打开文件 复制此范围 B3 I102 将其粘贴到活动工作簿的第一张工作表中 重复但在下面粘贴新数据 我已
  • 用于将 MS Word 表导出到 Excel 工作表的宏

    我有一个包含许多表格的word文档 有谁知道如何编写宏将此类表导出到不同的 Excel 工作表 答案摘自 http www mrexcel com forum showthread php t 36875 http www mrexcel
  • VBA仅清除数据透视表缓存,但保留数据透视表结构

    如何使用VBA清除数据透视表缓存 但不破坏数据透视表结构 我的数据透视表已连接到外部数据源 SQL 源决定哪个用户应该查看数据的哪一部分 当表刷新时 源会填充该表 我想保存 Excel 文件并使用干净的数据透视表 内部没有数据 分发它 结果
  • 复制列中的所有单元格[关闭]

    很难说出这里问的是什么 这个问题是含糊的 模糊的 不完整的 过于宽泛的或修辞性的 无法以目前的形式得到合理的回答 如需帮助澄清此问题以便重新打开 访问帮助中心 help reopen questions 我有一张表 有 200 行 行间有一
  • FileDialog 保留以前的过滤器

    我正在 Access 数据库中制作表单 我需要打开文件对话框窗口几次 我只是不明白为什么在我更改选项值几次并打开文件对话框窗口后它没有更改过滤器 Public Sub Command17 Click Dim fd As FileDialog
  • IE.navigate2 因保护模式关闭而失败

    我正在从 Excel VBA 自动化 IE8 Excel 2010 Windows 7 Set IE CreateObject InternetExplorer Application IE Navigate2 URL 如果 URL 是处于

随机推荐