Excel VBA 基礎(02.3)

02-04

在講解代碼之前，先介紹一個概念，遞歸。

按慣例，不扯學術。

「從前有座山，山上有座廟，廟裡有個老和尚跟小和尚講故事: 從前有座山，山上有座廟，廟裡有個老和尚跟小和尚講故事: ... 」

用數組表達出來相當於 [a, [a, [a, ...]]],

a = "從前有座山，山上有座廟，廟裡有個老和尚跟小和尚講故事",

a 後面數組相應括弧裡面的內容是老和尚講故事的內容

也可表達為 A = [a, A]，將 A 無限迭代入等號右邊。

第一次迭代nA = [a, [a, A]]n第二次迭代nA = [a, [a, [a, A]]]n第N次迭代nA = [a, [a, [a, [a, ...[a, A]]]]] (N + 1 對括弧)n

A = [a, A] 提示我們，N趨於無窮時，A是自己的組成部分。（這也解釋了電影 <前目的地>燒腦的原因。幻境亦是現實。）

等號右邊括弧裡面的A 等同於等號左邊的A，同時又是等號左邊的A的組成元素之一。

結合之前的例子2.2.2 ，一個數組(A)中可能包含普通元素(a)或另一個數組(A) ，現在我們要定義將數組轉換成字元串的方法A, 要實現方法A，根據分-治 1. 定義一個能夠處理兩類元素的函數(將數組元素以及非數組元素轉化為字元串。同時，將數組元素轉化為字元串又是我們正在定義的最終目標函數) 2. 將第1步處理結果整合（將各種元素的字元串連接起來）

上代碼

輸入數組，返回數組字元串nPrivate Function arrToStr(ByRef arr) As Stringn res 默認為返回值變數n Dim res As Stringn 數組循環變數一般為 i j kn Dim in n 1. 分別處理兩類元素n 如果為數組，即此元素也為 A 時n If IsArray(arr) Thenn 如果為空數組(長度為0)，則直接返回 [ ]n 務必注意特殊情況n If UBound(arr) - LBound(arr) + 1 = 0 Thenn res = "[ ]"n Elsen 如果為數組且非空n 2. 整合處理結果n res = "["n n 遍曆數組元素，實踐中常用foreachn For i = LBound(arr) To UBound(arr)n 將各組成部分的字元串表示連綴起來 n 數組轉成字元串本身也就是我們正在寫的這個函數n 在自身定義中調用自身就是遞歸函數的精髓所在，如同上面例子中A的定義n res = res & arrToStr(arr(i)) & ", "n Next in 將最末多餘的 ", " 去掉n res = Left(res, Len(res) - 2) & "]"n End Ifn Elsen 如果元素非數組返回元素本身字元串，也可使用Cstr n res = "" & arrn End Ifn n arrToStr = resnnEnd Functionn

這裡特別要強調，【寫測試時，一定要考慮臨界值以及特殊情況】。

常見例子有空數組，空字元串，未賦值單元格等等。測試一定要覆蓋全面，如果真等到實際使用中出現問題，後果可能要嚴重得多。

此類 A = [a, A] 的例子都可以用遞歸處理，比如說還有輸出文件夾結構，輸出會計科目結構。以後出現遞歸的實例，我會提醒大家試一試。

請大家務必結合測試例子逐步分析程序運行順序，以加深對遞歸理解。

1.5.2 的例子也是遞歸。對於初學者來說，遞歸應當算最難以理解的概念，沒有之一，但這也正是編程思維的精華之所在。如果一下子消化不了也不要擔心，寫代碼也是一個熟能生巧的過程。

接下來，我們要說，循環之for each。它的作用是遍歷集合中的所有元素。這裡的集合可以是數組，Dictionary, 文件夾，工作表區域中的單元格等等。

例如在2.2.2中，遍曆數組實踐中的寫法如下，要比for 簡潔得多

遍曆數組元素，實踐中常用foreachn For Each i In arrn res = res & arrToStr(i) & ", "n Next in

