VBA在Excel中實現自定義的Sum函數

    【IT168 論壇採風】在Excel電子表格中最常使用的函數應該是Sum了，它是內置的；當然，在Excel中內置的函數還有很多；但是，往往我們使用的函數Excel中並沒有直接提供，或者提供了我們並不知道，這個時候可以自己使用VBA定義一個自己需要使用的函數參與電子表格單元格的計算，這在日常工作中可能是經常要遇到的一個問題。下面，討論如何實現一個自定義的具有合計功能的函數，由於Sum函數系統已經內置，我們要實現的函數不妨命名為udSum。

相關精彩文章

    首先，想到的應該使用VBA，毫無疑問，最直接的針對Excel功能的擴展來源於VBA。事實上，我們可以在Excel的Microsoft Visual Basic編輯器的模塊中增加一個公共函數來實現該自定義函數，該公共自定義函數是可以在Excel單元格中直接像使用Excel內置函數一樣使用的。

    使用菜單「工具——宏——Visual Basic 編輯器」 打開Microsoft Visual Basic編輯器窗口；
在「工程」瀏覽器窗口中的樹狀目錄的任一項目上單擊右鍵，使用「插入——模塊」命令；OK，在這個模塊編輯器中，即可以開始編輯自定義函數了。     

    在此之前，我們應該考慮該函數的參數形式，和內置的Sum函數進行類比，我們可以發現Sum函數的參數應該是一個或多個單元格，而在VBA中對應的就應該是一個Range對象（關於Range對象可以參考Microsoft Visual Basic幫助中的Excel VBA對象模型），也就是說我們要自定義的函數的參數應該是一個Range對象，由此，我們可以在「模塊1」的編輯器中自定義一個如下的函數： 

 1    Public 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   
17    End 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如下所示：

 1    Public 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   
20    End Function
21

   

   可以注意到，修改後的函數的參數沒有指定類型，未明確指定的數據類型被作為Variant類型處理，此處顯示的類型顯然應該是Range，即一個Range對象的數組，然而由於VBA中ParamArray必須聲明為變體數組，故此處只能以變體類型出現，但在函數主體中，我們還是將其作為Range數組處理，遍歷該數組中的所有不連續區域中的所有單元格，並將其中的數組相加後返回即可。

    至此，我們可以這樣使用類似於=Sum(B2:C3,B5:C6,B8:C9)的公式了。也就是說，我們已經實現了一個和內置Sum函數一樣功能的自定義函數了。

    當然，這篇隨筆的目的不僅是如何實現一個合計函數，而是討論關於自定義函數的實現過程，舉一反三之後，可以解決很多平時可能遇到的問題。

更多熱點：Photoshop教程 ASP入門教程 視頻教程 圖片教程 開發速查手冊