使用VBA在Excel中实现自定义的Sum函数

时间：2024-03-17 14:56:23

    在Excel电子表格中最常使用的函数应该是Sum了，它是内置的；当然，在Excel中内置的函数还有很多；但是，往往我们使用的函数Excel中并没有直接提供，或者提供了我们并不知道，这个时候可以自己使用VBA定义一个自己需要使用的函数参与电子表格单元格的计算，这在日常工作中可能是经常要遇到的一个问题。下面，讨论如何实现一个自定义的具有合计功能的函数，由于Sum函数系统已经内置，我们要实现的函数不妨命名为udSum。

    首先，想到的应该使用VBA，毫无疑问，最直接的针对Excel功能的扩展来源于VBA。事实上，我们可以在Excel的Microsoft Visual Basic编辑器的模块中增加一个公共函数来实现该自定义函数，该公共自定义函数是可以在Excel单元格中直接像使用Excel内置函数一样使用的。

    如何打开Visual Basic编辑器？

    1. 启动Microsoft Excel；
    2. 使用菜单“工具——宏——Visual Basic 编辑器” 打开Microsoft Visual Basic编辑器窗口；
    3. 在“工程”浏览器窗口中的树状目录的任一项目上单击右键，使用“插入——模块”命令；

    OK，在这个模块编辑器中，即可以开始编辑自定义函数了。     

    在此之前，我们应该考虑该函数的参数形式，和内置的Sum函数进行类比，我们可以发现Sum函数的参数应该是一个或多个单元格，而在VBA中对应的就应该是一个Range对象（关于Range对象可以参考Microsoft Visual Basic帮助中的Excel VBA对象模型），也就是说我们要自定义的函数的参数应该是一个Range对象，由此，我们可以在“模块1”的编辑器中自定义一个如下的函数：   

 1Public Function udSum(r As range) As Double
 2
 3    Dim i As Integer
 4    Dim j As Integer
 5    Dim rtn As Double
 6        
 7    rtn = 0
 8            
 9    For i = 1 To r.Rows.Count
10        For j = 1 To r.Columns.Count
11            rtn = rtn + r.Cells(i, j)
12        Next j
13    Next i
14    
15    udSum = rtn
16    
17End Function

    函数很简单，遍历Range对象的所有单元格，使其中的数字相加后返回即可。

    保存更改后，返回到工作簿Book1界面，在连续的单元格（如B2:C3）中录入几个数字，然后在另外一个单元格（如E4）中录入“=udSum(B2:C3)”，也就是说，我们使用我们自定义的合计函数udSum来计算区域B2:C3中的数字之和。经过验证，我们可以发现udSum可以实现内置函数Sum的合计功能。另外，由于udSum函数接受的参数是一个Range对象，所以该函数也可以这样调用=udSum(testRegion)，当然在当前工作表中应该实现定义一个名称“testRegion”用于表示一个特定的单元格区域。

    在上面的描述中，我们可能也注意到了，udSum函数的参数是一个连续的单元格区域，而内置的Sum函数是可以以多个参数的形式接受不连续单元格区域中的数据，如=Sum(B2:C3,B5:C6,B8:C9)。也就是说，我们应该修改函数udSum，使其可以接受不定数量的多个参数，这个需要使用到VBA的ParamArray特性。修改后的自定义函数udSum如下所示：   

 1Public Function udSum (ParamArray x()) As Double
 2    
 3    Dim i As Integer
 4    Dim j As Integer
 5    Dim k As Integer
 6    Dim rtn As Double
 7
 8    rtn = 0
 9        
10    For i = 0 To UBound(x)
11        For j = 1 To x(i).Rows.Count
12            For k = 1 To x(i).Columns.Count
13                rtn = rtn + x(i).Cells(j, k)
14            Next k
15        Next j
16    Next i
17        
18    udSum = rtn
19    
20End Function
21

    可以注意到，修改后的函数的参数没有指定类型，未明确指定的数据类型被作为Variant类型处理，此处显示的类型显然应该是Range，即一个Range对象的数组，然而由于VBA中ParamArray必须声明为变体数组，故此处只能以变体类型出现，但在函数主体中，我们还是将其作为Range数组处理，遍历该数组中的所有不连续区域中的所有单元格，并将其中的数组相加后返回即可。

    至此，我们可以这样使用类似于=Sum(B2:C3,B5:C6,B8:C9)的公式了。也就是说，我们已经实现了一个和内置Sum函数一样功能的自定义函数了。

    当然，这篇随笔的目的不仅是如何实现一个合计函数，而是讨论关于自定义函数的实现过程，举一反三之后，可以解决很多平时可能遇到的问题。

    

相关文章