EXCEL VBA從頭來過-基本語法(上篇)

張凱喬
10 min readDec 19, 2017

--

前言
之前工作需要寫過一些VBA
但因為腦子很小,後來要再寫的時候就忘光
藉機重新整理一下,分享給有需要的人

VBA(全名為 Visual Basic for Applications)
是一種 Windows 下的巨集程式語言
其語法承襲傳統的 Visual Basic
在微軟 Office 之中的各種軟體(如 Word、Excel 等)
都可以直接使用,不需要另外安裝

首先,VBA雖然都附隨在Office裡
但是必須先到設定內把它開啟
這樣子才會出現在介面上

參考下列網址

(一)、認識專案與模組

在進入VBA介面的時候,會有幾個欄位
基本上先認識圖中綠色跟藍色的框框就夠了

綠色框框就是VBA專案的架構
包含你現在的Excel物件(例如工作表)
以及模組,也就是VBA程式的內容

這邊先新增一個Module,就可以開始寫程式了

右邊藍色那個框框,就是你寫程式碼的地方
通常一個程式會以Sub "程式名"() 開始
再以End Sub結尾

對於新手而言,或是以上班族需求而言
盡可能的將一個任務(操作資料的過程)寫在一個Sub裡
因為一個Sub就是一個巨集

例如下圖,我在同一個Module裡寫了兩個Sub
分別為test與test2,接著我在EXCEL選取巨集時
他會給我兩個巨集的選擇,就是test與test2
一個任務建一個巨集,使用上比較方便

那除了Sub之外,還可以寫甚麼?
Sub一般稱為副程式;除此之外還有Function(函數)、Property(屬性)

舉例而言,如果你今天建了好幾個巨集
那這些巨集又常幹一些很像的動作
你就可以把這些動作寫成Function
當你有需要時候,在Sub裡呼叫Function幫你完成部分內容
可以節省每次寫這些重複程式碼的時間精神

參考下列連結,有整理Sub與Function的不同與範例

http://edisonx.pixnet.net/blog/post/42109770-vba-procedure-%E7%B0%A1%E8%BF%B0

(二)、常用EXCEL儲存格表示法

接下來,先認識四種選取EXCEL欄位的方式
分別就是一格、一欄、一列與一區

一格就是Cells(列,欄)
例如Cells(1,2)就是對應到"B1"
如果不熟悉列欄的表示方式
就寫Cells(1,”B”)或Cells(“1”,”B”)都可以

沒錯,沒有Cells("B1")這種簡單的寫法
畢竟Cells的主要用途不是這個(下一段說明)

一列就是Rows(列)
例如Rows(1)就是第一列,等同於Rows(“1”)
也可以使用Rows(“1:3”)代表第一到第三列

一欄就是Columns(欄),"欄"或稱為"行"
Columns(4)就是第四欄,等同於Columns(“D”)
但Columns如果要選取多欄
在雙引號裡面要用英文字而不是數字
譬如Columns(“C:D”)代表C欄到D欄

一區的表示方式是Range
切記Range是VBA裡面最好用的選取方式
支援單格、多格、單欄、多欄、單列及多列

單格:
Range(“B1”)
多格:
Range(“A1,B2,C3,D4”)
單欄:
Range(“A:A”)
多欄:
Range("B:B,E:E")
列的表示方式就是把英文字改成數字

另外
如果Range("A1","B2")是表示一區喔(一次選四格的意思)
等同於Range(Cells(“A1”),Cells(“B2”))

參考

一開始可能會想說
既然Range可以選取多格多欄多列
這樣Cells、Rows跟Columns有何意義

Cells、Rows跟Columns的優點是
可以餵變數進去(這對於寫迴圈很重要)

'例如
For i=1 to 5
cells(i,2)=i*2
Next

從B1~B2的值分別為2、4、6、8、10

(三)、常用EXCEL儲存格操作

當你知道如何表示儲存格之後
下一步便是操作

最基本的便是直接給予值
譬如Range(“A1”,”B2”)=100

這樣A1、B1、A2、B2的值就變成100

但是給予值這個動作算特例
因為一般來說 我們會給他動作

譬如說 給予值的動作可以寫成.value
這樣我們上述的程式碼就會變成

Range("A1","B2").value=100

