VBA求和函数,求和的vba代码怎么写

　　本文讲解了在Excel中如何根据单元格的背景色轻松地对值求和，如何结合颜色和内置滤镜，按单元格的填充颜色对值求和。这是一种将颜色转换成有意义信息的简单方法。简单的过滤技术并不总是足够的，尤其是当您重用其他表达式的总和时。如果需要独立求和，可以使用两个VBA用户定义函数(UDF)中的一个。一个依赖于一个helper列；另一个没有。不需要大量代码或专业知识。不利的一面是，它们都有局限性：UDF既不能使用条件格式，也不能动态地使用它们。然而，如果这是你所需要的，那么你可以忍受这些限制。

　　我用的是Win1064位系统上的Excel 2016(桌面)，但是两个UDF在老版本都可以用。但是，该浏览器版本不支持VBA。您可以使用自己的数据或下载演示。xlsm，xls，cls和。bas文件。

　　启用宏的文件

　　开始之前，请确保将正在使用的工作簿保存为启用宏的文件，如下所示：

　　1.单击文件选项卡，然后在左侧窗格中选择另存为。

　　2.选择启用宏的Excel工作簿(*。xlsm)。

　　3.单击保存。

　　可下载的演示文件已经是这种格式了。如果您使用的是Excel的菜单版本，则无需执行此步骤。如果SUMIF()没有返回预期的结果，请确保标题单元格中的填充颜色与数据集中的颜色相同。

　　一个简单的UDF和帮助列

　　如果使用直接格式而不是条件格式，可以使用简单(但有限)的VBA UDF根据单元格的填充颜色对值求和。图A中的示例表在B列中显示了一些直接格式：如果值为$1，000或更高，则为红色；如果值为$200或更低，则为绿色。当然，在这种情况下，您可能会使用条件格式，所以请原谅这个人为的例子。

　　图a

　　B列中的直接格式标识最高和最低值。

　　第一步是将清单A中所示的简单UDF添加到工作簿中。为此，请按Alt F11打开Visual Basic编辑器(VBE)。从“插入”菜单中选择模块。然后，手动输入ColorSum()或导入可下载的。bas文件。不要从该网页复制和粘贴。

　　列表a

　　函数ColorSum(单元格颜色作为范围)

　　按单元格的背景色对值求和。

　　ColorSum=CellColor。内部颜色指数

　　结束功能

　　ColorSum()将当前单元格作为range对象传递，并返回ColorIndex属性设置。在D2中输入以下函数，并将其复制到其余单元格中，如图B所示：

　　=ColorSum(B2)

　　图b

　　调用UDF。

　　UDF引用B列中的单元格，并返回每个单元格的填充颜色的索引值：绿色是48，红色是3。我们不关心没有指定颜色的单元格(-4142)。

　　接下来，在E2中输入以下SUMIF()，并将其复制到其余单元格中：

