跳到主要內容

Excel VBA (一) : 自訂函數之自動加總

最近幫忙別人解決 Excel 的資料處理問題, 順便將一些使用心得做一下筆記

當我開啟Excel時, 預設是沒有開發人員的功能(如下圖的紅框)

要開啟就要點擊Excel選單的檔案(如上圖的黃框)
再點擊選項(如下圖的紅框)

再點擊自訂功能區(如下圖的紅框)

再將開發人員的功能打開即可(如下圖的紅框)

開啟開發人員中的Visual Basic(如下圖的紅框)就會開啟VBA的功能視窗了

我先嘗試最簡單的程式, 看看是否成功(如下圖)

Function 我的加總() As Double
    我的加總 = 100
End Function
再去Excel的工作表1中, 在任一儲存格中輸入"=我的加總()", 測試是否會顯示 "100"(如下圖), 結果成功了, 代表Excel VBA中函數與變數名稱都是可以用中文

再來就是真正開始寫程式了, 如果真正要寫的完整會要寫很多例外處理, 這邊只做簡單的版本, 請大家見諒喔!以下是我完成的程式碼

Function 我的加總(加總區域 As Range) As Double
    Dim data As Variant
    我的加總 = 0
    For Each data In 加總區域
        我的加總 = 我的加總 + data
    Next
End Function
在Excel中輸入如下圖的資料來測試

繼續在B8儲存格中點選 "插入函數fx" (如下圖)

出現Excel的函數視窗後, 函數類別選擇 "使用者定義" 會出現 "我的加總" 函數, 按下確定後選擇A1..A5的加總區域, 結果顯示15, 代表程式初步測試OK!

再來我將 B5 故意留空白, 也讓它加總看看, 手動將B8的 "A1:A5" 改成 "A1:B5" 結果顯示29(成功)!

再來是C1我故意輸入文字 "A", 讓它加總看看, 手動將B8的 "A1:B5" 改成 "A1:C5", 結果顯示 "#VALUE!", 如果要改成非數字的都自動濾掉不加, 就要再修改程式囉!

最後要感謝這篇文章教了如何在 Google Blogger 中將程式碼區塊改變顏色


測試時間: 2020年07月28日
測試環境: Windows 10 家用版, Excel 2019 家用版
標籤：

留言

張貼留言

這個網誌中的熱門文章

Excel VBA (二) : 自訂函數之自動加總(續)

繼續上篇的 Excel VBA (一) : 自訂函數之自動加總 來修改程式 加總時如果要將非數字的全部濾掉, 我利用 TypeName 這個函數來完成, 再將Excel的C欄資料除了文字型別以外, 加上日期以及布林型別, 測試結果如下 Function 我的加總(加總區域 As Range) As Double Dim data As Variant 我的加總 = 0 For Each data In 加總區域 If TypeName(data.Value) = "Double" Then 我的加總 = 我的加總 + data End If Next End Function 如果我現在很懶, 懶得選擇加總區域, 讓它自動將它上方的資料自動加總起來, 例如在A10中輸入自訂函數, 則會自動將A1到A9的儲存格都加總起來, 我的懶人程式如下 : Function 我的加總2() As Double Dim 目前儲存格 As Range Dim 目前列 As Integer Dim i As Integer Set 目前儲存格 = Application.Caller 目前列 = 目前儲存格.Row 我的加總2 = 0 For i = 1 To 目前列 - 1 Set 目前儲存格 = 目前儲存格.Offset(-1, 0) If TypeName(目前儲存格.Value) = "Double" Then 我的加總2 = 我的加總2 + 目前儲存格 End If Next End Function Application.Caller : 會取得函數計算的儲存格, 它的行跟列可以透過Column跟Row來取得 ( 參考文章 ) 加總則是從同欄的第一列開始加, 一直加到輸入的前一列, 也就是 Application.Caller.Row - 1 offset(-1,0) : 則表示要位移的位置, -1 表示垂直往上一格, 0 表示水平維持不變 我在A10輸入自
張貼留言
閱讀完整內容

Excel VBA (三) : 自訂函數參數的另一種用途

如果有用自訂函數來完成工作的人, 可能會發現有的自訂函數當你改變要計算區域的值時, 結果會跟著改變, 有的則不會跟著變, 如 Excel VBA (一) : 自訂函數之自動加總 會跟著變, 而 Excel VBA (二) : 自訂函數之自動加總(續) 則不會改變, 而這個答案就是參數 如果想要計算範圍內, 只要資料有異動, 則會自動更新計算結果, 就必須將該範圍納入自訂函數的參數( 參考微軟的文章 ), 參數裡的儲存格, 不論多少, 都是Excel監控的對象, 就如同 Excel 中的標準函數, 我只要輸入範圍, 那就會自動更新, 如果沒參數或是輸入資料而已, 如 SIN(100) 就不會有自動更新的功能, 因為沒監控對象 !! 如下圖中A欄黃色儲存格輸入的是 我的加總2() 無參數函數, 而橘色儲存格輸入的是 我的加總(A1:A9) 有參數函數, A欄的結果都是15, 我再將A欄複製到B欄後, 結果當然也是15,但是當我將B1儲存格從1改成2之後, 黃色無參數的結果沒變, 而橘色有參數的結果自動變成16了, 這是有參數的好處 測試時間: 2020年07月29日 測試環境: Windows 10 家用版, Excel 2019 家用版
張貼留言
閱讀完整內容