Excel VBA 基礎(02.6) - 常用函數 第一部分

之前詳細介紹了VB的基本語法，整個語言的框架已經構建完畢。

以下部分重點講解VBA中的常用內置函數。如果說語法部分是程序的主體框架的話，那麼內置函數就是VBA程序大廈的預製件。整體構架成型之後，只用按需進行填充即可。靈活運用內置函數可以大幅度提升程序編寫效率。

我們先從字元串函數說起。

再次提醒大家，在VBE下按下F2，開啟 對象瀏覽器 界面，方便查閱所有內置函數。

如上圖, 請找到Strings模塊。左邊所列示的就是此模塊下定義的所有 Public 函數。選中相關函數時，會顯示相關函數簽名。

大家可以看到，其中有我們之前介紹過的 len, left，string 函數，還記得這些函數的用法么？相關內容如果尚不清楚可以參考之前教程或按F1查看API，類似還有right, trim, replace，雖為實踐中常用函數，由於理解起來無障礙，自行閱讀文檔即可，均不在此講述。

重點提示 連字元 & ， 相關用法之前已經涉及到。

下面來講解 split 函數，

簽名如下

Function Split(Expression As String, [Delimiter], [Limit As Long = -1], [Compare As VbCompareMethod = vbBinaryCompare])

第一個參數Expression 為輸入字元串

第二個參數Delimiter為分隔符

第三個參數Limit 為返回數組最長長度

結合如下例子:

要求 取得科目序號 以及科目名稱 科目序號 長度為3到5位；科目名稱與科目序號以空格隔開，科目名稱中可能包含空格 如 8700 revenue EU -> 科目序號 8700 科目名稱 revenue EU 如 10000 receivables group A -> 科目序號 10000 科目名稱 receivables group A s 為科目序號加名稱 如 "10000 receivables group A", 返回值為長度為2的字元串數組 第一個元素為科目序號 第二個元素為名稱Public Function getAccNrAndName(ByRef s As String) As Variant getAccNrAndName = Split(s, " ", 2)End Function

與split 功能相反的函數是 join, 可以按指定的連字元將數組元素連綴起來。

另外一個難點，format 函數 。先看如下立即窗口中運行的例子：

第二個參數為 定義格式 > 將字元轉換為大寫? format("yang",">")YANG yyyy-mm-dd 分別為 年-月-日 ? format(now, "yyyy-mm-dd")2018-02-09 轉換手機號格式， @ 為字元佔位符? format("0139888888", "+86 (@) @@@ @")+86 (0) 139 888888 增加前置的0，#為數字佔位符? format(1, "0####")00001 以百萬為單位顯示，#為數字佔位符， ，為千位符，注意 點 前面的 斜杠 為轉意符 即顯示點本身? format(1998800000,"€ #,###,, Mio.")€ 1.999 Mio.

大家重點掌握以上例子即可。進一步細節請自行閱讀文檔。

格式函數 (Visual Basic for Applications)msdn.microsoft.com

題外話，學習的能力和習慣其實比 -知識本身更加重要。特別在這個知識迭代日益頻繁的時代，時時更新知識儲備遠比陳腐的知識存量重要得多。

查閱API文檔無疑是學習編程過程中至關重要的一步。不僅僅是VBA， 對於其他所有編程語言的學習均適用。官方的參考遠遠強過市面上所有的教程。

字元串相關函數補充注意事項：

• 實戰中務必要注意字元串中前置以及後置的空格，特別是在比較兩工作表字元串的時候推薦先trim 去掉前置以及後置空格。
• inStr用來判斷目標字元串在母字元串中位置，請務必掌握。
• 將其他類型轉換為字元串推薦用 &
• 就字元串處理的高階技巧 還需要進一步掌握 正則表達式 (RegExp)。相關知識，留待以後專門章節講述。

數字 相關函數

絕對值，對數，指數，三角函數略去不表。

重點介紹一下實踐中常用到的一些功能，

取余，mod ，以及取整， (注意，與除號方向相反)

這一個關鍵字，

A mod B, A除以B無法整除的部分。

A B, A除以B可以整除的部分

? 19 mod 7 5 ? 19 7 2

19 除以 7 ， 14 / 7 = 2, 餘下 5 (19 - 14)

處理周期性重複問題時經常用到。

例子: 2.6.1 不採用日期函數的前提下，寫一個函數dayOWeekAfterNDays，參數dayOWeek 為當前星期，以及經過的天數N，要求返回N天之後是星期幾。

Option ExplicitPrivate Function dayOWeekAfterNDays(ByVal dayOWeek As Integer, ByVal n As Long) As Integer 7 天為一個周期， 注意小於等於0的情況 dayOWeekAfterNDays = (dayOWeek + n) Mod 7 dayOWeekAfterNDays = IIf(dayOWeekAfterNDays <= 0, dayOWeekAfterNDays + 7, dayOWeekAfterNDays) End FunctionPrivate Sub main() Debug.Assert dayOWeekAfterNDays(1, -6) = 2 Debug.Assert dayOWeekAfterNDays(1, 6) = 7 Debug.Assert dayOWeekAfterNDays(1, -1) = 7 Debug.Assert dayOWeekAfterNDays(7, -140) = 7 Debug.Print "all tests past!"End Sub

隨機數生成器 rnd, 隨機產生0到1之間的小數

? rnd 0,5827582 ? rnd 0,7485327

例子: 2.6.2, 要求返回長度為N的數組，數組中的值為 大於等於 start 小於 ende 的隨機數。

Option ExplicitPrivate Function generateNRndNr(ByRef start As Long, ByRef ende As Long, ByRef n As Long) As Variant 構造返回數組 Dim res() ReDim res(0 To n-1) Dim i For i = 0 To n-1 代碼核心， 可以打斷點 嘗試利用實例來理解, 如果無法理解 請務必記住結果 Int 返回小於參數的一個最近的整數 int(1.1) = 1 int(-1.1) = -2 res(i) = start + Int(Rnd * (ende - start)) Next i generateNRndNr = res End Function 測試 列印結果 結合之前字元串函數 明白join運行原理了么 請考慮 為什麼之前文章關於 遞歸 數組轉字元串 為什麼不能採用join函數。Private Sub main() Debug.Print "[" & Join(generateNRndNr(0, 10, 20), ", ") & "]" End Sub

例子: 2.6.3，隨機抽查函數，從選中的填充有賬單編號工作表區域隨機選取N個單元格並標黃。

提示:

• 區域有一個Cells屬性，各Cell元素 索引從1開始遞增順序排列，如第一個單元格為 Selection.Cells(1), 依此類推
• 注意N個單元格為不重複的單元格
• 標黃的函數可參考上期
• 函數簽名為 Private Function sampling(ByVal n As Long)
• 多次運行函數達下類似下列效果

有任何問題請在下方留言。

本專欄所有文章著作權歸屬本人。未經本人書面許可，除知乎日報外，任何人不得轉載。