VBA其實很簡單——提高你工作效率的利器

05-03

最近自己看了一下VBA的使用，由於工作中會用到這個工具，所有抽空學習了一下，不要見笑，現在發現其實它平常可以節省我們一定的工作時間，提升我們工作效率，所有總結了一些學習內容。

3.VBA基礎語法

3.1 VBA里的數據類型

VBA里的數據類型有：

(1)位元組型（Byte）: 0~255的整數，例如：年齡

(2)整數型（Integer）：-2768~32767的整數，例如：學生人數、成績

(3)長整數型（Long）： -2147483648~2147483647的整數，例如：總資產、人口

(4)字元串型（String）：例如：姓名、住址、職業、學校等

(5)日期型（Date）：所有時間

(6)單精度浮點型（Single）：例如：圓周率

(7)雙精度浮點型（Double）

(8)貨幣型（Currency）

(9)小數型（Decimal）

(10)布爾型（Boolean）: Ture或False

(11)對象變數（Object）: 用來引用對象

(12)變體型（Variant）: 可以包含任何種類的數據

3.2 存儲數據的容器：常量和變數

3.2.1 使用變數

(1)聲明變數：

Dim 變數名 AS 數據類型

例如：

Dim str AS string

Dim nu AS integer

Dim str AS string,nu as integer（也可以同時聲明多個變數）

Dim str AS string*10（10是指長度最多為10）

(2)使用變數類型聲明符

Dim strs$ （$表示將變數strs聲明為string類型）

Dim ints% （%表示將變數ints聲明為integer類型）

Dim longs& （&表示將變數longs聲明為long類型）

Dim singles! （!表示將變數singles聲明為single類型）

Dim doubles# （#表示將變數doubler聲明為double類型）

Dim currencys@（@表示將變數currencys聲明為currenry類型）

(3)強制聲明所有變數

方法一:在模塊第一句輸入代碼：「Option Explicit」,如果有變數未聲明，會報提示.

方法二：設置強制聲明變數，工具->選項->編輯器->勾選要求變數聲明.

(4)其他聲明類型：

public str AS string 變數聲明為公共變數

private str AS string 變數聲明為私有變數

static str AS string 變數聲明為靜態變數，在整個代碼運行期間都會保留該變數

(5)變數的作用域

單個過程：在一個過程中使用Dimstatic語句聲明變數，作用域為本過程，聲明變數的語句所在過程的可使用，稱為本地變數。例如：

Option Explicit

Sub bdbn()

Dim a As Integer

Static b As Integer

End Sub

單個模塊：在模塊的第一個過程之前使用Dimpivate聲明變數，作用域為聲明變數的語句所在模塊里的所有過程，所有過程都可以使用它，稱為模塊級變數。例如：

Option Explicit

Dim a As Integer

Private b As Integer

Sub bdbn()

Range("A1") = "1111111"

End Sub

所有模塊：在模塊的第一個過程之前使用public聲明變數,作用域為所有模塊，即所有模塊都可使用它，成為公共變數。例如：

Option Explicit

Public b As Integer

Sub bdbn()

Range("A1") = "1111111"

End Sub

(6)變數賦值

如果數據是文本，需用英文雙引號引起；

如果是日期，將日期寫在兩個#號之間，如:d=#2011-01-01#

例如：

Sub szbn()

Dim str As String

Let str = "學習VBA"

Range("A1").Value = str

End Sub

如果給對象變數（Object型，如單元格）賦值：Set 變數名稱=對象（Set一定不能少）

例如：

Sub bdbn()

Dim rng As Range

Set rng = Worksheets("Sheet1").Range("A1")

rng.Value = "學習VBA"

End Sub

3.2.2 使用常量

常量常用來存儲一些固定的、不會被修改的值，例如：圓周率、個人所得稅等。常量聲明不但需要指定常量的名稱及數據類型，還需要在聲明的同時給予賦值，並且在賦值以後不能再重新賦值。

Const 變數名稱 AS 數據類型=數值

Const p AS Single=3.14

3.2.3 使用數組

(1)聲明數組：聲明數組除了要指定數組的名稱及數據類型，還應指定數組的大小.

Public|Dim 數組名 (a to b) AS 數據類型

Dim 七6班 (1 to 50) AS string

Dim arr(49) as string

註：默認數組起始索引是0，除非在模塊第一句標註「OPTion Base 1」 ,那麼數組起始索引是1開始)

