Excel 與 VBA 財務應用 第四課

Eugene Chang (張佑成)

November 26th 2024

# Excel 與 VBA 財務應用初階班 ![](https://drive.google.com/uc?export=download&id=1Gr9ZYFiI9rwn7cMXxtppqvBZC3zqMb8I) --- # 練習(填值自動化) ![](https://drive.google.com/uc?export=download&id=1OePZ8sZCGKrXWv-pzyc7ndAJJ5WJ1EIX) 請下載:[Lecture3 - Cell & Range 練習](https://drive.google.com/uc?export=download&id=1lVv9r3iItyA5vBoCgvotwuekWn8Wih_n) --- # 注解(Comment) 接下來程式會變得越來愈複雜,若需要在程式碼旁寫一些備注或筆記,而又不會破壞原本程式碼的功能,可以在前方加上單引號 `'` 來注解 ```vb ' 我的前面有 ',不會被執行哦,啾咪! Msgbox("Hello World") ``` --- # 解答 透過以下程式碼,就可以將資料寫入一個指定的範圍自動化: ```vb Range(Cells(Cells(1, "B").Value, Cells(2, "B").Value), Cells(Cells(3, "B").Value, Cells(4, "B").Value)).Value = Cells(5, "B").Value ``` 問題是,這樣的程式碼全部都擠在一起,變得過度冗長 --- # 原則 寫程式碼不但要能執行出正確的結果,也要能夠寫出**容易看懂的、或者換句話説,好維護的程式碼** --- # 變數 (Variable) 就如同代數公式的符號,可以將任何一個值賦值給該符號 ```vb ' 宣告了一個名爲 x 的變數,并且將 5 賦值給 x x = 5 ' 接下來每當我的程式碼出現 x 時,便會帶入 5 Msgbox(x) ``` --- # 變數 (Variable) 變數也可以想象成是一個暫存空間,可以把資料動態的存入變數内 --- # 資料型別(Data Types) 今天我們在寫程式,目的不外乎是爲了用電腦自動化現實生活中的人事物以及流程 一個程式語言會有許多不同的資料型別(Data Types) --- # 範例 Excel 檔案 請下載:[Lecture4 - Variable 變數與運算](https://drive.google.com/uc?export=download&id=1HYk0jBm2EDJWA1X-UC-zePXMgmxCnean) --- # 資料型別(Data Types) - 舉例來説,**一個人的歲數是用整數來表示** - 但是,一個人的體重則是用**帶有小數點的數字來表示** - 相對的,一個人的名字則是可以用**一連串的文字,也就是字串來表示** - 一個人是否脫魯,可以用**布林值來表示** --- # 宣告變數 ```vb Dim 變數名稱 As 資料型別 ``` 範例: ```vb ' 宣告變數 height,該變數只能存入一個整數 Dim height as Integer height = 180 Msgbox(height) ``` --- | 資料型別 | 中文 | 記憶體空間 | 預設值 | 範圍 | |:--:|:------:|:------:|:------:|:------:| | Integer | 整數 | 2 bytes | 0 | –32,768 ~ 32,767 | | Double | 浮點數 | 8 bytes | 0 | –4.94065645841247E–324 ~ 4.94065645841247E–324 | | String | 字串 | 10 bytes | "" | 0 到 20 億個字元 | | Boolean | 布林值 | 2 bytes | False | True 或 False | --- | 資料型別 | 中文 | 記憶體空間 | 預設值 | 範圍 | |:--:|:------:|:------:|:------:|:------:| | Variant | 自由形態 | 16 bytes | vbEmpty | 與 Double 一樣 | --- # 資料型別(Data Types) 不同型別的資料,占據的記憶體空間是不同的 --- # 宣告變數 範例: ```vb ' 宣告變數 height,該變數只能存入浮點數(帶有小數點的數字) Dim weight as Double weight = 75.2 Dim name as String name = "John Doe" Dim employed as Boolean employed = True ``` --- # Variant 一種萬用型別,若不確定會被存入變數的資料型別,可以使用 `Variant` 宣告變數 代價就是,使用過多的 `Variant` 會讓 VBA 程式的效能大幅變慢 --- # Variant 若宣告時沒有明確的表明資料型別,變數就會自動被宣告成 `Variant` ```vb ' x 的資料型別是 Variant x = 5 Msgbox(x) ``` --- # 原則 電腦的空間有限,佔用的記憶體越少,運行的速度就越快 --- # 用嚴格的方法寫程式 强迫在宣告變數時必須明確的表明資料型別,可以使用 `Option Explicit` --- # 回到原本的問題... 利用變數簡化以下程式碼: ```vb Range(Cells(Cells(1, "B").Value, Cells(2, "B").Value), Cells(Cells(3, "B").Value, Cells(4, "B").Value)).Value = Cells(5, "B").Value ``` --- # 修改一下程式碼 用變數之後,程式碼是不是變得好讀很多呢? ![](https://drive.google.com/uc?export=download&id=1OePZ8sZCGKrXWv-pzyc7ndAJJ5WJ1EIX) --- # 完整程式碼 ```vb Sub InputFillVar() Dim startRow As Integer Dim startCol As String Dim endRow As Integer Dim endCol As String Dim fillValue As Variant startRow = Cells(1, "B").Value startCol = Cells(2, "B").Value endRow = Cells(3, "B").Value endCol = Cells(4, "B").Value fillValue = Cells(5, "B").Value Range(Cells(startRow, startCol), Cells(endRow, endCol)).Value = fillValue End Sub ``` --- # 簡化程式碼 將宣告與賦值變成同一行程式碼: ```vb Sub InputFillVar() Dim startRow As Integer: startRow = Cells(1, "B").Value Dim startCol As String: startCol = Cells(2, "B").Value Dim endRow As Integer: endRow = Cells(3, "B").Value Dim endCol As String: endCol = Cells(4, "B").Value Dim fillValue As Variant: fillValue = Cells(5, "B").Value Range(Cells(startRow, startCol), Cells(endRow, endCol)).Value = fillValue End Sub ``` --- ## VBA 運算子: 算數運算子 在 VBA 裡表達數學運算的符號 | 運算子 | 描述 | 範例 | 結果 | |:--:|:------:|:------:|:------:| | + | 加法 | 3+2 | 5 | | - | 減法 | 5-3 | 2 | | * | 乘法 | 3*2 | 6 | | / | 除法 | 10/2 | 5 | | ^ | 指數 | 2^3 | 8 | | Mod | 取餘數 | 19 Mod 5 | 4 | --- # 練習 ![](https://drive.google.com/uc?export=download&id=1Q-oEzN2kaDt0TLUvX5utYRX0Swwc_Pob) --- # 練習 ![](https://drive.google.com/uc?export=download&id=1FNzSSlNG4noClumt2qf2X1FddprsBI9i) --- # 練習 ![](https://drive.google.com/uc?export=download&id=1ulIOc4_kACSJw7Bf5Z-UeVR5c7rREyfu) --- # 練習 ![](https://drive.google.com/uc?export=download&id=1whXaoe7uIKqn1yZw6o2jYvLU1l-wVeHH) --- # 範例 Excel 檔案 請下載:[Lecture4 - Range End 練習](https://drive.google.com/uc?export=download&id=1RzZ7f0mXTjhESgNlz3ndFTg9tkDoxrHR) --- # Range.End 屬性 動態取得一個連續範圍的最後一格 當我們在工作表中有一連串續的資料,想要移動到它最後個儲存格時可以用End屬性 ```vb Range("C5").End(xlToLeft) Range("C5").End(xlToRight) Range("C5").End(xlDown) Range("C5").End(xUp) ``` --- # Range.End 屬性 - 在要尋找資料的起頭或結尾的位置時很好用 - 如果是需要該位置相對應的 row 值與 column 值,後面可以再加上.Row與.Column。 --- # Range.Count 屬性 動態偵測一個選定的範圍共有幾個儲存格 ```vb Range("A1:B2").Count ``` --- ## 在 VBA 内使用 Excel 函數 - 要運用Excel 函數的話,他們屬於Application 類別中的WorksheetFunction - 只要打出 Application 關鍵字 後面部分都會有自動提醒 ![](https://drive.google.com/uc?export=download&id=1ptEL7sVDPhC9b-uwv1vg2PTndXL9mrG8) --- ## 在 VBA 内使用 Excel 函數 選擇想要的函數: ![](https://drive.google.com/uc?export=download&id=1hFtyV7TNeNI5YVvhNxEUa5B2An3TES2w) --- # 在 VBA 内使用 Excel 函數 利用 Excel 的 `Average` 函數計算下面三個浮點數的平均值 ```vb Sub ExcelFunction() Dim x As Double: x = 1.234 Dim y As Double: y = 3.143 Dim z As Double: z = 4.236 End Sub ```

Thanks for Watching

Contact: yuyueugene84@gmail.com

Download PDF