字體：小 中 大 |
|
|
|
| 2019/11/25 23:01:07瀏覽6664｜回應2｜推薦3 | |
|
朋友來信說，Excel 公式裡的 Sum() 函數不會略過隱藏欄和列的儲存格，用 Subtotal() 函數的 109 號功能也只會略過隱藏 "列" 的儲存格，可不可以用 VBA 巨集來解決這個問題？於是，我試著寫了一個 "使用者自定函數 (user-defined function)" 命名為 SumX()，就是在 VBA 編輯器裡，插入-->模組，然後在模組試窗裡面寫入函數程式碼：Public Function SumX(SumRange As range) ... ... End Function，就可以在這個活頁簿的儲存格裡使用這個 SumX() 函數，它的參數是一個 (可涵蓋多列多欄的) "範圍"(註3)。 *註：朋友來信要求將 SumX() 做成可以接受多重不連續範圍，因此修訂版 (v1.5 版) 參數的宣告寫法與此處不同，程式裡的迴圈也比底下圖中的程式碼多一層迴圈。詳細請看本文末尾的說明。
這個函數的原理很簡單，就是算出指定的範圍有多少列、多少欄。Range 的 .Count 是範圍內包含多少儲存格；Range 的 Rows.Count 是範圍內包含多少列；Range 的 Columns.Count 是範圍內包含多少列。 然後用兩層 for 迴圈依序把沒有被隱藏的每列每欄的儲存格值加總起來。"SumX =" 就是把值傳回公式。 比較特別的是 Application.Volatile 方法(method)它只能放在函數裡，不可以放在函數外，它的作用是告訴 Excel 說，每當活頁簿有儲存格內容的異動時, 就要自動重新計算(執行)此函數。它有個布林值引數，預設為 True，可省略。詳細請參閱 Microsoft Office Dev Center 關於它的說明文件 (讀英文版，中文版是電腦翻譯的，保證愈看愈不懂)
來看看 SumX() 的效果，我把它和 Sum()、Subtotoal (9, ......)、Subtotoal (109, ......) 做了一個簡單的比較，如下圖，在工作表裡面有四個公式都是加總 A2:F2 範圍的儲存格，目前加總的值都是 111111; 另外有四個公式都是加總 F2:F6 範圍的儲存格，目前加總的值都是 11111：
此時，若將 B 和 C 欄隱藏起來，會看到結果如下圖：
這時候 SumX(A2:F2) 的加總值沒變，跟其他幾個公式一樣，是因為 "欄的隱藏" 沒有觸動 Excel 的重新計算 (此時如果在活頁簿任何一個儲存格輸入或刪改內容，就會看到 SumX 的計算結果更新)。 接下來若隱藏第 4 列，會看到 Subtotal(109, F2:F6) 加總值少了 100，SumX(F2:F6) 的加總值也少了 100，也就是少了 F4 儲存格的值。也就是說，"列的隱藏" 會觸發 Excel 的重新計算。
寫在模組裡的使用者自定函數，有效使用範圍僅只於它所在的活頁簿，若要在別的活頁簿裡使用，需把模組程式碼拷貝過去。你可以從一個檔案把這個模組匯出 (按 Ctrl+E) 存成 Basic 檔案 (.BAS)(註1)，再匯入 (按 Ctrl+M) 另一個檔案。 或者你可以把這個活頁簿 "另存ce新檔" 為 .XLA (.XLAM) 增益集檔案，然後將它安裝到你的 Excel 裡面，就不需在每個活頁簿檔案裡面加入這個 VBA 模組了。(網路上可以找到安裝啟用增益集的指導影片，我不在此贅述) ※註1：來信索取 email 標題請寫「我要 SumX() 函數 BAS 檔」 (SumX程式碼很短，建議您自行輸入) ※註2：關於 Subtotal() 的 9 號和 109 號功能比較，請參閱 Vincent 的部落格： https://isvincent.pixnet.net/blog/post/45160967
程式裡主要是將 Function 的引數改成一個 "參數陣列 (Parameter Array)"，也就是使用 "ParamArray" 這個關鍵字宣告 傳入的是 RangeList() 是一個陣列，此陣列是由 Variant(不定型別) 的元素構成 (預設是 Variant，所以 "As Variant" 可以省略不寫)。 在函數裡則增加一層 for 迴圈 "For Each ... In ... Next" 使參數陣列指定的每個範圍都被處理 (檢查隱藏屬性、加總)。
關於 "參數陣列 (Parameter Array)"，可以參閱 Microsoft Office Dev Center 裡的相關說明 (中文是電腦翻譯的，建議讀英文版) |
|
| ( 知識學習｜其他 ) |