(2)聲明多維數組

Dim 數組名 (1 to 3,1 to 20) #(多維數組)

Dim 數組名 (2,19)

(3)聲明動態數組

不能預知數組的大小，可以首次定義數組是括弧為空，寫成：

Dim 數組名稱()

在程序中使用ReDim語句重新指定其大小，例如：

Sub dtsz()

Dim arr() As String

Dim n As Long

n = Application.WorksheetFunction.CountA(Range("A:A")) 統計A列有多少個非空單元格

ReDim arr(1 To n) As String 重新定義數組的大小

End Sub

(4)其他常用的創建數組的方式

變數類型必須定義為Variant型

方法一：使用Array函數創建數組

Sub ArrayTest()

Dim arr As Variant

arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

End Sub

方法二：使用Split函數創建數組

Sub ArrayTest()

Dim arr As Variant

arr = Split("張三,李四,王二",",")

End Sub

方法三：通過Range對象直接創建數組

直接將單元格區域的值賦給變數名

Sub ArrayTest()

Dim arr As Variant

arr = Range("A1:C3").value

Range("E1:G3").value=arr

End Sub

(5)使用UBound和LBound函數

使用UBound和LBound函數計算數組的最大和最小索引號。

UBound(arr)、LBound(arr)

例如：

Dim arr(10 To 50)

MsgBox "數組的最大索引號是:" & UBound(arr) & Chr(13) _

& "數組的最大索引號是:" & LBound(arr) & Chr(13) _

& "數組的元素個數是:" & UBound(arr) - LBound(arr) + 1

End Sub

(6)Jion函數

Jion函數將一個一維數組裡的元素使用指定的分隔符連成一個新的字元串。

例如：

Sub jointest()

Dim arr As Variant, txt As String

arr = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

txt = Join(arr, ",")

MsgBox txt

End Sub

(7)將數組寫入單元格

將一維數組arr里的元素寫入活動工作表中的A1單元格

例如：Range("A1").Value=arr(23)

將數組裡的所有元素批量寫入一個單元格區域：

例如：

Sub arrtorng1()

Dim arr As Variant

arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)

Range("A1:A9").Value = Application.WorksheetFunction.Transpose(arr)

End Sub

3.3 集合、對象、屬性和方法

3.3.1 選取集合中的一個對象

ApplicationWorkbooks("Book1").Worksheets("Sheet1").Range("A2")

Application 代表Excel程序

Workbooks 工作薄集合，表示代開的所有工作薄

Worksheets, 工作表集合，表示指定工作薄中的所有工作表

Worksheets("Sheet2").Range("A2")

Range("A2")

3.3.2 對象的屬性

Worksheets("Sheet2").Range("A2").Font.Color

3.3.3 對象的方法

不同級別的對象和方法之間用點，點後面自動顯示的是圖標是綠色的表示它是方法，圖表不是綠色表示它是屬性。

3.4 VBA中的運算符

3.4.1 算術運算符

+； -； *； /(商)； (整除)； ^；Mod(求模)

3.4.2 比較運算符

=；<；>；<=；>=；<>；IS；Like(比較兩個字元串是否匹配):字元串1 like 字元串2

例如：Range("A2").value Like "*剛*"

VBA中的通配符：

* ：代表任意多個字元

？：代表任意的一個字元

# ：代表任意的一個數字

[charlist]:代表位於charlist中的任意一個字元

3.4.3 連接運算符

連接運算符用來連接兩個文本字元串，有+和&兩種

3.4.4 邏輯運算符

And;Or;Not;Xor(異或);Eqv(等價)

3.5 VBA的基本語句結構

3.5.1 If...Then語句

(1) If Time<0.5 Then MsgBox "1111！"

(2) If Time<0.5 Then

MsgBox "學習VBA1！"

ElseIf Time>0.75 Then

MsgBox "學習VBA2！"

Else

MsgBox "學習VBA3！"

End If

3.5.2 Select Case語句

Sub sayhello()

Select Case Time

Case Is < 0.5

MsgBox "學習VBA1"

Case 0.5 To 0.75

MsgBox "學習VBA1"

Case Is > 0.75

MsgBox "學習VBA2"

Case Else

MsgBox "學習VBA3"

End Select

End Sub

3.5.3 For...Next語句

Sub xingji()

