Excel VBA 基礎(01.4)

非常感謝大家對本專欄的認可。

最近事務所在做一個合併報表的項目，10個公司4年的明細賬（科目設置各不相同）上千條調整、合併、抵銷分錄，以至最後形成合併報表，如果沒有VBA的幫助，通過公式純手工處理工作量不可想像。實踐中學好VBA，前途不可限量。

剛加完班，頭腦不是太清醒。我們先聊一聊方法論的問題。上期文章發布後，有不少朋友通過私信或留言的方式詢問我，對於初學者而言VBA應當如何上手。現在做一個總結。依個人經驗來看，至少應當在全面了解VB基本語法以及Excel相關一系列重要的對象的屬性和方法之後再開始上手實踐項目，即Excel VBA 基礎(01.1)中所列提綱的第1以及第2大點。鏈接後附。

楊風颯：Excel VBA 基礎(01.1)zhuanlan.zhihu.com

由於VBA與現行VB語言就標準庫而言存在極大差異，許多高階編程語言理所應當具備的語言特性的缺失(如ArrayList, HashSet等容器，以及Lambda表達式等特性)，相關功能只能依靠使用者實現，這對於新手而言是極度不友好的；並且市面上鮮有體例完備的VBA教程(至少在我學習VBA的當時沒見到過)，對於沒有任何編程經驗的財務人員來說又增添一重障礙。

如果你已經具備了相應的基礎，那麼你已經可以開始上手日常的項目，可以在平時工作中考慮採用VBA優化你的工作流程，多思考，多動手實踐，對自己英文比較自信的朋友碰到問題建議多用Google以及StackOverflow，從其他人的最優實踐中慢慢打磨自己的技藝。同時，我也會視大家的反饋，更新本專欄中Excel VBA 實戰的內容，每一期以項目的形式展示我工作實踐當中所解決的一些典型問題。

如果你們遇到任何關於本教程或實踐中Excel VBA的問題，請隨時跟我留言，我將儘可能地在第一時間盡我所能解答你們的問題。

扯淡之後頭腦子清醒了一些。繼續上次的例子。大家都有答案了吧。

Option ExplicitnnPublic Sub main()n n Debug.Print getLongerStringLen2("qiou.eu", "sgfxq", "yang")n nEnd Subnn return the length of the longer stringnPrivate Function getLongerStringLen(str1 As String, str2 As String) As Integernn length of str1n Dim l1 As Integern n length of str2n Dim l2 As Integern n get length of str1n l1 = Len(str1)n n get length of str2n l2 = Len(str2)n n return the length of longer stringn getLongerStringLen = IIf(l1 > l2, l1, l2)nnEnd Functionnn return the length of the longer stringnPrivate Function getLongerStringLen2(str1 As String, str2 As String, str3 As String) As Integernn length of str1n Dim l1 As Integern n length of longer string out of str2 and str3n Dim l2 As Integern n get length of str1n l1 = Len(str1)n n get length of longer string out of str2 and str3n l2 = getLongerStringLen(str2, str3)n n return the length of longer stringn getLongerStringLen2 = IIf(l1 > l2, l1, l2)nnEnd Functionn

問題很容易，初學者就是要掌握這種編程的感覺。寫函數就是為了使代碼能夠方便的重複使用，這樣大大增加了編程的靈活度並且提高了效率。

1.4.1 作用域 & ByRef

Option Explicitnn accessible in the scope of ModulenDim val1 As LongnnPublic Sub main()n n plusOne val1n n Debug.Print "The Value Of val1 is " & val1n n accessible in the scope of Subn Dim val2 As Longn n plusOne val2n n Debug.Print "The Value Of val2 is " & val2n n Debug.Print String(10, "*")n nEnd Subnn plus one to the valuenPublic Function plusOne(ByRef v As Long)nn v = v + 1n nEnd Functionnn plus one to the valuenPublic Function plusOne2(v As Long) As Longnn plusOne2 = v + 1nnEnd Functionn

複製這段程序，到你的Main Module。

我們接下來要講變數的作用域。作用域的基本原理是Module上定義的變數(val1)對本Module的全部函數以及Sub可見，在某個函數內部定義的變數(val2)只限於此函數。空談太過抽象，請嘗試多次連續運行程序。我們看到如下輸出。

我們來逐步解釋。

首先，val1定義在Module最外層，沒有被其他任何Sub或函數包裹，作用域為當前Module，為本Module的全部函數以及Sub可見。

main雖為入口函數，但其本質上也屬於本Module的Sub，故在main當中也可以訪問(即，該變數可見並可以直接使用)該變數。

接下來碰到plusOne, 我們找到相關函數簽名，我們注意到有個關鍵字(即，有特殊意義的系統保留字如 Sub, Public, Option。關鍵字不能成為變數或函數名。想想為什麼。)ByRef，引用傳遞。

不談學術。設想如下例子，你做完表格，上傳到公司伺服器上，並將表格的鏈接發給你們CFO，CFO打開表格之後，將第一張表的第一行第一列從0改為1，然後保存並關閉。第二天你打開同一個鏈接，你看到的第一張表的第一行第一列數據是多少？

第二次，你直接將表格本身作為郵件附件發送至CFO，CFO進行相同操作。第二天當你打開已發送郵件的附件，你看到的數據又是多少？

第一種情況，傳遞鏈接，兩人共用同一張表，叫引用傳遞又叫傳址, ByRef；第二種情況，傳遞一份複製，叫傳值, ByVal。

由於引用傳遞前後針對同一個對象，任何一方進行更改都會影響到該值本身。(對應第一種情況)，而傳值由於只是本體的複製，並不會對本體產生影響，VBA默認為傳值(即不明確指定ByVal或ByRef的情況下，默認為ByVal)。

+++++++++++++++

關於默認為傳值或傳址的問題，我又查了一下官方文檔，確認為傳值。

How to: Force an Argument to Be Passed by Value (Visual Basic)docs.microsoft.com

但實際操作中，編譯器以及Excel版本的原因，可能出現默認傳址的情況。建議大家對於引用變數務必要指定傳遞方式。

+++++++++++++++

再來看plusOne函數體，將鏈接指向的對象加1。傳遞對象為val1，由於只聲明沒有賦值，故為默認值0，plusOne處理完畢後val1變為1。當你再次運行main, 此時val1累加變為2。

對比val2, 定義在main的Sub之內。每當運行一次main意味著， main之內的所有命令都要重新執行一次。也就是說val2每次都會重新聲明並賦默認值0。

【& 為連字元，用來將前後兩個元素連成一個字元串 "a" & 1 會變成 "a1"】

string 函數 第一個參數為重複次數，第二個參數為字元樣式 。此例為星號重複10次。

問題留給大家思考, 如果用plusOne2 達到同樣的效果，main的函數體應該如何修改?

1.4.2 例子: 等差數列製造器

請補充getVal的函數體，使得連續運行Main函數後得到如下圖所示的列印結果

Option Explicitnn accessible in the scope of ModulenDim val1 As LongnnPublic Sub main()n n plusOne val1n n Debug.Print getValn Debug.Print String(10, "*")n nEnd Subnn plus one to the valuenPublic Function plusOne(ByRef v As Long)nn v = v + 1n nEnd Functionnn generate series of number 8, 11, 14, 17, 20, 23nPublic Function getVal() As Longn n TODO: add your code heren nEnd Functionn

請大家務必預先嘗試，歡迎給出你們的代碼。

解析留待下期。

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

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