模式為

For Each 元素變數 In 集合nnNext 元素變數n

與for 區別為

此時不再有整數循環變數而是成員元素本身，
元素變數不能聲明為基本類型，
沒有Step。所有元素都會遍歷一次，
不用關心索引的問題，適用於包括數組在內的所有集合。

例子2.3.1 添加工作表

要求描述

為當前選中的區域單元格中公司增加四張相應的報表(rep1, rep2, rep3, rep4)。每張報表為一單獨工作表，工作表名稱格式為兩位數連續編號_公司名_報表名，如 "01_c1_rep1", "05_c2_rep1"

如上圖中選擇

針對當前選中區域，Excel VBA單獨定義了一個變數叫 Selection。該變數類型為引用類型即對象，具體而言是 Range 類型。這不同於我們之前所說的基礎類型。關於引用類型以後詳細介紹，這裡只需要知道 Range對象有一個集合屬性（所謂屬性，可以理解為描述對象的特徵）Cells，Cells指本區域內單元格的集合，通過Selection.Cells我們即可以訪問該集合。

上代碼

Option ExplicitnnPublic Sub Main()n n generateShtsnnEnd Subnn 依據所選單元格建立工作表nPrivate Function generateShts()n n Dim c As Rangen i 不能聲明為字元串類型n Dim in cnt 為記數器，每次循環後加1n Dim cnt As Integern cnt = 1n n 選中區域中已使用的部分, 防止誤操作n For Each c In Intersect(ActiveSheet.UsedRange, Selection).Cellsn 將要生成的報表名稱放在數組裡，實踐中一般不會直接將數據寫入程序當中，而會放在工作表當中。n For Each i In Array("rep1", "rep2", "rep3", "rep4")n Debug.Print "cnt is " & cnt & ". c is " & c.value & ". i is " & in addShtWithName getShtName(cnt, c.Value, i)n cnt = cnt + 1n Next in Next cnnEnd Functionnn按要求返回目標工作表名稱，參數為計數器公司名稱報表名稱nFormat函數水很深，有興趣自己參考API。目前只需要了解 Format(cnt, "0#") 作用是增加一個前置的0nPrivate Function getShtName(cnt As Integer, companyName As String, ByVal reportName As String) As Stringn n getShtName = Format(cnt, "0#") & "_" & companyName & "_" & reportNamen nEnd Functionnn 在當前工作薄增加指定名稱報表n 此函數待講完 Excel對象後再回頭來看看nPrivate Function addShtWithName(shtName As String)n On Error Resume Nextnn Application.ScreenUpdating = Falsen n Worksheets.Add(, Worksheets(Worksheets.Count)).Name = shtNamen n Application.ScreenUpdating = Truen nEnd Functionn

分析業務邏輯，依次為各公司創建4張報表並指定為相應名稱。定義一個指定名稱的輔助函數，以及一個依據指定名稱創建工作表的函數。現在只需要兩層循環嵌套即可得到所需相應參數。

關於程序調式以及運行流程分析有一個實戰技巧。左鍵點擊相應行數左邊框空白處，即可設置斷點，每次執行到此句都會暫停。（注意取消注釋，否則無法標記該行）你可以逐步觀察變數的變化。繼續運行單擊向右的綠色三角或按F5

此時當你將滑鼠懸停在變數名上會顯示相應變數值。

Intersect(ActiveSheet.UsedRange, Selection)，表示選中區域中已使用的部分。使用，Intersect(ActiveSheet.UsedRange, Selection).Cells 而非直接 Selection.Cells 防止選中整列運算量過大使程序假死。具體細節以後再講。

2.3.1 除了addShtWithName 函數外，其他請動手試一試。

本節代碼如下。

http://qiou.eu/xl/Demo02.3.xlsmqiou.eu

基礎(02)部分的知識相當重要, 特別是循環兩模式 for 和 foreach ，請務必熟練掌握。

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

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