Dim xj As String, i As Integer

For i = 1 To 4 Step 1

Select Case Cells(i, "D")

Case Is < 85

xj = "不評定"

Case Is < 100

xj = "一星級"

Case Is < 115

xj = "二星級"

Case Is < 130

xj = "三星級"

Case Else

xj = "四星級"

End Select

Cells(i, "E") = xj

Next i

End Sub

3.5.4 Do While語句

開頭判斷式：

Sub xingji1()

Dim xj As String, i As Integer

i = 1

Do While Cells(i, "D") <> ""

Select Case Cells(i, "D")

Case Is < 85

xj = "不評定"

Case Is < 100

xj = "一星級"

Case Is < 115

xj = "二星級"

Case Is < 130

xj = "三星級"

Case Else

xj = "四星級"

End Select

Cells(i, "E") = xj

i = i + 1

Loop

End Sub

結尾判斷式：

Sub xingji11()

Dim xj As String, i As Integer

i = 1

Select Case Cells(i, "D")

Case Is < 85

xj = "不評定"

Case Is < 100

xj = "一星級"

Case Is < 115

xj = "二星級"

Case Is < 130

xj = "三星級"

Case Else

xj = "四星級"

End Select

Cells(i, "E") = xj

i = i + 1

Loop While Cells(i, "D") <> ""

End Sub

3.5.5 Do Until語句

開頭判斷式：

Sub xingji2()

Dim xj As String, i As Integer

i = 1

Do Until Cells(i, "D") = ""

Select Case Cells(i, "D")

Case Is < 85

xj = "不評定"

Case Is < 100

xj = "一星級"

Case Is < 115

xj = "二星級"

Case Is < 130

xj = "三星級"

Case Else

xj = "四星級"

End Select

Cells(i, "E") = xj

i = i + 1

Loop

End Sub

3.5.6 For Each...Next語句

當前活動工作薄中有許多工作表，但不知數量，如果要把所有工作表的名稱按次序寫入活動工作表的A列，就用此循環。(變數必須定義為worksheets工作表類型)

Sub shtname()

Dim sht As Worksheets, i As Integer

i = 1

For Each sht In Worksheets

Cells(i, "G") = sht.Name

i = i + 1

Next sht

End Sub

3.5.7 其他常用語句

(1)Go...To 語句

Sub he()

Dim mysum As Long, i As Integer

i = 1

x: mysum = mysum + i

i = i + 1

If i <= 100 Then GoTo x

MsgBox "1到100的自然數和是：" & mysum

End Sub

(2)With語句

當需要對相同的對象進行多次操作時，用With語句

Sub fontset()

With Worksheets("sheet1").Range("A1").Font

.Name = "仿宋"

.Size = 12

.Bold = True

.ColorIndex = 3

End With

End Sub

3.6 指定顏色的單元格個數

Range("B1").Interior.color=RGB(255,255,0)

(1)統計有顏色的單元格的個數

Function countcolor()

Dim rng As Range

For Each rng In Range("A1:A10")

If rng.Interior.Color = RGB(255, 255, 0) Then

countcolor = countcolor + 1

End If

Next rng

End Function

======================================================

4.常用對象

4.1 最頂層的Application對象

清除表中所有數據

Cells.ClearContents

4.1.1 ScreenUpdating屬性

Range("A2:D10000").ClearContents

Application.ScreenUpdating=False #關閉屏幕更新

Application.ScreenUpdating=Ture #恢復屏幕更新

4.1.2 DisplayAlerts屬性

刪除工作表的小程序

Sub delsht()

Dim sht As Worksheet

Application.DisplayAlerts = False 不顯示警告信息

For Each sht In Worksheets 遍歷所有工作表

If sht.Name <> ActiveSheet.Name Then 判斷sht代表的工作表是不是活動工作表

sht.Delete

End If

Application.DisplayAlerts = Ture 恢復顯示警告信息

End Sub

4.1.3 EnableEvents屬性

Application.EnableEvents=False 』禁用事件

Application.EnableEvents=True 』啟用事件

(1)自動寫入單元格地址

Private Sub worksheet_selectionchange(ByVal Target As Range)

Application.EnableEvents=False 』禁用事件

Target.Value = Target.Address

Application.EnableEvents=True 』啟用事件

End Sub

4.1.4 WorksheetFunction屬性

Sub counttest()

