# Excel 與 VBA 財務應用初階班
---
# 範例 Excel 檔案
請下載：[Lecture 9 - 自訂函數](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
```
---
# 讓自訂函數支援陣列運算
使用者要是輸入一個或多個範圍，應該如何處理？
---
# 讓自訂函數支援陣列運算
輸入一個 Range，回傳一個 VBA Array
```vb
Function BMI(heights as Range, weights as Range) as Double()
BMI = _____________________
End Function
```
---
# 解答
```vb
Function DiscountAry(prices As Range, rates As Range) As Integer()
Dim pricesAry As Variant
Dim ratesAry As Variant
Dim output() As Integer
Dim i As Integer
Dim nData As Integer: nData = prices.count
If nData > 1 Then
pricesAry = prices.Value
ratesAry = rates.Value
Debug.Print UBound(pricesAry, 1)
Debug.Print UBound(pricesAry, 2)
ReDim output(1, UBound(pricesAry, 2)) As Integer
For i = 1 To UBound(pricesAry, 2)
output(1, i) = Application.Round(pricesAry(1, i) * ratesAry(1, i), 0)
Debug.Print output(1, i)
Next i
ElseIf nData = 1 Then
ReDim output(1, 1) As Integer
output(1, 1) = Application.Round(prices.Value * rates.Value, 0)
End If
DiscountAry = output
End Function
```
---
# DRY 原則
今天若一個模組内的多個子程序都有一些共同的邏輯或運算，我們可以用`Function` 將這些共同的邏輯封裝起來
---
# 用 Function 封裝共同邏輯
若今天我們有兩個 Sub 需要計算營業稅：
```vb
Sub Example1()
Dim receivable1 as Double: receivable1 = 100000
Dim tax1 as Double
tax1 = receivable1 * 0.05
Debug.print tax1
End SUb
Sub Example2()
Dim receivable2 as Double: receivable2 = 250000
Dim tax2 as Double
tax2 = receivable2 * 0.20
Debug.print tax2
End Sub
```
---
# 解答
請實作出 `calculateTax()` 函數
```vb
Function calculateTax(amount As Integer, rate As Double) As Integer
calculateTax = amount * rate
End Function
```
---
# 用 Function 封裝共同邏輯
請實作出 `calculateTax()` 函數
```vb
Function calculateTax(amount As Integer, rate As Double) As Integer
End Function
```
---
# 用 Optional 給予參數預設值
因爲在臺灣營業稅是 5%，我們的函數在大多數的情況下使用的稅率都是 0.05，所以我們可以給予第二個參數一個預設值
```vb
Function calculateTax(amount As Integer, Optional rate As Double = 0.05) As Integer
End Function
```
---
# 用 Optional 給予參數預設值
接下來當我們在使用函數時：
```vb
calculateTax(100000) '5000
```
若沒有輸入第二個參數，便會將預設值 0.05 帶入，計算出結果
---
# 讓自訂函數支援陣列運算
定義一個函數，能夠一次算出多一筆或多筆折扣價格：
---
# 讓自訂函數支援陣列運算
輸入是兩個 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
```
---
# 自製量化指標函數
接下來我們就寫出計算移動平均的函數
範例檔案：[Lecture 9 - 技術分析指標函數](https://drive.google.com/uc?export=download&id=1UhOBSJ04XtDEFYWXB43nz2d3BqkbSAnh)
---
# SMA (簡單移動平均函數)
---
# 簡單移動平均(SMA)
簡單移動平均為單純的**算術平均**
- p 代表價格，n 代表天數
---
# 練習：SMA 函數
```vb
Function SMA(prices as Range) as Double
End Function
```
---
# 簡單移動平均(SMA)
以上述範例來說，12/11 之三日簡單移動平均為：
```
(25 + 29 + 27) / 3 = 27
```
---
# 解答
```vb
Function SMA(prices as Range) as Double
SMA = Application.Average(prices.Value)
End Function
```
---
# WMA (加權移動平均函數)
---
# 加權移動平均(WMA)
計算方法如下：
- 我們總共有三天，權重分為：3、2、1。
- 因此，計算平均的分母為：3+2+1=6。
- 12/11 之三日加權移動平均為：
---
# 練習：WMA 函數
```vb
Function WMA(prices as Range) as Double
End Function
```
---
# 解答
```vb
Function WMA(inputData As Range) As Double
'計算使用者丟進來的資料筆數
Dim nData As Integer: nData = inputData.count
'計算權重的加總，也就是分母
Dim demoninator As Integer: demoninator = (1 + nData) * nData / 2
'分子
Dim numerator As Double
Dim count As Integer: count = 1
Dim cell As Range
For Each cell In inputData
numerator = (cell.Value * count) + numerator
count = count + 1
Next cell
WMA = numerator / demoninator
End Function
```
---
# 另一種寫法
```vb
Function WMA(inputData As Range) As Double
Dim nData As Integer: nData = inputData.count
Dim prices As Variant: prices = inputData.Value
Dim i As Integer
Dim demoninator As Integer: demoninator = (1 + nData) * nData / 2
Dim numerator As Double
Dim count As Integer: count = 1
For i = 1 To UBound(prices)
numerator = (prices(i, 1) * count) + numerator
count = count + 1
Next i
WMA = numerator / demoninator
End Function
```
---
# EMA (指數移動平均函數)
---
# 指數移動平均
1. 給訂一個常數 `𝛼∈(0,1)`
* 𝛼 也可以用以下公式計算 (N代表天數)：
---
# 指數移動平均
2. 利用 `𝛼` 計算出每一天的權重，再乘上相對應的價格：
---
# 練習：EMA 函數
```vb
Function EMA(prices as Range) as Double
End Function
```
---
# 解答
```vb
Function EMA(inputData As Range) As Double
'計算使用者丟進來的資料筆數
Dim nData As Integer: nData = inputData.count
'計算 alpha 值
Dim alpha As Double: alpha = 2 / (nData + 1)
Dim numerator As Double
Dim denominator As Double
'記錄每次迭代的指數
Dim index As Integer: index = 0
Dim cell As Range
For Each cell In inputData
numerator = (1 - alpha) ^ index * cell.Value + numerator
denominator = (1 - alpha) ^ index + denominator
index = index + 1
Next cell
EMA = numerator / denominator
End Function
```
---
# 另一種寫法
```vb
Function EMA(inputData As Range) As Double
Dim nData As Integer: nData = inputData.count
Dim alpha As Double: alpha = 2 / (nData + 1)
Dim prices As Variant: prices = inputData.Value
Dim i As Integer
Dim numerator As Double
Dim denominator As Double
Dim index As Integer: index = 0
For i = LBound(prices) To UBound(prices)
numerator = (1 - alpha) ^ index * prices(i, 1) + numerator
denominator = (1 - alpha) ^ index + denominator
index = index + 1
Next i
EMA = numerator / denominator
End Function
```
---
# 完成版程式碼
範例檔案完成版：[Lecture 9 - 技術分析指標函數(完成版)](https://drive.google.com/uc?export=download&id=1VcbVPGSd-JZMeArYqnUjVmWDVgkseoy6)
---
