參考書籍

• • Excel VBA 超入門教室, 坪崎誠司 , 旗標 [Link]

  • Excel VBA+巨集入門：實現零加班、光速般的工作效率, 古川順平, 博碩 [Link]

簡介

• • 安全性警告：開啟含巨集的Excel檔，會有安全性警告，避免不小心病毒入侵。

  • 開啟 [開發人員]索引分頁：檔案\選項→自訂功能區\ [V]開發人員

  • 執行「巨集」：開發人員\程式碼\巨集→執行

    • 巨集：自動化的連續操作步驟

  • 查詢「巨集」內的「VBA」程式碼：開發人員\程式碼\Visual Basic

    • 「VBA」程式碼 工作環境設定：

      • 大圖示：檢視\工具列\自訂→選項\[V]大圖示

      • 程式碼的「字型」設定：工具\選項→撰寫風格\字型大小

  • 巨集功能：

    • 利用「含巨集檔*.xlsm」「參照」多個其他已存在Excel檔

    • 利用「含巨集檔*.xlsm」「建立」多個其他新的Excel檔

  • 開啟 [VBE編輯環境]：Alt+F11

  • 巨集的內容就是VBA

  • 含巨集的Excel檔為「*.xlsm」

  • 執行巨集：F5，執行\執行

  • 單步執行巨集：F8，偵錯\逐行

  • 停止執行巨集：ESC

  • 註解：「'」單引號

巨集程式設計簡介

• 啟動VB：開發人員\程式碼\Visual Basic

• 新增「模組」：開啟VB環境→專案視窗→右鍵\插入\模組

• VBA編輯器：專案視窗、屬性視窗、程式碼視窗、功能表列

• 巨集：程式碼與寫在「Sub test01()」與「End Sub」之間

Sub test01()

MsgBox("Hello!")

End Sub

• 訊息顯示視窗函數：MsgBox("訊息文字")

• 執行巨集：

  • 開發人員\程式碼\巨集→選取巨集名稱\執行

  • VBA編輯環境→執行\執行F5

• 資料輸入視窗函數：InputBox("輸入：")

Sub test02()

InputBox("Please input number:")

End Sub

• 變數 宣告

  • Dim 變數名稱 As 資料型態

  • 資料型態：整數「Integer」；字串「String」；日期「Date」

  • 變數宣告：Dim a As Integer

Sub test03()

Dim a As Integer '宣告 變數a 且資料型態為整數

a=InputBox("Please input number:") '利用變數儲存資料

MsgBox(a)

End Sub

• 代入運算子：「=」，將「右邊」的資料傳遞給「左邊」的變數。

• 程式執行是依序「由上至下」一行一行執行。

• 算數運算子：+(加)、-(減)、*(乘)、/(除)、^(次方)、\(取商數)、MOD(取餘數)

• 雙引號("")：在固定文字內容，需要其前後加上雙引號

• 連接運算子：可使用(&)或(+)

• 儲存巨集：*.xlsm

• 常數宣告

  • Const 常數名稱 As 類型=常數值

  • 常數是固定不變的數

• 資料型態：依據不同的資料，可選擇適合的資料型態。

  • 布林 - Boolean - (True/False)

  • 位元組 - Byte - (0~255)

  • 整數 - Integer - (-32768~32767)

  • 長整數 - Long

  • 貨幣 - Currency

  • 單精數 - Single

  • 雙精數 - Double

  • 日期 - Date

  • 字串 - String

  • 物件 - Object

  • 通用型別 - Variant

• 變數命名規則

• 註解：單行註解，單引號「'」

• 巨集安全性 設定：檔案\選項\信任中心\信任中心設定\巨集設定\●停用所有巨集(事先通知)

什麼是變數

• 程式執行：F5

• 單步執行：F8；偵錯\逐行；黃色處代表目前要執行的程式行。

