Excel 與 VBA 財務應用 第九課 (336)
Eugene Chang (張佑成)
November 23rd 2024
# Excel 與 VBA 財務應用初階班  --- # 範例 Excel 檔案 請下載：[Lecture 8 - 自訂函數](https://drive.google.com/uc?export=download&id=11Zv5JRYZHnHTBqihZ5aY-K1t4TbTi1nM) --- # Function（副程式） 又稱自訂函數（User Defined Function） 讓開發者打造自己的函數，可以在 Excel 前端被使用 --- # Function 定義方法 ```vb Function 函數名稱(參數1 as 資料型別, 參數2 as 資料型別) as 回傳的資料型別 函數名稱 = 用參數運算後的結果 End Function ``` --- # Function 練習 計算 bmi 值 ```vb Function BMI(height as Integer, weight as Integer) as Double BMI = _____________________ End Function ``` --- # 讓自訂函數支援陣列運算 要是我們今天希望使用者可以將一個範圍當作參數放入 function 換句話説，就是要如何能讓我們的 function 支援陣列運算？ --- # 範例 Excel 檔案 請下載：[Lecture8 - 陣列範例](https://drive.google.com/uc?export=download&id=1Kh8uIaHq6e3fiP7z8bPEAOjPFNquS7oc) --- # 資料結構（Data Structure） 到目前爲止，我們在這堂課遇到的問題，資料的量級都不大 但是，若今天我們需要用程式處理的問題在量級上改變了，就需要用更高明的方法來解決問題 --- # 資料結構 Data Structure 今天假設你需要紀錄多天的台積電股價 ``` 224.5， 224.5， 233， 237.5， 237.5， 238 ``` --- # 若需要用變數存所有的數值... 土法鍊鋼法： ```vb Dim price1 as Double: price1 = 224.5 Dim price2 as Double: price2 = 224.5 Dim price3 as Double: price3 = 233 Dim price4 as Double: price4 = 237.5 Dim price5 as Double: price5 = 237.5 Dim price6 as Double: price6 = 238 ``` --- # 若今天需要計算這些股價的加總... ```python total = price1 + price2 + price3 + price4 + price5 + price6 ``` 需要計算的數值越多，程式碼就越容易變得冗長... --- # 若我們把這個問題提升一個量級 需要計算 100 筆、或是 1000 筆股價的加總呢？ 你不會希望在程式碼内宣告 1000 個變數... --- # 陣列（Array） 是一種能夠裝載大量，型別相同資料的容器 --- # 陣列（Array） 可以把它想象成一排編了號碼的抽屜  --- # 宣告陣列 在變數名稱的後方加上 `()` 符號，裏面標記長度 ```vb Dim ary(6) as Integer ``` 現在我們就有了一個能裝下六個整數的陣列 --- # 將資料寫入陣列 要將資料寫入陣列的某一個空間，需要用索引值（Index）來指定。 特別注意索引值是從 0 起跳 ```vb ary(0) = 224.5 ary(1) = 224.5 ary(2) = 233 ary(3) = 237.5 ary(4) = 237.5 ary(5) = 238 ``` --- # 將資料輸出至試算表 任何一個 Range 的 Value，都是被封裝在一個陣列内 --- # 將資料輸出至試算表 先選擇與陣列大小一樣的範圍，再將陣列賦值給該範圍 ```vb Range("A3:F3").value = ary ``` --- # 將資料輸出至試算表  --- # Range 與 VBA 陣列 任何一個試算表上的 Range，也就是多個連續的儲存格，**裏面的多筆資料都是被封裝在一個陣列裏面** --- ## 所以回到我們原本的範例 ```vb Dim ary(6) as Integer ary(0) = 224.5 ary(1) = 224.5 ary(2) = 233 ary(3) = 237.5 ary(4) = 237.5 ary(5) = 238 Range("A1:F1").value = ary ``` 這段程式碼可以解讀成： 1. 宣告一個大小為 1 x 6 的 VBA 陣列 2. 放滿資料之後 3. 在一次性的寫入到 `A1:F1`，大小也是 1 x 6 的儲存格範圍 --- # 多維陣列 若今天資料有多個維度，該如何用陣列封裝？ VBA 支援多維度的陣列 --- # 多維陣列 宣告多維陣列的方法： ```vb Dim 陣列名稱(第一維長度, 第二維長度) as 資料型別 ``` 範例： ```vb Dim mat(3, 2) As Integer '宣告一個 3 x 2 大小的陣列 ``` --- # 多維陣列 將資料寫入多維陣列： ```vb 陣列名稱(第一維索引值, 第二維索引值) = 要指定的值 ``` --- # 多維陣列 範例： ```vb mat(0, 0) = 1 mat(0, 1) = 2 mat(1, 0) = 3 mat(1, 1) = 4 mat(2, 0) = 5 mat(2, 1) = 6 ``` --- # 多維陣列 若要將值輸出到 Excel 試算表上： ```vb Sub MultiDimArray() '宣告 3 x 2 大小的陣列，mat Dim mat(3, 2) As Integer mat(0, 0) = 1 mat(0, 1) = 2 mat(1, 0) = 3 mat(1, 1) = 4 mat(2, 0) = 5 mat(2, 1) = 6 '將陣列寫入到大小一樣的 Excel 儲存格範圍 Range("A8:B10").Value = mat End Sub ``` --- # 多維陣列 最後我們的陣列就被寫入 `A8:B10` 了！  --- # 小結 - VBA 陣列是一個活在記憶體内，虛擬的表格 - 任何一個 VBA 的 Range.Value 它的資料都是封裝在一個 VBA 的陣列内 - 想要一次寫入多筆資料到一個範圍時，需要宣告相同大小的 VBA 陣列去封裝資料 --- # Option Base 1 因爲索引值相對不直覺，所以我們可以透過設定 `Option Base 1` 來强制該模組内所有的陣列索引值都是從 1 開始 --- # 陣列運算（效能） 接下來請試試看產生十萬個隨機數（Random Number） 並且將這十萬個隨機數顯示在 A1 到 A100000 --- # Rnd() 函數 產生隨機數的函數 ```vb Debug.print Rnd() ``` --- # 範例程式碼 ```vb Dim i as Long For i = 1 To 100000 Cells(i, "A").value = Rnd() Next i ``` --- # 如何截取執行所耗費的時間？ 可以使用 `Timer` 函數，就像按碼表一樣，它會將當下的時間以從零點開始過多少秒的方式記錄下來 --- # 範例程式碼 ```vb Dim i as Long Dim timeStart as Double Dim timeEnd as Double Dim timeSpent as Double '記錄迴圈起跑前的時刻 timeStart = Timer For i = 1 To 100000 Cells(i, "A").value = Rnd() Next i '記錄迴圈跑完的時刻 timeEnd = Timer '耗費的時間 = 結束時間點 - 開始時間點 timeSpent = timeEnd - timeStart Debug.print "Cells 費時： " & timeSpent ``` --- # 一個提升效率的原則 每次使用 `Range().value` 和 `Cell().Value` 都是在讀取或寫入 Excel 試算表 讀取或寫入會耗費時間 所以要**避免增加讀取與寫入 Excel 的次數** --- # 改成用陣列處理 *請確認有把 Option Base 1 打開 ```vb Dim i As Long Dim timeStart As Double Dim timeSpent As Double Dim x(100000, 1) As Double timeStart = Timer For i = 1 To 100000 x(i, 1) = Math.Rnd() Next i Range("A1:A100000").Value = x timeSpent = Timer - timeStart Debug.Print "陣列費時: " & timeSpent ``` --- # 小結 陣列存在的目的，是希望我們能夠有效的處理大量的資料 當資料的量級提升，效能的差異就越明顯 --- # 動態陣列（Dynamic Array） 若遇上了**不確定陣列大小的狀況**，就無法定義陣列了... --- # 動態陣列（Dynamic Array） 我們希望能夠有類似以下的功能，得知長度之後，再將陣列宣告出來： ```vb Dim length as Integer length = Inputbox("請輸入長度：") Dim ary(length) as Integer ``` --- # 動態陣列（Dynamic Array） 可以用動態宣告陣列的方法，先產生出陣列再處理 ```vb '宣告名爲 ary 的空陣列 Dim ary() As Double Dim length As Integer: length = 2 ReDim ary(length) as Double ary(1) = 101 ary(2) = 777 ``` --- # 動態陣列（Dynamic Array） 若需要再次改變陣列的大小，可再次使用 `ReDim` ```vb '宣告名爲 ary 的空陣列 Dim ary() As Double Dim length As Integer: length = 2 ReDim ary(length) as Double ary(1) = 101 ary(2) = 777 ReDim ary(3) as Double ary(3) = 888 ``` --- # 動態陣列（Dynamic Array） 但是，若我們將改變過長度的陣列的值印出來看時： ```vb ary(1) = 101 ary(2) = 777 ReDim ary(3) as Double ary(3) = 888 Debug.print ary(1) '0 Debug.print ary(2) '0 ``` 就會發現原本陣列的值都不見了... --- # 動態陣列（Dynamic Array） 當使用 `ReDim` 時，VBA 會建立一個新的陣列，而非改變原本陣列的長度 --- # 動態陣列（Dynamic Array） 若要改變陣列長度，有需要保留原本陣列的内容，可以用 `Preserve` 關鍵字 ```vb ReDim Preserve ary(3) as Double ``` --- # 動態陣列（Dynamic Array） 修改原本的程式碼： ```vb ary(1) = 101 ary(2) = 777 ReDim Preserve ary(3) as Double ary(3) = 888 Debug.print ary(1) '101 Debug.print ary(2) '777 Debug.print ary(3) '888 ``` --- # Ubound 函數 偵測一個陣列的最大索引值： ```vb Dim x(1 To 10) As Integer Debug.Print UBound(x) '10 ``` --- # Lbound 函數 偵測一個陣列的最小索引值： ```vb Dim x(1 To 10) As Integer Debug.Print LBound(x) '1 ``` --- # 偵測多維陣列大小 偵測一個陣列的最小索引值，用第二個參數指定偵測的維度： ```vb Dim x(1 To 10, 2 To 6) As Integer Debug.Print LBound(x, 1) '1 Debug.Print UBound(x, 2) '6 ``` --- # Array() 函數 若需要快速產生出一個一維陣列，可以使用 `Array()` 函數： ```vb Dim ary As Variant ary = Array(1, "ABC", True) Range("A1:C1").Value = ary ``` *注意需要把陣列宣告成 `Variant` 型別 --- # 練習：計算 BMI 值  請從試算表上截取所有的身高與體重，算出 BMI 值之後，再一次性的寫入至 C 欄標定的地方： ```vb Dim weights As Variant Dim heights As Variant heights = Range("B1:F1").Value weights = Range("B2:F2").Value ``` --- # 解答 ```vb Sub ArrayFuncEx1() Dim weights As Variant Dim heights As Variant Dim i As Integer Dim bmi() As Double heights = Range("B2:F2").Value weights = Range("B3:F3").Value '宣告 n x 1 大小的陣列 ReDim bmi(UBound(heights, 2), 1) As Double For i = LBound(weights, 2) To UBound(weights, 2) bmi(i, 1) = weights(1, i) / (heights(1, i) / 100) ^ 2 Next i Range("B7:B11").Value = bmi End Sub ``` --- # Transpose 函數 我們經常遇到的狀況是，資料是個 1 x N 的陣列，但是最後需要輸出的範圍是 N x 1，或是剛好相反 --- # Transpose 函數 因此我們希望有一個功能能夠快速轉置的方法：  --- # Transpose 函數 簡答來説就是把二維陣列反過來： ```vb Sub ArrayTranspose() Dim ary As Variant Dim aryT As Variant ' 1 x 6 ary = Range("A3:F3").Value Debug.Print UBound(ary, 1) '1 Debug.Print UBound(ary, 2) '6 aryT = Application.WorksheetFunction.Transpose(ary) Debug.Print UBound(aryT, 1) '6 Debug.Print UBound(aryT, 2) '1 ' 6 x 1 Range("A3:A8").Value = aryT End Sub ``` --- # 修改原本的 BMI 寫法 ```vb Sub ArrayFuncEx1() Dim weights As Variant Dim heights As Variant Dim i As Integer Dim bmi() As Double heights = Range("B2:F2").Value weights = Range("B3:F3").Value ReDim bmi(UBound(heights, 2)) As Double For i = LBound(weights, 2) To UBound(weights, 2) bmi(i) = weights(1, i) / (heights(1, i) / 100) ^ 2 Debug.Print bmi(i) Next i Range("B7:B11").Value = Application.Transpose(bmi) End Sub ``` --- # 另一種寫法 ```vb Sub ArrayFuncEx1() Dim weights As Variant Dim heights As Variant Dim i As Integer Dim bmi() As Double heights = Range("B2:F2").Value weights = Range("B3:F3").Value heights = Application.Transpose(heights) weights = Application.Transpose(weights) ReDim bmi(UBound(heights), 1) As Double For i = LBound(heights) To UBound(heights) bmi(i, 1) = weights(i, 1) / (heights(i, 1) / 100) ^ 2 Next i Range("B7:B11").Value = bmi End Sub ``` --- # 讓自訂函數支援陣列運算 輸入是兩個 Range，輸出則是一個陣列 ```vb Function GetDiscountPrice(price As Range, ratio As Range) As Integer() End Function ``` 將計算出來的多筆資料 --- # Application.Round vs Math.Round() 注意我們最後需要將價格以整數回傳，需要將浮點數簡化成整數 VBA 提供了兩者數值簡化的方法，`Math.Round()` 是使用了銀行家四捨五入 ```vb Sub roundTest() Debug.Print Application.Round(12.55, 1) Debug.Print Application.Round(12.65, 1) Debug.Print Application.Round(12.75, 1) Debug.Print Math.Round(12.55, 1) Debug.Print Math.Round(12.65, 1) Debug.Print Math.Round(12.75, 1) End Sub ``` # 練習：計算報酬率 1. 截取試算表上所有歷史股價 2. 利用陣列計算所有報酬率，寫入陣列 3. 將陣列輸出至試算表  --- # 解答 ```vb Sub returnValue() Dim maxRow As Integer: maxRow = Range("B1").End(xlDown).Row Dim prices As Variant Dim i As Integer Dim returnValues() As Double ReDim returnValues(maxRow - 2, 1) As Double prices = Range(Cells(2, "B"), Cells(maxRow, "B")).Value For i = 2 To UBound(prices, 1) returnValues(i - 1, 1) = (prices(i, 1) - prices(i - 1, 1)) / prices(i - 1, 1) Next i Range(Cells(3, "C"), Cells(maxRow, "C")).Value = returnValues End Sub ```
Thanks for Watching
http://kyosei.ai
Contact: yuyueugene84@gmail.com
Download PDF