　　=苏米夫($2美元：18美元；D2($ 2美元：18美元)

　　如图C所示，SUMIF()函数返回绿色和红色单元格的总数：分别为3，600和602。第一个范围是指d列中UDF返回的颜色索引值。对$ D2的引用使用当前索引值作为标准——我们正在寻找与D2中的索引值相匹配的D2: d18中的索引值。的最后一个范围引用标识了前两个值匹配时函数sum的值。

　　图C

　　SUMIF()返回绿色和红色单元格的和。

　　坦白说，有点乱。即使成功了，你也可能不满意这样的安排。图D显示了一个更合理的设置。手动为G1和H1添加填充颜色；我们将使用它们作为标题单元格和标准。然后，在G1输入以下函数，并将其复制到H1:

　　=SUMIF($D$2:$D$18，ColorSum(G$1)，$B$2:$B$18)

　　第一个范围是指UDF返回的索引值。第二个参数ColorSum()返回标题单元格的颜色索引值；在这种情况下，单元格为G1，索引值为48。当当前两个参数的值匹配时，此函数将B2中的相应值相加：B18。如果不想看到D列中的索引值，请隐藏该列。因此，E列中的值是不必要的。

　　图d

　　他的简单矩阵简化了求和。

　　一个改进的UDF

　　第一个UDF取决于帮助列D列中的颜色索引值.您可以使用清单B中所示的UDF来删除该列。图E在一个简单的矩阵中显示了这个UDF的结果，但是在第3行。按照功能转到G3并复制到H3:

　　=色彩组合2(格元1美元，乙元2美元：乙元18美元)

　　这个UDF使用语法。

　　=COLORSUM2(标准，范围)

　　其中，标准是指您匹配的颜色，范围表示您正在求和的值。简而言之，这个UDF将当前单元格的填充颜色(B2: B18)与条件单元格的填充颜色(G1)进行比较。当这些值匹配时，此函数将当前单元格中的值添加到lSum中。UDF返回检查范围(rng)中所有单元格后lSum的最终值。

　　名单b

　　函数ColorSum2(单元格颜色作为范围，rng作为范围)

　　按单元格的背景色对值求和。

　　Dim lSum As Long

　　Dim iIndex为整数

　　Dim cclr作为变体

　　iIndex=CellColor。内部颜色指数

　　调试。打印索引

　　对于rng中的每个cclr

　　如果cclr。Interior.ColorIndex=iIndex那么

　　lSum=工作表功能。总和(cclr，lSum)

　　如果…就会结束

　　下一个cclr

　　ColorSum2=lSum

　　结束功能

　　图e

　　这种改进的UDF消除了帮助列。

　　动态的，有点

　　两个UDF都可以工作，但都不是动态的。图F显示了改变B3-UDF更新颜色的结果。更改、添加或删除颜色后，必须通过按Ctrl+Alt+F9来强制重新计算文件。您可以在UDF将volatility属性设置为True，但是您仍然需要按F9。这可不好。嗯，一点都不好。

　　图f

　　UDF没有更新；这是一次性交易。

　　让UDF充满活力并不容易。清单C包含一个依赖于Worksheet_SelectionChange事件的事件过程。当您选择新的单元格时，此过程会强制文件计算填充颜色的变化。同样，手动输入代码或导入可下载的。cls文件。这段代码放在工作表的模块中，而不是前面添加的模块中。(您可以在VBE的项目浏览器中选择工作表。

　　列表c

　　私有子工作表_SelectionChange(ByVal目标作为范围)

　　将选择更改后的颜色与强制计算进行比较。

　　强制UDF、ColorSum()和ColorSum2()重新计算

　　当填充颜色改变时。

　　静态LastRange作为范围

　　字符串形式的静态LastColorIndex

　　如果LastRange。cells . interior . colorindex LastColorIndex然后

　　申请。计算完整

　　如果…就会结束

　　Set LastRange=Target

　　LastColorIndex=Target。单元格.内部.颜色索引

　　末端接头

　　虽然它很有帮助，但它不是一个优雅的解决方案。你必须记得移动选择来触发它，所以它仍然是有问题的。我提供这最后的程序是全面的；它不会真正改善这种情况，因为你必须记住无论如何都要强制重新计算。另外，每次选择不同的单位，都会触发这个过程。在繁忙的工作表中它会变慢。我不能真的推荐它的使用，但是你自己决定。不管是好是坏，我更喜欢Ctrl Alt F9键而不是工作表事件过程。我建议在自动化过程中寻找一种方法来组合这个UDF，这样您就可以添加一个重新计算的方法，而不需要记得手动进行。

　　敬请关注

　　除了它们的非易失性状态，两个UDF都有一个更大的限制——都不适用于条件格式。在以后的文章中，我将使用条件格式提供类似的解决方案。我不关心这些UDF，因为它们不是动态的，但是我不知道如何在不创建捕获格式变化的类模块的情况下使用VBA获得这些结果，这超出了短文的范围。

郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。