• 監看視窗：可監看程式中變數的內存值。檢視\監看視窗。在程式視窗中，於變數上按「右鍵\新增監看式」。

• 偵錯

  • F8：逐行執行

  • Ctrl+F8：執行至游標處

  • F5：執行全部程式

  • Ctrl+Pause Break：中斷執行

  • F9：設定/解除 中斷點

條件判斷

• 單條件判斷式

If 判斷式 Then

敘述

End If

• 多條件判斷

If 判斷式1 Then

敘述1

ElseIf 判斷式2 Then

敘述2

End If

• 條件-其他條件判斷

If 判斷式 Then

敘述1 '判斷式成立時執行

Else

敘述2 '判斷式不成立時執行

End If

• 比較運算子

  • 等於：=

  • 不等於：<>

  • 小於：<

  • 大於：>

  • 小於等於：<=

  • 大於等於：>=

  • 物件是否相同：Is

  • 字串是否相同：Like

Select Case判斷式

Select Case 判斷式

Case 條件值1

敘述1

Case 條件值2

敘述2

Case Else

敘述3

End Select

-----------------

Select Case x

Case 1, 3, 5

敘述1

Case Is>=5

敘述2

Case 2 To 5

敘述3

Case Else

敘述4

End Select

-----------------

迴圈

For 迴圈變數=起始值 To 結束值

敘述

Next

====

(前判斷語法)

Do Until 條件式

敘述

Loop

---

(後判斷語法)

Do

敘述

Loop Until 

====

(前判斷語法)

Do While 條件式

敘述

Loop

---

(後判斷語法)

Do

敘述

Loop While 條件式

====

• 強迫中斷程式執行: Ctrl+Break

語法

• • Range物件

    • 儲存格的值：Range("儲存格位址").Value。

    • 例：Range("B2").Value="Visual Basic fo Excel"

    • Range("儲存格編號")

    • 多格儲存格範圍：Range("左上儲存格編號:右下儲存格編號")，例：Range("A1:B6")，或Range("A3,C4,D5")，或整列Range("3:3")，或整欄Range("B:B")

  • 清除 儲存格的值：Range("儲存格位址").ClearContents。

    • 例：Range("A2:C5,D6").ClearContents

  • 清除 儲存格的公式設定與格式設定，即不僅清除儲存格內容，連其他格式設定也會清除：Range("儲存格位址").Clear

    • 例：Range("A2:C5,D6").Clear

  • 目前的物件

    • 目前作用中的[儲存格]：ActiveCell

    • 目前作用中的[工作表]：ActiveSheet

      • 命名目前工作表名稱：ActiveSheet.Name="ABC"

    • 目前作用中的[活頁簿]：ActiveWorkbook

    • VBE目前開啟的活頁簿：ThisWorkbook

    • 目前選取的範圍：Selection

• 儲存格物件Cell(橫列數,直欄數)

  • 例：val=Cell(4,5).Value

Range物件-儲存格

• 宣告Range物件設定成變數

Dim target As Range

Set target=Range("A5")

• 儲存格屬性

  • 儲存格值：Range("A6").Value=5

  • 儲存格字型名稱：Range("A6").Font.Name

  • 儲存格列高：Range("A6").RowHeight

  • 儲存格底色：Range("A6").Interior.Color=vbYellow，系統顏色[Link]

• Selection與ActiveCell

  • Selection：回傳被選取的對象，可多個儲存格。例：Selection.Vale="XOX"

  • Active：回傳目前進行的對象，僅能為單一儲存格。例：ActiveCell.Vale="X_X"

• RGB顏色函數

  • RGB(R,G,B)，其中 R=0~255, G=0~255, B=0~255

Workbooks物件-活頁簿