Dim mycount As Integer

mycount = Application.WorksheetFunction.CountIf(Range("A1:A9"), ">3")

MsgBox "大於3的單元格數為：" & mycount

End Sub

4.1.5 Application的常用屬性

ActiveCell 當前活動單元格

ActiveChart 當前活動工作簿中的活動圖表

ActiveSheet 當前活動工作簿中的活動工作表

ActiveWindow 當前活動窗口

ActiveWorkbook 當前活動工作簿

4.2 Workbook對象

4.2.1 Workbooks

(1)引用單個工作表

方法一：Workbooks.Item(3) / Workbooks(3)

方法二：Workbooks("Book1")

4.2.2 獲取Workbook對象的屬性

Sub wbmsg()

Range("B2") = ThisWorkbook.Name

Range("B3") = ThisWorkbook.Path

Range("B4") = ThisWorkbook.FullName

End Sub

4.2.3 Workbook操作

(1)創建一個工作薄文件

Workbooks.Add xlWBATChart

(2)打開工作薄

sub openfile()

Workbooks.Open Filename:="F:Book1.xls"

End Sub

(3)激活工作薄

Sub jhwb()

Workbooks("book1").Activate 激活book1工作薄

End Sub

(4)保存工作薄

Sub savewb()

ThisWorkbook.Save 保存工作薄

End Sub

Sub savetofile()

ThisWorkbook.SaveAs Filename:="F:Book1.xls" 使用SaveAs方法將工作薄另存為新文件，關閉原文件，打開新文件

End Sub

Sub savetofile()

ThisWorkbook.SaveCopyAs Filename:="F:Book1.xls" 保存文件，保留源文件，不打開新文件

End Sub

(5)關閉工作薄

Sub closewb()

Workbook.Close 關閉所有打開的工作薄

End Sub

Sub closewb()

Workbook("Book1").Close 關閉book1工作薄

End Sub

4.2.4 ThisWorkbook 與 ActiveWorkbook

ThisWorkbook 是對程序所在工作簿的引用

ActiveWorkbook 是對活動工作簿的引用

Sub wb()

Workbooks.Add

MsgBox "代碼所在的工作簿為：" & ThisWorkbook.Name 顯示代碼所在工作簿名稱

MsgBox "當前活動工作簿為：" & ActiveWorkbook.Name 顯示當前活動工作簿名稱

ActiveWorkbook.Close savechanges:=False 關閉新建工作簿，不保存修改

End Sub

4.3 Worksheet對象

4.3.1 Worksheets

(1)引用單個工作表

方法一：Worksheets.Item(3) / Worksheets(3)

方法二：Worksheets("Book1")

Sheet1.Range("A1")=100

Sub showshtcode()

MsgBox ActiveSheet.CodeName 獲取工作表的代碼名稱

End Sub

4.3.2 操作工作表

(1)新建工作表

Worksheets.Add

Worksheets.Add before:= Worksheets(1)

Worksheets.Add after:= Worksheets(1)

Worksheets.Add count:= 3 插入3張工作表

Sub shtadd()

Worksheets.Add after:=Worksheets(1), Count:=3

End Sub

(2)更改工作表標籤名稱

Worksheets(1).name=test

Sub shtadd1()

Worksheets.Add before:=Worksheets(1)

ActiveSheet.Name = "test1"

End Sub

(3)刪除工作表

Worksheets("sheet6").Delete

(4)激活工作表

Worksheets(1).Activate

Worksheets(1).Select

(5)複製工作表

Sub shtcopy()

Worksheets("sheet1").Copy before:=Worksheets("sheet6")

End Sub

(6)移動工作表

Sub shtmove()

Worksheets("sheet2").Move before:=Worksheets("sheet1")

Worksheets("sheet1").Move

End Sub

(7)獲取工作表的數目

Sub shtcount()

Dim mycount%

mycount = Worksheets.Count

MsgBox "工作簿里一共有 " & mycount & ""

End Sub

4.4 Range對象

4.4.1 引用Range對象

(1)Range().Select

Sub rng()

Range("A1:A10,A4:E6,C3:D9").Select 選中多個單元格

End Sub

Sub rng1()

Range("B1:B10 A4:D6").Select 選中多個單元格區域的交集

End Sub

(2)Cells屬性

Sub cel()

ActiveSheet.Cells(3, 4).Value = 200000