以下先整理幾個比較常用的基本設定
可以直接貼進VBA試試
( ' 是VBA的註解方式)

'粗體字
Range("A1").Font.Bold = true
'設定字體大小
Range("B1").Font.Size = 20
'設定欄位顏色(顏色使用RGB表示)
Range("C1").Interior.Color=RGB(0,255,0)
'設定字體顏色
Range("D1").Font.Color = RGB(255, 0, 0)
'外框設定成雙框線
Range("E1").Borders.LineStyle = xlDouble
'改變欄位寬度
Range("F1").ColumnWidth = 30
'自動調整欄寬(需整欄選取 如果沒有資料則看不出變化)
Range("G1").EntireColumn.AutoFit
'清除資料內容
Range("H1").ClearContents
'清除資料格式
Range("I1").ClearFormats

此外
也可以讀取一些值

'A1的值等於B1的欄位 => A1=2
Range("A1") = Range("B1").Column
'C1的值等於第一個工作表的名字 => C1=工作表1
Range("C1") = Worksheets(1).Name
'D1的值等於E1~E5的格數 => D1=5
Range("D1") = Range("E1","E5").Count

(四)、WorkBooks與WorkSheets

上面兩段提到內容都是在同一個工作表的動作
但是我們使用EXCEL常常會跨工作表甚至不同活頁簿

所以這邊提一下WorkBooks與WorkSheets的概念
WorkBooks就是活頁簿的意思
比較常用到的
大概就是
WorkBooks.Add ‘開啟一個新的活頁簿
WorkBooks.close ‘將所有活頁簿關閉

這邊要小心有沒有s
因為WorkBooks是WorkBook的集合
WorkBook是指一本活頁簿
譬如
WorkBook(1).Save '開啟的第一本活頁簿儲存
WorkBook(2).Activate ‘將開啟的第二本活頁簿拉到當前視窗

一般比較常用的可能是ActiveWorkbook
因為它就是指"當前視窗這本活頁簿"

簡單來說,WorkBooks是針對整個EXCEL、多份活頁簿來操作
WorkBook則是針對單一活頁簿使用,其功能比較多一些
參考

再來就是WorkSheets,指的是工作表
這個在操作資料的時候比較常用
工作表可以用順序、也可以用名字
WorkSheets(1)或WorkSheets(“工作表1”)

'譬如 工作表1的"B2"值等於456
WorkSheets(1).Range("B2")=456
'工作表2的"C3"值等於789
WorkSheets(2).Range("C3")=789

也很常使用WorkSheets(1).Activate
意思就是點開第一個工作表的意思
再來就是ActiveWorkSheet
因為指的是”當前視窗這本活頁簿”

(五)、Select、Copy & Paste

最後
介紹一下最重要的Select、Copy & Paste

Range(“A1”).Select 就很像是滑鼠在A1上點一下的動作
通常.Select之後會搭配Selection

'先點選A1 然後點選起來複製 => 完成複製A1的內容
Range("A1").Select
Selection.Copy

複製之後就要貼上,但貼上比較麻煩
貼上有兩種 一種是Paste、PasteSpecial

如果要比較直觀地重覆與Copy相同的動作
就要使用PasteSpecial

'先點選A1 然後點選起來複製 => 完成複製A1的內容
Range("A1").Select
Selection.Copy
'先點選B1~B4,點選起來後貼上 => 完成內容貼到B1~B4
Range("B1","B4").Select
Selection.PasteSpecial

.PasteSpecial 後面還能附加動作
譬如(記得空白隔開)
.PasteSpecial xlPasteFormats '只會複製格式
.PasteSpecial xlPasteValues '只會複製值

參考

至於Paste,比較麻煩
他前面要接WorkSheets物件
舉例

'先點選A1 然後點選起來複製 => 完成複製A1的內容
Range("A1").Select
Selection.Copy
'先點選B1~B4,點選起來後貼上 => 完成內容貼到B1~B4
Range("B1","B4").Select
ActiveSheet.Paste '在當前的工作表上貼上

最後補充
EXCEL的方法有千千萬萬種
除了GOOGLE是你的好朋友之外
也請善用"錄製巨集"這個功能

簡單來說
錄製巨集就是把你手動操作的動作轉錄成程式碼
(網路上教學萬萬種可以看看)

可以從轉錄之後的程式碼
看一下執行的邏輯,再轉成你自己需要的方式

OK 這篇先降
下一篇再講變數與控制邏輯

--

--

Responses (7)