• • 活頁簿的工作表的儲存格的值：Workbooks("活頁簿檔名.xlsx").Worksheets("工作表名稱").Range("儲存格位址").Value

  • 開啟活頁簿

    • Workbooks.Open("路徑\活頁簿名稱.xlsx")

    • 例：Workbooks.Open("C:\aaa\test01.xlsx")

  • 關閉活頁簿

    • 語法：Workbooks("活頁簿名稱.xlsx").Close

    • 例：Workbooks("test01.xlsx").Close

    • 語法：Workbooks("活頁簿名稱.xlsx").Close(True)，關閉時，先儲存檔案再關閉。

    • 語法：Workbooks("活頁簿名稱.xlsx").Close(False)，關閉時，不會儲存檔案再關閉。

    • 語法：Workbooks.Close。關閉所有的活頁簿。

  • 儲存活頁簿

    • 語法：Workbooks("活頁簿名稱.xlsx").Save

  • 開啟活簿個數

    • 語法：Workbooks.Count

  • 該活頁簿的工作表的個數

    • 語法：Workbooks("活頁簿名稱.xlsx").Worksheets.Count

  • 執行中的活頁簿

    • 語法：Workbooks("活頁簿名稱.xlsx").Activate

  • 宣告 活頁簿 為變數

    • 語法：Dim 變數名稱 As Workbook

  • 宣告 工作表 為變數

    • 語法：Dim 變數名稱 As Worksheet

    • 例：

Dim wb As Workbook

Dim ws As Worksheet

Dim rng As Range

Set wb=Workbooks("test01.xlsx")

Set ws=wb.Worksheets("sheet01")

Set rng=ws.Range("A3")

MsgBox(rng.Value)

Worksheets物件-工作表

• Worksheet物件

  • 工作表的儲存格的值：Worksheets("工作表名稱").Range("儲存格位址").Value

  • Worksheets(索引編號)。例：Worksheets(1)

  • Worksheets("工作表名稱")。例：Worksheets("Sheet1")

• 執行中的工作表

  • 語法：Workbooks("活頁簿名稱.xlsx").Worksheets("工作表名稱").Activate

• 宣告 工作表 為變數

  • • 語法：Dim 變數名稱 As Worksheet

Application物件-Excel應用程式

• 關閉應用程式：Application.Quit

• Workbooks.Close：會關閉所有的活頁簿，但Excel應用程式還會打開(存在)。

• ActiveWorkbook：執行中(最上層顯示)的 活頁簿

• ThisWorkBook：執行該巨集的 活頁簿

MsgBox (Application.Name) '顯示軟體名稱 Microsoft Excel

MsgBox (ActiveWorkbook.Name) '顯示 最上層顯示的活頁簿

MsgBox (ThisWorkbook.Name) '顯示 目前執行本巨集命令的活頁簿

「儲存格Range」屬性與方法

Address屬性-儲存格位址

• 語法：Range.Address

  • MsgBox(Selection.Adress)

Borders屬性-框線屬性

• Range.Borders.LineStyle屬性：框線樣式屬性 [Link]

• Range.Borders.Weight屬性：框線粗細屬性 [Link]

• Range.Borders.Color屬性：框線顏色屬性，色彩常數 [Link]

• 指定要擷取的框線：xlBordersIndex的擷取 [Link]

  • 語法：Range.Borders(xlBordersIndex)

  • 例：Range("A2:B5").Borders(xlDiagonalDown).LineStyle=xlContinuous '斜框線的實線

Borders屬性-列號、行號、列數、行數

• Range.Row：儲存格的 列號

• Range.Column：儲存格的 欄號

• Range.Rows：儲存格範圍的 列數

• Range.Columns：儲存格範圍的 欄數

Height與Width屬性-儲存格高度與寬度

• Range("A1").Height：儲存格高度，單位為「點」。

• Range("A1").Width：儲存格寬度，單位為「點」。

End屬性-儲存格範圍的最終儲存格

• Range("A1").End(xlToRight).Address：以儲存格A1之右最終儲存格

Font字型屬性-文字字型

• Range("A1").Font.Name="Time New Roman"：文字字型

