转载:VBA入门

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

    首先,想到的应该使用VBA,毫无疑问,最直接的针对Excel功能的扩展来源于VBA。事实上,我们可以在ExcelMicrosoft 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,使其可以接受不定数量的多个参数,这个需要使用到VBAParamArray特性。修改后的自定义函数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函数一样功能的自定义函数了。

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

posted @ 2008-04-25 10:31  寒天飞雪  阅读(719)  评论(0编辑  收藏  举报