VBA指令 – 彙整筆記
VBA 提供相當多的指令用法，VBA指令 彙整筆記，將會陸續記錄 VBA文章中，所用過的 VBA指令，方便學員們對照運用。
目錄 :
Range / Cells (儲存格)
※ Range 是最常用的儲存格指令，它有個孿生兄弟 — Cells，程式中有關於儲存格的所有運用，都會看到它們 ; 其差別僅在於設計者如何巧妙的靈活使用。
[ 備註 ] : Range 可以將 Cells 拿來當成屬性使用，反觀，Cells 卻不行。( ※ 比如 : Range( Cells( 1 , 1 ) , Cells( 1 , 2 ))。)
游標儲存格：ActiveCell
ActiveCell — 目前游標所在的儲存格。( B3 位置 )
ActiveCell.Address — 游標位址。( $B$3 )
ActiveCell.Column — 游標行號。( 2 ) — ( PS. A欄為 : 1，B欄為 : 2 ……，以此類推 ! )
ActiveCell.Row — 游標列號。( 3 )
選取(單格)儲存格 : Select
[A3].Select — 選取 [ A3 ] 儲存格。意思是 : 滑鼠游標點選 A3 位址。 [ 注意 ] : [A3] — 意指 : 絕對位置。中括號內，不能使用變數。
Range(“A3”).Select — 同上。
Cells(3, “A”).Select — 同上。★ Cells( 3, “A” ) — 列 : 在前‧行 : 在後 ★
Cells(3, 1).Select — 同上，選取 3列 1行 的位址 = A3。
選取(範圍)儲存格
[A2:C5].Select — 選取 [ A2 : C5 ] 範圍儲存格。
Range(“A2:C5”).Select — 同上。
Range(Cells(2, “A”), Cells(5, “C”)).Select — 同上。 [備註] 所說 : Range 可以將 Cells 拿來當成屬性使用。
Range(Cells(2, 1), Cells(5, 3)).Select — 同上。
選取(整行/列)儲存格
Range(“A:A”).Select — 選取 ( A行 ) 全部。
Range(“1:5”).Select — 選取 ( 1列 ~ 5列 ) 全部。
Columns(“A”).Select — 選取 ( A行 ) 全部。
Columns(5).Select — 選取 ( 第5行 ) 全部。
Rows(2).Select — 選取 ( 第2列 ) 全部。
☆ 選取游標所在的整行 / 列 :
ActiveCell.EntireColumn.Select — 游標儲存格在 B3，執行後，B行 整行會被選取。
ActiveCell.EntireRow.Select — 游標儲存格在 B3，執行後，第3列 整列會被選取。
☆ 選取所有行 / 列 / 儲存格 — 下面三組語法，目的都相同。意思是 : 選取工作表上的所有儲存格。
Columns.Select — 選取所有的行。
Rows.Select — 選取所有的列。
Cells.Select — 選取所有的儲存格。
選取(不相鄰)儲存格
Range(“A1:C3, F1:H3”).Select — 選取 ( A1:C3 ) 及 ( F1:H3 ) 的範圍儲存格。
Union(Range(Cells(1, 1), Cells(3, 3)), Range(Cells(1, 6), Cells(3, 8))).Select — 同上用法。
合併(Merge) / 解除合併(UnMerge)
[A1:C1].Select
Selection.Merge — 合併儲存格。
Selection.UnMerge — 解除合併儲存格。若沒有給予選取目標，預設 : 解除上一個合併儲存格。
清除(Clear) / 刪除(Delete)
※ Clear / ClearContents / Delete 指令，皆適用於單格 / 範圍 / 整行、列 / 所有儲存格 的操作。
Cells.Clear — 清除所有儲存格的內容與格式。
Cells.ClearContents — 清除所有儲存格的內容。
Cells.Delete — 刪除所有儲存格。
Range(“M10”).Delete Shift:=xlUp
※ 刪除(Delete) – Shift 參數用法 :
xlToLeft : 刪除儲存格後，原儲存格資料往左移動。
xlUp : 刪除儲存格後，原儲存格資料往上移動。
複製(Copy) / 剪下(Cut) — 貼上
[A1].Copy [B1] — 複製 [ A1 ] 儲存格，粘貼到 [ B1 ] 儲存格。
Range(“A1:C3”).Copy Range(“D1”) — 複製 ( A1 : C3 ) 範圍儲存格 到 ( D1 ) 開頭的位置 — 貼上。
Range(“A1:C3”).Cut Range(“D1”) — 剪下 ( A1 : C3 ) 範圍儲存格 到 ( D1 ) 開頭的位置 — 貼上。
Range(“A1”).Copy Sheets(“工作表2”).Range(“A1”) — 複製 ( A1 ) 儲存格資料 到 工作表2 ( A1 ) 儲存格 — 貼上。
貼上(Paste)
Range(“A2:H2”).Copy
Range(“A” & lastRow + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
※ 貼上(Paste) 參數用法 :
xlPasteAll : 全部(All)貼在目標位置。
xlPasteValues : 貼在目標位置的資料為 : 數值
xlPasteFormulas : 貼在目標位置的資料為 : 公式
xlPasteFormats : 貼在目標位置的資料為 : 格式
插入(Insert)
Range(“M10:Q10”).Select
Selection.Insert Shift:=xlToRight
※ 插入(Insert) – Shift 參數用法 :
xlToRight : 插入儲存格後，原儲存格資料往右移動。
xlDown : 插入儲存格後，原儲存格資料往下移動。
行/列轉置 (Transpose:=True)
Range(“B2”).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range(“V2”).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
※ 貼上(Paste) 其他參數用法 :
Operation : xlNone (不採取行動) / xlPasteSpecialOperationAdd : 複製的資料將新增至目的地儲存格。
SkipBlanks : 預設值為 False。若為 True : 不會將剪貼簿中的空白儲存格貼到目的地。
Transpose : 預設值為 False。若為 True : 表示將複製的資料列與欄轉置後貼上。
快速移動 : End
※ 如同 EXCEL 快速鍵 : CTRL + 方向鍵，意思是 : 將游標快速移動到資料區域(有資料)的邊界儲存格。
[ 快速移動 – 參數用法 ] :
CTRL + ↓ : End(xlDown) — 快速向下。
CTRL + → : End(xlToRight) — 快速向右。
CTRL + ← : End(xlToLeft) — 快速向左。
CTRL + ↑ : End(xlUp) — 快速向上。
[ 快速移動 – VBA指令 解說 ] : 從 A1 儲存格，快速移動一圈，再回到 A1。
ActiveCell.End(xlDown).Select — 由上圖目前 A1 游標儲存格，End(xlDown) 快速往下移動到 A4 資料邊界(有資料)欄位。
[A4].End(xlToRight).Select — 游標從 [ A4 ] 儲存格，End(xlToRight) 快速往右移動到 E4。
Range(“E4”).End(xlDown).Select — 游標從 ( E4 ) 儲存格，End(xlDown) 快速往下移動到 E7。
Cells(7, “E”).End(xlToLeft).Select — 游標從 ( E7 ) 儲存格，End(xlToLeft) 快速往左移動到 A7。
Cells(7, 1).End(xlUp).Select — 游標從 ( A7 ) 儲存格，End(xlUp) 快速往上移動，回到 A4。
ActiveCell.End(xlUp).Select — 游標從 ( A4 ) 儲存格，End(xlUp) 快速往上移動，但上方沒有任何資料欄位，就會停止在最邊界位置 A1。
快速範圍選取 : End
※ 如同 EXCEL 快速鍵 : CTRL + SHIFT + 方向鍵，意思是 : 選取游標快速移動到資料區域(有資料)邊界的全部儲存格。
[ 快速範圍選取 – 參數用法 ] :
CTRL + SHIFT + ↓ : Selection.End(xlDown) — 快速向下選取。
CTRL + SHIFT + → : Selection.End(xlToRight) — 快速向右選取。
CTRL + SHIFT + ← : Selection.End(xlToLeft) — 快速向左選取。
CTRL + SHIFT + ↑ : Selection.End(xlUp) — 快速向上選取。 
[ 快速範圍選取 – VBA指令 解說 ] : 從 A1 儲存格，快速選取到 E4 的全部儲存格。
Range(“A1”).Select — 若游標起始在 A1 位址，這行指令可以不寫。
Range(Selection, Selection.End(xlToRight)).Select — 從游標 A1 位址，向右範圍選取到 E1 位址的所有儲存格。
Range(Selection, Selection.End(xlDown)).Select — 從游標 E1 位址，繼續向下範圍選取到 E4 位址，涵蓋到 A4 的所有儲存格。
微調移動 : Offset
[ 微調移動 – 參數用法 ] :
ActiveCell.Offset( 1, 0 ).Select — 游標儲存格( 下移1列 )。
ActiveCell.Offset( -1 ).Select — 游標儲存格( 上移1列 )。
ActiveCell.Offset( 0, 2 ).Select — 游標儲存格( 右移2行 )。
ActiveCell.Offset( , -2 ).Select — 游標儲存格( 左移2行 )。
[ 語法範例 ] :
[A1].Offset(0, 2).Select — 游標由 [ A1 ] 右移2行。
Range(“A1”).Offset(0, 2).Select — 同上。
Cells(1, “A”).Offset(0, 2).Select — 同上。
排序 : Sort
[ 語法範例 – 輕鬆幾步驟 – 抓取 外資買賣超歷史資料 ] :
Range(“A4:O100”).Sort Key1:=Range(“J4“), Order1:=xlDescending, Header:=xlNo
[ 排序(Sort) – 指令用法 ] :
◎ Range(“A4:O100”) : 設定排序範圍 : A4 ~ O100。
◎ Key1:=Range(“J4“) : 指定排序的開始欄位 : J4欄。
◎ Order1:=xlDescending : 排序方式。( PS. 參數 : xlAscending ( 升冪 – 由小到大排列 ) / xlDescending ( 降冪 — 由大到小排列 )
◎ Header:=xlNo : 排序資料開頭是否為 : 表頭(Header)。( PS. 參數 : xlYes(有表頭) / xlNo(無表頭)。)
搜尋 : Find
[ 語法範例_A – 3分鐘教會你 – 抓取 大盤借券賣出餘額 歷史資料 ] :
countRow = Range(“B:B”).Find(“合計”).Row
[ 搜尋(Find) – 指令用法 ] :
◎ countRow = Range(“B:B”).Find(“合計”).Row : 利用 Range.Find方法，搜尋 : B整欄，是否有 “合計“字元，若有 → 傳回 Row(1112列)值，給 countRow 變數。
[ 語法範例_B ] :
Set xFind = Range(“B2:B100”).Find(stockCode, LookIn:=xlValues)
If Not xFind Is Nothing Then
xPrice = Range(“C” & xFind.Row)
End If
Set xFind = Nothing
[ 參數設定 ] :
◎ LookIn:=xlValues : 數值搜尋。( ※ xlFormulas : 公式搜尋 / xlComments : 備註搜尋。)
◎ lookat:=xlWhole : 完整符合。( ※ xlPart : 部分符合。)
◎ SearchOrder:=xlByRows : 依列號搜尋。( ※ xlByColumns : 依行號搜尋。)
◎ SearchDirection:=xlNext : 正向搜尋。( ※ xlPrevious : 反向搜尋。)
◎ MatchCase:=Ture : 區分大小寫。( ※ 預設值 : False。)
※ 使用 FindNext 及 FindPrevious 方法，可以進行重複搜尋。( PS. 必須搭配 Find指令搜尋到第一個值之後，才能使用 FindNext。)
( PS. 未找到符合資料，系統會傳回 Nothing，不會傳回錯誤訊息。)
→ 以IF指令來作判別 : If xFind is Nothing then。
( 注意 : 公式是以 xFind is Nothing 來設定，而非 xFind = Nothing。)
查找行數(Column)/列數(Row)
☆ 得知游標儲存格的行數(Column)/列數(Row) :
A = Range(“B1”).Column — Column 為 B ( 第2行 )，所以 A 變數的值 = 2。
B = Range(“C5”).Row — Row 為 5 ( 第5列 )，所以 B 變數的值 = 5。
newColume = Range(“A1”).End(xlToRight).Column — 得知範圍資料的最右一行的行數。( 從 A1 往右找 )
newRow = Range(“A1048576”).End(xlUp).Row — 得知範圍資料的最底一列的列數。( 從 底部 A1048576 往上找 )
刪除重複資料 : RemoveDuplicates
[ 指令用法 ] :
◎ Range(“A2:E100”).RemoveDuplicates Columns:=1, Header:=xlYes ‘ 刪除A2~E100欄位中的重複資料 — 以第1行(A行)為目標值(含表頭)。
字串處理指令
◎ & (字串連接) : 可利用 & 指令，將 A 和 B 兩字串相連接，但須注意的是 & 指令前後，都要加入一個 空白格。
[ 舉例 ] : [ A1 ] = ” Missing ” & ” You ! ” ( 注意 : & 前後皆有空格 ! )
[ 輸出 ] : Missing You !
◎ Len : 傳回該字串的長度。
[ 舉例 ] : [ A1 ] = Len(“Hello”)
[ 輸出 ] : 5
◎ Left : 取出字串開頭(左邊)的部分。
[ 舉例 ] : [ A1 ] = Left(“Welcom”, 3)
[ 輸出 ] : Wel
◎ Right : 取出字串結尾(右邊)的部分。
[ 舉例 ] : [ A1 ] = Right(“Welcom”, 3)
[ 輸出 ] : com
◎ Mid : 取出字串任意的位置。
[ 舉例 ] : [ A1 ] = Mid(“Welcom”, 2, 3)
[ 輸出 ] : elc
[ 範例 ] : 西元年 / 中國年 日期互換
[ 程式碼 / 西元年轉換成中國年 ]
nowDate = Format(Now(), "yyyymmdd") ' → 所得之西元日期格式為 : "20230814"。
ChDate = Int(Left(nowDate , 4) - 1911) & "/" & Mid(nowDate , 5, 2) & "/" & Right(nowDate , 2) ' → 中國年 : "112/08/14"。
[ 程式碼 / 中國年轉換成西元年 ]
ADdate = Int(Left(ChDate, 3) + 1911) & Right(ChDate, 6) ' 西元年日期格式 : "2023/08/14"。
nowDate = Replace(ADdate, "/", "") ' → 西元年日期格式 : "20230814"。
( PS. 也可直接轉換 : ADdate = Int(Left(ChDate, 3) + 1911) & Mid(ChDate, 5, 2) & Right(ChDate, 2) → 西元年日期格式 : "20230814"。
字串比對 : StrComp
※ StrComp : 比對字串，傳回 整數值 的比對結果。
[ 語法公式 ] : StrComp ( string1 , string2 , [ compare ] )
※ compare : 為 0 或 省略，則進行 二進位 比較；為 1 : 則進行 文字 比較。
[ 傳回值 ] :
◎ string1 小於 string2 : 傳回 -1。
◎ string1 等於 string2 : 傳回 0。
◎ string1 大於 string2 : 傳回 1。
◎ string1 或 string2 為 NULL : 則傳回 NULL。
[ 程式碼 / 比對字串 ]
xCompare = StrComp("20230817", "20230817", 1) ' 比對字串。
If xCompare = 0 Then
msgbox " 資料存在 "
Else
msgbox " 資料不存在 "
End if
V B A 資 源