• Range("A1").Font.Bold=True：文字粗體字

• Range("A1").Font.Italic=True：文字斜體字

• Range("A1").Font.Underline=True：文字底線

• Range("A1").Font.Size=24：文字大小

Formula屬性-儲存格公式

• Range("A1").Formula：儲存格的內容公式

• Range("A1").Value：儲存格的內容值，有可能是公式計算完後的值

Activate方法-致能儲存格

• Range("A1").Activate

Select方法-選取儲存格

• Range("A1:C3").Select：選取一個範圍的儲存格

AddComment方法-儲存格註解

• Range("A1").AddComment("註解內容")：增加儲存格註解

AutoFill方法-拖曳填滿

• Range("B1:B2").AutoFill(ActiveSheet.Range("B1:B7"))

Merge方法-合併儲存格

• Range("B2:C5").Merge

UnMerge方法-解除合併儲存格

• Range("B2:C5").UnMerge

Insert方法-插入儲存格

• Range("A1").Insert(xlShiftDown)

Delete方法-刪除儲存格

• Range("A1").Delete(xlShiftUp)

Copy方法-複製儲存格

• Range("A1").Copy(Worksheets("工作表1").Range("D1"))

Cut方法-剪下儲存格

• Range("A1").Cut(Worksheets("工作表1").Range("D1"))

「活頁簿WorkBook」的屬性與方法

FullName屬性-活頁簿的路徑與檔名

• Workbooks("活頁簿1.xlsx").FullName

Path屬性-活頁簿的路徑

• Workbooks("活頁簿1.xlsx").Path

Name屬性-活頁簿的名稱

• Workbooks("活頁簿1.xlsx").Name

Add方法-開新活頁簿

• Workbooks.Add

PrintOut方法-列印活頁簿

• ActiveWorkbook.PrintOut

PrintPreview方法-預覽列印活頁簿

• ActiveWorkbook.PrintPreview

SaveAs方法-另存新檔

• ActiveWorkbook.SaveAs("C:\test01.xlsx")

「工作表WorkSheet」的屬性與方法

Index屬性-工作表索引編號

• Worksheets("工作表2").Index

Previous屬性-前一個(左邊)的工作表

• Worksheets("工作表2").Previous.Name

Next屬性-後一個(右邊)的工作表

• Worksheets("工作表2").Next.Name

Rows屬性-第n列儲存格

• ActiveShet.Rows(1).Value="O_O"

Columns屬性-第n欄儲存格

• ActiveShet.Columns(1).Value="*_*"

UsedRange屬性-已經使用的儲存格範圍

• ActiveSheet.UsedRange.Select

Add方法-增加工作表

• Worksheets.Add '增加 工作表

• Worksheets.Add Before:=Worksheets("AAA") '在工作表AAA的左邊新增一個工作表

• Worksheets.Add After:=Worksheets("AAA") '在工作表AAA的右邊新增一個工作表

Copy方法-複製工作表

• Worksheets("工作表1").Copy Before:=Worksheets("工作表3") '將 工作表1 複製 至 工作表3 的 左邊

• Worksheets("工作表1").Copy After:=Worksheets("工作表3") '將 工作表1 複製 至 工作表3 的 右邊

Move方法-移動工作表

• Worksheets("工作表1").Move Before:=Worksheets("工作表3") '將 工作表1 移動 至 工作表3 的 左邊

• Worksheets("工作表1").Move After:=Worksheets("工作表3") '將 工作表1 移動  至 工作表3 的 右邊

Delete方法-刪除工作表

• Worksheets("工作表1").Delete

PrintOut方法-列印

• Worksheets("工作表1").PrintOut

PrintPreview方法-預覽列印

• Worksheets("工作表1").PrintPreview

Select方法-單/複 選

• Worksheets("工作表1").Select (True) '單選 工作表1

• Worksheets("工作表2").Select (False) '複選 工作表2