認識 VBA...1
VBA簡介...1
模組與程序...5
VBA基本程式技巧...5
簡易流程控制...7
巨集開始...8
啟用巨集...8
設定安全性...9
製作格式巨集...10
製作填滿巨集...11
修改填滿巨集...11
間插入列巨集...12
插入指定列數巨集...12
刪除空白資料巨集...13
資料轉換 : 全部自動...13
資料清單...14
自動編號...15
表單...16
結合工具鈕...16
實際案例...17
同工作表的固定插入...17
自動選範圍...18
合併工作表...18
自訂函數 (area)...19
自訂函數 (πr2)...20
負數變正數設計[任意範圍]...20
新增多張工作表...22
工作表排序...23
自動程序設計...24
匯入檔案...24
區域號碼 : 儲存格值的控制 [ 工作表 ]...26
清除全部註解...27
取出註解...27
活頁簿控制...28
認識VBA VBA 簡介
VBA 為 Visual Basic For Application 的簡稱，在 Microsoft Office 中包括 Excel、Word、PowerPoint、Access、Visio 均提供 VBA 程式的功能。
敘述
VBA 的語法與 Visual Basic 大多數均是相同的，下列這幾行 正確的VBA 敘述
index1=1
index1=index1+1
If index1 > 10 Then
index1=10
value1=0.01:
alpha=0.85*index1 連續行
VBA 連續字元符號是底線( _ )，當程式執行到某一列是以底 線符號結尾時，他會假設有一個連續的行，並將下一行視為目前敘 述的一部份。:
Response = MsgBox("Do you want to continue ?", vbYesNo + vbCritical + vbDefaultButton2, "MsgBox Demonstration", "DEMO.HLP", Ctxt = 1000)
如果程式列包含很長的列，比較好的方法是加一空白字元和底 線符號在該行結束的地方 ，並在下一列繼續此一列敘述。如下所示 Response = MsgBox("Do you want to continue ?", vbYesNo + vbCritical + _vbDefaultButton2, "MsgBox Demonstration", "DEMO.HLP", Ctxt = 1000)
連續行的最末列的最後面不需要加上底線符號，而且底線符號 之後也不可以再加上任何文字或數字，註解也不可以。
註解
註解列可讓程式易被看懂，又如果要讓程式中的某一列不執行，
只要將這一列標示為註解即可。VBA 中的註解列符號為單引號( ' )，
若是一整列都要作為註解，也可在這列以REM 敘述開頭。
‘This is a remark demo
MyStr1 = "Hello" : Rem 將註解加在陳述式之後，用冒號隔 開。
MyStr2 = "Goodbye" ' 此亦為註解，且不需要冒號。
常數與變數
常數在程式中只包含固定的數值，您不能在程式進行中更動他，
常數以關鍵字Const 始，例如下一列將 beta 定義為常數，其值為 0.85:
Const beta=0.85
VBA 中已有預先定義了某些常數，例如在 MsgBox 函數中 vbYesNo 代表常數值 4。詳細的常數定義可在 VBA 的編輯器中取得 說明。
變數是指到程式可以儲存值的記憶體位置的代表名稱。變數與
常數不同的是變數可在程式執行期間改變其值。在VBA 中變數可有
不 同 的 內 容 型 態 ， 例
如:Byte、Boolean、Integer、Long、Currency、Decimal、Single、
Double、Date、String、Object、Variant ，其中預設的變數型態是 Variant。
以下三列是變數的使用範例:
price=45 ‘數值(integer)變數 Item=”水果” ‘文字(string)變數
Set xlApp = CreateObject("excel.application") ‘物件(object)變數
Option Explicit敘述
這一敘述通知VBA 編譯器找出未宣告即使用的數數。
在預設的情況下VBA 允許變數不經宣告即可使用，但這也造
成程式除錯上的某些困難。您也可在程式的第一行使用 Option
Explicit 敘述，可強制程式在使用變數前先需宣告，這樣有避免拼 錯字母造成程式上邏輯錯誤的問題。
Dim敘述
您可以使用Dim 在程序或函數中明確的定義變數或陣列變數。
若您使用了 Option Explicit 敘述，則您;所使用的變數皆需使用 Dim 先行宣告才可使用。以下三列皆是正確的變數宣告
Dim text ‘宣告了一個變數
Dim price,Value,sum ‘同時宣告兩個以上的變數 Dim Item(100) ‘宣告了 101 個元素的陣列。
VBA 亦可使用陣列，陣列的第一個元素預設為 0，所以上述最 後一列中宣告了Item(0)至 Item(100)共計 101 個變數。
Public與Private關鍵字
Public 與 Private 關鍵字宣告變數用來限制變數的有效範圍，使 用 Public 陳述式宣告的變數，可以在所有模組裏使用 (除非使用 Option Private Module)；變數有效範圍為它們所在的專案裏。
使用 Public 陳述式來宣告變數的資料型態。舉例來說，下列的 陳述式宣告一個變數為 Integer：
Public NumberOfEmployees As Integer
您也可用 Public 陳述式來宣告變數的物件型態。下列的陳述式 宣告一個變數為工作表的新執行個體。
Public X As New Worksheet
Public 陳述式無法在物件類別 模組裏宣告固定長度的字串變數。
Private 用以宣告變數只適用於宣告它們的模組中。Private 陳 述式也可以用來宣告變數的資料型態。舉例來說，下列的陳述式宣告 變數為 Integer：
Private NumberOfEmployees As Integer
您也可使用 Private 陳述式加上空白括號來宣告動態陣列。宣告 之後，在程序裏使用 ReDim 陳述式來定義陣列的維數和元素。變 數的大小已由 Private、Public 或 Dim 陳述式所明確指定，若重新 宣告陣列變數的維數，則會發生錯誤。
變數名稱
如同在Visual Basic 中一般，一個變數名稱最多可使用 255
1.變數的第一個字元需英文字母。例如 Person1 為有效變數 名，23men 則是不合規則的變數名稱。
2.變數名稱不可包含空白、點、，逗號或其他特殊字元。
3.變數名稱不可為關鍵字，例如 for、next、while、public 等 模組與程序
模組(Module)：
功能：是存放程式碼的視窗，使用：可以建立很多模組，分 別存放不同特性的程式碼，包含：在模組中可以建立各種的程序 (Procedure)、函數(Function)
程序(Procedure)與函數(Function)
程序(Procedure)：呼叫這個程序後，會自動執行其內部的程式碼 Sub 程序名稱()
程式內容….
End Sub
函數(Function)：在執行這個函數後，會傳回資料到主程式 Function 函數名稱(參數名稱)
程式內容…
函數名稱(參數名稱) = ……
End Function VBA 基本程式技巧
變數的宣告
Dim x as Integer
Dim str1,str2 as String Dim total as Long
Dim t_date as date
變數型態 大小 資料範圍
Byte 1 位元組 0～255 Boolean 2 位元組 True, False
Integer 整數 2 位元組 -32768～32767
Long 長整數 4 位元組 -2147483648～2147483647 Currency 貨幣 8 位元組 -922337203685477.5808 ～
922337203685477.5807
Decimal 十進位 10 位元組 沒 有 小 數 位 ： +-
7922816251426433759354395 0335
有 小 數 28 位 ： +-
7.92281625142643375935439 50335
Single 單 精 度 浮 點
4 位元組 -3.402823E38 ～ -1.401298E- 45
Double 倍精度浮 點
8 位元組 -1.79769313486231e308 ～ -4.94065645841247e-324
Date 日期 8 位元組 String 字串 可變動
Object 物件 4 位元組 可引用任何物件
Variant 預設 視型態而定 此具有可彈性的資料型態
使用者自訂型態 不定
運算符號 / 算數運算
加+，減-，乘*，除/，餘數 mod，指數^
a^b a mod b
邏輯運算
符號 說明 範例
And 且 X and y
Or 或 X or y
Not 否 Not x
Eqv 相等 X eqv y
Xor 互斥 X xor y
Imp 包含 X imp y
關係運算
符號 說明 範例
= 等於 X = y
<> 不等於 X <>y
< 小於 X<y
>
>=
<=
字串運算
符號 說明 範例
+ 連結 X1+x2+x3
& 連結
先轉換成字串後再連結
X1 & x2 & x3
簡易流程控制
If 條件 then 描述式 End If
If 條件 then 描述式 Else 描述式 End If Select case 運算式
Case 測試值 1 描述式 Case 測試值 2 描述式 End Select
Select case x Case 1 to 3
Str=”第一季”
Case 4 to 6
Str=”第二季”
End Select For 變數=初值 to 終值
描述式 next
Sum=0
For I= 1 to 10 step 1 Sum=sum+I
Next Do while 條件
描述式 loop
X=0
Do while x<5 X=x+1 Loop Do
描述式
X=0 Do
loop while 條件 X=x+1
Loop while x<5 Do until 條件
描述式 loop
X=0
Do until x<5 X=x+1 Loop
Do
描述式
loop until 條件
X=0 Do
X=x+1
Loop until x<5 Do
描述式 loop
X=0 Do
X=x+1 loop 巨集開始
啟用巨集
1. 選取 OFFICE【按鈕】 / EXCEL 選項【清單】
2. 選取 常用 【標籤】/ 勾選 開發人員 索引標籤【項目】
3. 按 確定【按鈕】
設定安全性
選取開發人員【索引標籤】 / 巨集安全性【項目】
設定 啟用所有巨集【項目】 / 確定【按鈕】
製作格式巨集
選取開發人員【索引標籤】 / 錄製巨集【項目】
輸入巨集名稱【如：藍色的字】
選取 確定 鈕
選取常用【索引標籤】 / 字型【項目】 / 設定色彩【如：藍色】
選取開發人員【索引標籤】 / 停止錄製【項目】
製作填滿巨集
選取有數值的任一儲存格【如：B2】
選取開發人員【索引標籤】 / 錄製新巨集【項目】
輸入巨集名稱【如：填滿】 / 選取 確定 鈕
選取常用【索引標籤】 / 填滿【清單】 / 數列【項目】
設定欄 / 設定等差級數 / 設定終止值 【如：50】 / 確定 選取開發人員【索引標籤】 / 停止錄製【項目】
修改填滿巨集
選取開發人員【索引標籤】 / 巨集【項目】
選取巨集名稱【如：填滿】 / 選取 編輯 鈕 修改程式碼
Dim x
x = Range("E2").Value
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Stop:=x, Trend:=False
間插入列巨集
選取開發人員【索引標籤】 / 巨集【項目】
輸入巨集名稱【如：插入列】 / 選取 建立 鈕 Sub 插入列()
For X = ActiveCell.Row To 10 Step 2 Rows(X).Select
Selection.Insert Shift:=xlDown Next
End Sub
插入指定列數巨集
選取開發人員【索引標籤】 / 巨集【項目】
選取巨集名稱【如：插入列】 / 選取 編輯 鈕 Sub 插入列()
X1 = ActiveCell.Row
Y = InputBox("請輸入列數") * 2 + X1 For X = ActiveCell.Row To Y Step 2 Rows(X).Select
Selection.Insert Shift:=xlDown Next
End Sub
註 : Selection.Delete Shift:=xlUp 刪除 刪除空白資料巨集
選取開發人員【索引標籤】 / 巨集【項目】
輸入巨集名稱【如：插入列】 / 選取 建立 鈕
For X = 1 To 100 Step 1
If ActiveCell.Value = "" Then Selection.EntireRow.Delete Else
ActiveCell.Offset(1, 0).Select End If
Next End Sub
資料轉換 : 全部自動 Sub 全部正數() Dim z1, z2
z1 = ActiveCell.Column
Selection.End(xlToRight).Select z2 = ActiveCell.Column
Selection.End(xlToLeft).Select For z = 1 To (z2 - z1 + 1) Step 1 Call 正數
ActiveCell.Offset(0, 1).Select Next
End Sub 資料清單
Sub 清單設計()
With Selection.Validation .Delete
.Add
Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="人事,行政,財務,總務"
.IgnoreBlank = True .InCellDropdown = True .InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl .ShowInput = True
.ShowError = True End With
End Sub End Sub 自動編號
Sub 自動編號() Dim x1, x2, x, y
y = ActiveCell.Address Selection.Copy
Range("A1").Select ActiveSheet.Paste
Selection.End(xlUp).Select x1 = ActiveCell.Row
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select Selection.ClearContents
Range(y).Select
For x = 1 To (x2 - x1 + 1) Step 1 y = "." & ActiveCell.Value ActiveCell.Value = x & y ActiveCell.Offset(1, 0).Select Next
End Sub 表單
選取 開發人員【索引標籤】 / 選取 VB 編輯器 選取 插入【索引標籤】 / 選取 自訂表單【項目】
設計表單內容 / 確定 結合工具鈕
選取OFFICE【按鈕】 / EXCEL 選項【按鈕】
選取 自訂【項目】 / 巨集 【由此選擇命令】
選取 巨集命令【項目】/ 選取 新增【按鈕】 / 確定【按鈕】
實際案例
同工作表的固定插入
Sub 插入() ‘ 固定 5 列 做 10 次 Dim i
For i = 0 To 10 Step 1 x = ActiveCell.Row Rows(x + 5).Select
Selection.Insert Shift:=xlDown ActiveCell.Offset(1, 0).Select Next
End Sub 自動選範圍
合併工作表
Sub 合併工作表()
Dim mySht As Worksheet Dim myRng1 As Range Dim myRng2 As Range Dim i
Set mySht =
Worksheets.Add(After:=Sheets(Sheets.Count)) With mySht
.Name = "new" '設定表名 End With
Worksheets("new").Move AFTER:=Worksheets(Sheets.count)
Set mySht = Worksheets(1) For i = 2 To 4 Step 1
Worksheets(i).Select Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Application.CutCopyMode = False
Selection.Copy mySht.Select
ActiveSheet.Paste Range("A1").Select
Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Next
Worksheets(2).Select '加入標題 Rows("1:1").Select
Selection.Copy mySht.Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown Range("a1").Select
End Sub
自訂函數 (area) Function area(l,w) clp=int(w*l)
End
自訂函數 (πr2) Function clp(r)
clp= Application.WorksheetFunction.Round(3.14*r*r,2) End
負數變正數設計[任意範圍]
Sub 負數變正數()
Dim myRng As Range Dim myRng2 As Range Dim mySht As Worksheet Dim x, x1, y, y1, i, j, n
Set mySht = Worksheets(1) '任意的工作表 Set myRng = mySht.UsedRange
Set myRng = ActiveCell x = ActiveCell.Row
y = ActiveCell.Column
Set myRng2 =
myRng.SpecialCells(xlCellTypeLastCell) Range(myRng2.Address).Select x1 = ActiveCell.Row
y1 = ActiveCell.Column
Range(myRng.Address).Select Set myRng = ActiveCell
ActiveCell.Select n = myRng.Address For j = 0 To (y1 - y) For i = 0 To (x1 - x)
If ActiveCell.Value < 0 Then
ActiveCell.Value = ActiveCell.Value * -1 End If
ActiveCell.Offset(1, 0).Select Next i
For i = 0 To (x1 - x)
ActiveCell.Offset(-1, 0).Select Next i
ActiveCell.Offset(0, 1).Select Next j
Set myRng = Nothing Set myRng2 = Nothing
Set mySht = Nothing End Sub
新增多張工作表 Sub 方法一() Dim x, y
x = InputBox("請輸入增加工作表的數量", "增加工作表") With Worksheets
.Add Count:=x '指定張數來新增 End With
End Sub
Sub 新增多張工作表()
Dim mySht As Worksheet Dim x, y
x = InputBox("請輸入增加工作表的數量", "增加工作表") For y = 1 To x Step 1
Set mySht =
Worksheets.Add(After:=Sheets(Sheets.Count)) With mySht
.Name = y '設定工作表名稱 End With
Next y
Set mySht = Nothing '物件的釋放 End Sub
工作表排序
Sub 排序工作表()
Dim myArray() As String Dim i As Long
ReDim myArray(1 To Worksheets.Count, 1 To 1)’工作表陣 列
For Each mySht In Worksheets
myArray(mySht.Index, 1) = mySht.Name Next
With Worksheets.Add.Range("A1").Resize(UBound(myArray), 1)
.Value = myArray .Sort _
Key1:=.Range("A1"), _ Header:=xlNo, _
Orientation:=xlTopToBottom For i = .Cells.Count To 1 Step -1
Worksheets(.Cells(i).Value).Move _ Before:=Worksheets(1)
Next
Application.DisplayAlerts = False .Parent.Delete
Application.DisplayAlerts = True End With
End Sub 自動程序設計
Sub Auto_Open()
Application.OnKey "%{F11}", "SHOW"
End Sub SUB SHOW()
RANGE(“A1”).VALUE=123 END SUB
組合鍵
組合鍵 符號 組合鍵 符號
ALT % SHIFT +
CTRL ^ ALT+F11 %{F11}
一般鍵
鍵盤 符號 鍵盤 符號 鍵盤 符號
BACKSPAC E
{bs} HOME {HOME} ENTER {ENTER}
-> {RIGHT} {LEFT} {UP} {DOWN}
F1 – F12 {F1} – {F12} ESC {ESC}
匯入檔案
Sub 匯入檔案()
Dim myCnc1 As String Dim myCnc2 As String Dim myFileName As String
myFileName = "F_Data.csv" ' 讀入檔 案
Worksheets.Add '工作表的新增 myCnc1 = "TEXT;"
myCnc2 = ThisWorkbook.Path & "\" & myFileName With
ActiveSheet.QueryTables.Add( Connection:=myCnc1 &
myCnc2,Destination:=Range("A1")) '讀入目標儲存格
.TextFilePlatform = 950 '文字碼的指 定
'格式的指定（標準）
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
'在欄位中指定格式時
'.TextFileColumnDataTypes = Array(2, 1, 9, 9, 9, 9, 9, 1)
.Refresh End With End Sub
區域號碼 : 儲存格值的控制 [ 工作表 ] '工作表模組
Private Sub Worksheet_Change(ByVal Target As Range) Dim x
x = Left(Target.Address, 2) If x = "$A" Then
Select Case Target.Value Case 2
Target.Value = "台北"
Case 3
Target.Value = "桃園"
Case 4
Target.Value = "台中"
Case 5
Target.Value = "嘉義"
Case 6
Target.Value = "台東"
Case 7
Target.Value = "高雄"
End Select End If
End Sub 清除全部註解
Sub 清除全部註解() Cells.Select
Selection.ClearComments ActiveCell.Select
End Sub 取出註解
Sub 取出註解()
Dim myRng As Range Dim myCmt As Comment Dim i
For i = 0 To 10 Step 1
Set myRng = ActiveCell.Offset(i, 0) '任意的儲存格 With myRng
On Error Resume Next Set myCmt = .Comment On Error GoTo 0
If myCmt Is Nothing Then
ActiveCell.Offset(i, 1).Value = myCmt.Text End If
End With Next
Set myRng = Nothing '物件的釋放 Set myCmt = Nothing
End Sub 活頁簿控制
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim x
x = Left(Target.Address, 2) If x = "$A" Then
Select Case Target.Value Case 2
Target.Value = "台北"
Case 3
Target.Value = "桃園"
Case 4
Target.Value = "台中"
Case 5
Target.Value = "嘉義"
Case 6
Target.Value = "台東"
Case 7
Target.Value = "高雄"
End select End If End Sub