ActiveSheet.Cells(3, "E").Value = 3000000

End Sub

Sub cle2()

Range("B3:F9").Cells(2, 3) = 1111111

End Sub

Cells屬性還可以用作Range屬性的參數

Range(Cells(1, 1), Cells(10, 5)).Select 選中活動工作表的A1：A10單元格

Range("A1","E10").Select

Range(Range("A1"),Range("E10")).Select

這三種都是等價的方式

Sub cel4()

Range("B3:F9").Cells(8).Value = 100

Range("B3:F9").Cells(20).Value = 100

End Sub

4.4.2 引用單元格

(1)引用整行

ActiveSheets.Rows("3:3").Select

ActiveSheets.Rows(5).Select

Rows("3:10").Rows("1:1").Select 選中第3行到第10區域中的第一行

(2)引用整列

ActiveSheets.Columns("F:G").Select

ActiveSheets.Columns(6).Select

Rows("B:G").Columns("B:B").Select 選中B:G區域中的第2行

(3)Application對象的Union方法

將不連續的多個單元格區域粘在一起,可同時對其進行操做

Sub rngunion()

Application.Union(Range("A1:A10"), Range("D1:D5")).Select

End Sub

(4)Range對象的Offset屬性

Offset可以控制移動的方向和距離，正數表示向下向右，負數表示向上向左

Sub rngoffset()

Range("A1").Offset(2, 3).Value = 500

Range("C5:D6").Offset(-3, 0).Select

End Sub

(5)Range對象的Resize屬性

擴大或縮小指定的單元格區域，得到一個新的單元格

Sub rngresize()

Range("B2").Resize(5, 4).Select

End Sub

Sub rngresize1()

Range("B2:E6").Resize(2, 1).Select

End Sub

(6)Worksheet對象的UsedRange對象

返回工作表中已經使用的單元格圍成的矩形區域,包含空行空列

Sub usedrng()

ActiveSheet.UsedRange.Select

End Sub

(7)Range對象的CurrentRegion對象

CurrentRegion屬性返回當前區域，以空行空列的組合為邊界的區域

Sub rngcurr()

Range("B5").CurrentRegion.Select

End Sub

(8)Range對象的End屬性

End屬性返回當前區域結尾處的單元格

Sub rngend()

Range("C5").End(xlUp).Select

Range("C5").End(xlDown).Select

Range("C5").End(xlToLeft).Select

Range("C5").End(xlToRight).Select

End Sub

選擇第一個非空的單元格

Sub rngend1()

ActiveSheet().Range("A65536").End (xlUp)

End Sub

4.4.3 操作單元格

(1)單元格里的內容，Value屬性

Range("A1:B2").Value="abc"

(2)單元格個數，Count()

Count()屬性返回指定單元格區域中包含的單元格個數

Sub rngcount()

Dim mycount As Integer

mycount = Range("B2:F10").Count

MsgBox "B2:F10區域中一共有：" & mycount & ""

End Sub

(3)單元格地址，Address屬性

Sub engaddress()

MsgBox "當前選中的單元格區域地址為：" & Selection.Address

End Sub

(5)選中單元格，Activate與Select方法

ActivateSheet.Range("A1:B10").Select

ActivateSheet.Range("A1:B10").Activate

=======================================================

5.EXCEL事件

事件是一個能被對象識別的對象

5.1 Worksheet事件

5.1.1 常用的Worksheet事件

(1)Worksheet_Change事件：自動提示更改的內容

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox Target.Address & "單元格的值更改為：" & Target.Value

End Sub

(2)Worksheet_SelectionChange事件

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

MsgBox "當前選中的單元格區域為：" & Target.Address

End Sub

(3)Worksheet_Activate事件：自動提示工作表名

(4)Worksheet_Deactivate事件：禁止選中其他工作表

5.2 Workbook事件

5.2.1 常用的Workbook事件

(1)Workbook_Open事件

Workbook_Open事件，當打開工作簿時自動運行程序

(2)Workbook_BeforeClose事件

(3)Workbook_SheetChange事件

5.3 其他事件方法

(1)Application對象的OnKey方法

OnKey方法作用：當鍵盤上按下指定鍵或組合鍵是自動運行程序

(2)Application對象的OnTime方法

OnTime方法，到指定的時間時自動運行程序

後面我將會更新窗體的設計方面和幾個實例應用。