跳到主要內容

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 C# (一) : 增益集 (Add-Ins) 之自訂函數

Excel的自訂函數除了之前介紹的VBA方式外, 還可以用 JavaScript, C# 等, 我就來試試看用C#來開發 我爬文找到了這篇 Excel-DNA 裡有詳細的教學, 不過我還是自己照做一遍看看是否成功 ! 第一步先開啟 Visual Studio 2019, 專案選擇 "類別庫(.NET Framework)" 過來將專案名稱輸入 MyExcelAddIns, 其他不變按下 "建立" 第二步接著在 "工具"->"NuGet 套件管理員"->"管理方案的 NuGet 套件", 將 Excel-DNA 套件加入至專案中 第三步將專案中的 Class1.cs 檔案刪除, 自己新增一個類別 "MyFunctions", 然後我複製 https://excel-dna.net 該網站上的範例, 貼進來方便測試 第四步我則是將方案組態由 "Debug" 改成 "Release"(如上圖的紅框), 再按 "建置" 下的 "重建方案" 產生 Excel XLL 檔案, 再去方案總管的 MyExcelAddIns 上按右鍵, 選擇 "在檔案總管中開啟資料夾" 會看到下面第二張圖 過來我的習慣是將這個 Release 目錄整個複製, 貼到桌面方便測試 ! 第五步則是開啟空白的Excel, 按 "程式開發人員" 下的 "Excel增益集", 點擊瀏覽後, 選擇桌面上我剛剛的 Release 資料夾, 如果Excel是32位元則選 MyExcelAddIns-AddIn.xll 檔案, 如果是64位元版本則選擇 MyExcelAddIns-AddIn64.xll 檔案, 接著在 Excel 任一儲存格輸入 =SayHello("World!"), 結果出現 Hello World!, 表示一切都正確, 接著我可以開始建立自己的函數了! 最後我個人認為自訂函數的VBA與C#兩種方式, 其優缺點如下:VBA的優點...
張貼留言
閱讀完整內容